Advise needed

Question

Post reply

Advise needed

halifaxdal

SSCoach

Points: 19741
More actions
December 3, 2013 at 7:10 pm

#301930

Please pardon me first: I know this forum might not be the most fitted one but I also know this forum attracts most users so I decided to post my question here.
I have a new database which contains a table called workflow. The table has 10 items and basically each one represents a status.
The values are:
Draft, Saved, Submitted, Returned, LARReviewed, LARSignedOff, SCRReviewed, SCRSignedOff, ...
For each record created in the project, different workflow will be assigned to it.
I almost finished my preliminary front-end and back-end design and just realized a disaster during the final testing:
The system allows any record has more than one status in (LARReviewed, LARSignedOff, SCRReviewed, SCRSignedOff)!!! and my main table has only one int field for the workflow. So it seems impossible to take multiple workflow for one single int field.
I want to hear your advise on this.
Thank you in advance, any suggestion is welcomed and appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

You could use bit locations of the int to store each status

ie Draft=1, Saved=2, Submitted=4, Returned=8, LARReviewed=16, LARSignedOff=32, SCRReviewed=64, SCRSignedOff=128

So a value of 36 would be Submitted and LARSignedOff=32 (4+32)

I would not recommend this though as you may have performance issues.

A redesign of the structure would be preferable.

Far away is close at hand in the images of elsewhere.
Anon.

Richard Warr SSCertifiable Points: 6966 More actions · Answer 2

I'd go for the redesign as well. It's not a major one.

Take out the Workflow field from the Project table and have another table called ProjectWorkflow which would be something like:

ProjectID

WorkflowID (one of your 10)

Date (it's normal to record the date a project reaches a specific milestone - here would be a good place for it).

You can always pick up the current workflow for a project by picking the record with the most recent date.

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Richard Warr (12/4/2013)
I'd go for the redesign as well. It's not a major one.
Take out the Workflow field from the Project table and have another table called ProjectWorkflow which would be something like:
ProjectID
WorkflowID (one of your 10)
Date (it's normal to record the date a project reaches a specific milestone - here would be a good place for it).
You can always pick up the current workflow for a project by picking the record with the most recent date.

+1 😀

Far away is close at hand in the images of elsewhere.
Anon.

halifaxdal SSCoach Points: 19741 More actions · Answer 4

Richard Warr (12/4/2013)
I'd go for the redesign as well. It's not a major one.
Take out the Workflow field from the Project table and have another table called ProjectWorkflow which would be something like:
ProjectID
WorkflowID (one of your 10)
Date (it's normal to record the date a project reaches a specific milestone - here would be a good place for it).
You can always pick up the current workflow for a project by picking the record with the most recent date.

Thanks. I am not sure if I understand you correctly: are you suggesting adding each milestone to the new table?

The workflow could be:

Draft->Saved->Submitted->Returned->(Saved->Submitted->Returned->.....)->LARReviewed->Returned->....Saved->Submitted->Returned->...LAR SignedOff->....Decommissioned

At the same time, a different group of people will do SCR Review and SignOff, just same as LAR Review and SignOff.

This is making the case very complex.

Thanks.

David Burrows SSC Guru Points: 65142 More actions · Answer 5

halifaxdal (12/4/2013)
are you suggesting adding each milestone to the new table?

That would be my understanding.

You could add other attributes to the table (eg name of person who comppleted the milestone)

You could even pre populate the table will all the milestones and include a sequence/process order and a status.

Far away is close at hand in the images of elsewhere.
Anon.

Richard Warr SSCertifiable Points: 6966 More actions · Answer 6

Thanks, David - that was indeed what I was suggesting.

Without knowing more about the business it's hard to be more specific. I have implemented Project Tracking solutions in the past (including one for BP) and this tends to be how they need to work, especially if your organisation is using some sort of "Project Methodology" like PRINCE2.

It does complicate the database solution a bit but it's our job to implement software that fits business requirements.

halifaxdal SSCoach Points: 19741 More actions · Answer 7

halifaxdal

SSCoach

Points: 19741

December 4, 2013 at 12:23 pm

#1670825

Thanks David and Richard, advise gladly taken.