Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Advise needed Expand / Collapse
Author
Message
Posted Tuesday, December 3, 2013 7:10 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 6:58 PM
Points: 788, Visits: 1,412
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.

Post #1519457
Posted Wednesday, December 4, 2013 7:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 7,045, Visits: 6,784
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.

Post #1519619
Posted Wednesday, December 4, 2013 7:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 4:54 AM
Points: 2,082, Visits: 1,691
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.
Post #1519624
Posted Wednesday, December 4, 2013 7:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 7,045, Visits: 6,784
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.

Post #1519626
Posted Wednesday, December 4, 2013 7:40 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 6:58 PM
Points: 788, Visits: 1,412
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.
Post #1519635
Posted Wednesday, December 4, 2013 7:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 7,045, Visits: 6,784
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.

Post #1519645
Posted Wednesday, December 4, 2013 8:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 4:54 AM
Points: 2,082, Visits: 1,691
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.
Post #1519658
Posted Wednesday, December 4, 2013 12:23 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 6:58 PM
Points: 788, Visits: 1,412
Thanks David and Richard, advise gladly taken.
Post #1519754
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse