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

I am curious what sort of complexity concerns you, because your attempt at simplification is so complex you have yet to figure out how to do it. Meanwhile, you have quickly and easily described an entire solution for the allegedly complex solution.

NULL is a glaring sign of a very poor design (ie. abandon all hope ye who enter here), and I suggest boolean attributes are at least a warning to a possibly bad design (ie. there by tygers here). Received on Tue Jan 09 2007 - 23:25:07 CET