SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Advise needed


Advise needed

Author
Message
halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1951 Visits: 1744
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.
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9520 Visits: 9742
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
Richard Warr
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2786 Visits: 1987
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.

_____________________________________________________________________
MCSA SQL Server 2012
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9520 Visits: 9742
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 :-D


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

Anon.


halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1951 Visits: 1744
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
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9520 Visits: 9742
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
Richard Warr
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2786 Visits: 1987
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.

_____________________________________________________________________
MCSA SQL Server 2012
halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1951 Visits: 1744
Thanks David and Richard, advise gladly taken.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search