Re: How to represent a Specification in the Relational model

"S. Robert James" <srobertjames_at_gmail.com> wrote in message news: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...
>
> 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.
>

This subject is covered in detail on many web pages. Search the web on "generalization specialization relational modeling". Received on Wed Jan 10 2007 - 15:21:39 CET