Re: How to represent a Specification in the Relational model

S. Robert James wrote:
> 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.

Do not specify products to order, just subproducts. If a generic order can be made, add a subproduct id for generic ones.

Or, in the orders TABLE, add a CHECK CONSTRAINT forcing either the Product, or the Subproduct field to be NULL.

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

Add a rush reason id of "No reason", and drop the flag. Then, if it isn't NULL, it's a rush order.

Or, add a CHECK CONSTRAINT not allowing rush_boolean to be false whilst a rush_reason_id is not NULL.

B.

B. Received on Wed Jan 10 2007 - 16:50:55 CET