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.