How to represent a Specification in the Relational model
Date: 9 Jan 2007 13:27:22 -0800
Message-ID: <1168378041.168814.267950_at_o58g2000hsb.googlegroups.com>
I'm working on using RDB to represent (order) specifications, and yet keep on hitting what seems to be a brick wall as to the limits of what can be represented relationaly. Hoping some of the experts here can shed some light...
- An order may be for a product (eg Toaster), or for a subproduct (GE Toaster 3000). Specifying just a product and no subproduct means any Toaster.
What first comes to mind, when using SQL, is:
(order_id, product_id, subproduct_id), with subproduct_id being
nullable.
We can set (product_id) to be a FK to product, and (product_id, subproduct_id) to be a composite FK to subproduct.
But there's still a glaring update anomaly: Changing the product_id needs to destroy whatever information is stored in subproduct_id. (The FK won't necessarily catch this.) I can't come up with any way to represent this properly.
2) An order may be marked Rush. If it's marked Rush, you may optionally
specify a reason (from a list).
So, in standard SQL:
(order_id, rush_boolean, rush_reason_id) with rush_reason_id being
nullable.
Again, marking rush_boolean as false needs to destroy rush_reason_id. The only way I've been able to handle and avoid anamolies is to make a sep. relation "rush" (order_id, rush_reason_id), where existence of a record implies rush, use joins to determine if an order is rush, and use cascading deletes to handle deleting an order. Although doable, I really need to question is the complexity involved in doing it this way is justified. Received on Tue Jan 09 2007 - 22:27:22 CET