June 3, 2009 at 8:23 am
Looking for some thoughts on a modeling prob. that I inherited
I have a Work Table and a Task Table
Work
PK work_code
work_desc
Task
PK task_code
PK work_code (FK to Work)
etc...
I now have an Order Table and an Order_Line Table
The Order table has work_code in it. (FK to WORK)
The Order_Line table has task_code, work_code (FK to Task).
Is there a good way to design this to prevent Order_Line from having Tasks not belonging to the Order.work_code?
Thanks for any thoughts.
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
June 4, 2009 at 9:08 pm
[font="Verdana"]You could set the referential constraint between Order_Line and Order to include the Work_Code. So long as the Order_Code has a separate unique constraint on it on the Order table (primary key) you can still create another unique index on (Order_Code, Work_Code) and use that for the referential constraint.
Personally I'm not a fan of propogating natural keys, particularly composite ones. I'd use surrogate keys, and enforce the rule of only allowing order lines that match to the tasks for the defined work list in a gateway stored procedure and use security to ensure that the only thing that can insert into the order lines table is the gateway stored procedure.
I've tried to create database designs in the past that enforce the business rules, but it's a pain in the proverbial and business rules change. So instead I design the database to store the data, and enforce extended constraints through logic in the gateway procedures.
[/font]
June 5, 2009 at 5:25 am
Thanks for the input. At this point, its "enforced in the application", but being in the data business I always have that preference to make the model truly express what the data relationships are. Naturally many orders can have a work_code so it gets difficult to use any sort of keys with it to ORDER_LINE.
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply