How to represent a Specification in the Relational model

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...

1. 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 - 15:27:22 CST