| Author |
Topic |
|
donnapep
Starting Member
15 Posts |
 Posted - 2007-01-11 : 09:46:58
|
| Hi,I am trying to decide between 2 table designs. I basically have several different types of work items that need to be tracked. Each work item has their own set of statuses, some of which may be the same as other work item's statuses.Now, should I create one status table for each work item, or would it be better to use one status table, but add a column to track which work item the status belongs to?I'm not sure which is a better design. Any help is appreciated.Thx. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-01-11 : 11:34:48
|
| Status Code TableList of all valid statusesWork Item TableList of all work items, and a foreign key to the Status code table. Keep a list of all current statuses. Never have more than one table per item. Just have the item ID and perhaps type.Work Item HistoryA list of all items and what their statuses where at a point in timehave a trigger move the rows here, no need for a FK, butyou couldWe need more detail thoughBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-11 : 14:10:07
|
| An example covering the different cases you need to handle might be helpful; it is kind of hard to visualize what you are describing with the info provided.- Jeff |
|
|
|
donnapep
Starting Member
15 Posts |
Posted - 2007-01-12 : 08:14:42
|
The current table is as follows:StatusCode StatusDesc Screen SortOrder StatusAPPRO Approved RFS 6 ACTIVEAPPROVED Approved ADHOC 24 ACTIVEOPEN Open RFS,Defect,ADHOC 1 ACTIVE These statuses are used to populate the Status drop-down box on particular Screens.SortOrder is the order in which they should appear in the drop-down.I've included some sample data that highlights the main problems.Problem 1 is that some of the statuses are the same, yet have different codes.Problem 2 is that the Screen for which these statuses apply should be separate rows, not a comma-delimited list.Problem 3 is that this design does not allow the SortOrder column to be used as it was intended, as a particular SortOrder will apply for all screens.Right now I am leaning towards creating RFSStatus, DefectStatus and AdHocStatus tables, but just not sure that this is the optimal solution?Thx. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-12 : 09:09:35
|
you simply need two tables. One for your status codes and what they indicate, and another to relate StatusCodes to screen. For the StatusCodes table, I would structure it like this:StatusCode StatusDesc SortOrder Active--------- ---------- --------- --------APPRO Approved 6 1OPEN Open 24 1CLOSED Closed 99 0 etc .... Don't use text to describe Active, use a boolean/bit to make your data meaningful . And don't repeat APPROVED twice if they both have the same meaning. If the status codes are defining other things (show on reports, cannot be edited, cannot be deleted, etc) add more bit columns to your status table to define these attributes as well.Anyway, next you would create a StatusScreens table (primary key of StatusCode/Screen) in which you define which status codes are valid for which Screens:StatusCode Screen--------- -------APPRO RFSOPEN RFSOPEN DefectOPEN ADHOCCLOSED ...etc.. and so on. Now your status tables are normalized and you can easily determine which status codes are valid for which screen type.If different screens require different sort orders for their statuses, you would instead move the SortOrder column to the StatusScreens table.I hope this helps.See example # 4 here: http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspxfor some ideas on how you can really make use of a Status or lookup tables to simplify your code by adding more data to them.- Jeff |
|
|
|
donnapep
Starting Member
15 Posts |
Posted - 2007-01-12 : 11:53:11
|
| Yes, I like your suggestions a lot, especially the bit field to represent Status. The only change I will need to make is to move both SortOrder and IsActive to the StatusScreens table.Loved the link that you provided. This answered a lot of questions I had, however, it leads me to another. What happens if you have 3 or more conditions that need to be represented? For example, where a field value is required for particular stages, optional for other's and must be blank for another. How would you handle something like this in a table? I have been trying to find some sort of solution for storing business rules without having to resort to purchasing a business rule engine. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-12 : 13:57:13
|
| Are you speak of handling these constraints at the database layer? that can be tough, but you can do it with triggers. Just add the conditional columns to your Status table that indicates what is valid or required for each status, and when data changes in your table, your trigger would need to validate it by checking the corresponding entries in the corresponding status.Or, if you are enforcing things at the application layer, just do it there, again by adding more columns to your Status table and checking those columns when the status changes to determine what data must be present in the table.If you mean something else or need something more specific, please provide a more specific example and I should be able to help you out.Glad you enjoyed the article!- Jeff |
|
|
|
donnapep
Starting Member
15 Posts |
Posted - 2007-01-17 : 14:10:18
|
| For example, here's something that I'm not sure how to represent in the table as you've just described.-Items with a status of Submitted can only be changed to Approved or Declined.-Items with a status of Approved can only be changed to Declined.-Items with a status of Declined can only be changed to Approved.My guess would be to add CanBeSubmitted, CanBeApproved and CanBeDeclined columns to the status table and set them accordingly? And then the intersection of the Submitted row and the CanBeSubmitted column would be 0? That could potentially add a lot of columns to my table if there are many statuses (and there are). Is that a concern at all? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-01-17 : 14:29:06
|
quote:
I have been trying to find some sort of solution for storing business rules without having to resort to purchasing a business rule engine.
That's funny.DavidMProduction is just another testing cycle |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-17 : 14:34:42
|
Right idea, but the solution is actually very simple if you again consider that often adding related tables to your design is the way to go, not adding columns. if you add columns, then your status values are now hard-coded and dependant on the columns. but if you add rows to another table ....StatusActions (PK of Status/Action)Status Action NewStatus---------- --------- ---------Submitted Approve ApprovedSubmitted Decline DeclinedApproved Decline DeclinedDeclined Approve Approved Nice and easy, makes sense, your data is normalized, no need to worry about too many columns, and now you know for each Status exactly what statuses it can be changed to ... changing your business logic now only require adding/deleting/changing rows in this table, there is no need to edit columns or anything like that .... you even now have an Action column which you can use to present to the user as the things he can do. very little logic is now embedded in your code . I use this type of table all the time, it makes things so easy. (note that for this to be really normalized, you would have a table of Actions, actually, but hopefully you get the basic idea ...)- Jeff |
|
|
|
donnapep
Starting Member
15 Posts |
Posted - 2007-01-18 : 09:09:54
|
| I'll give that a go. Thanks for the help. |
|
|
|
|
Table Design