Re: How to represent a Specification in the Relational model

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 10 Jan 2007 13:48:07 GMT
Message-ID: <r86ph.43064$cz.629919_at_ursa-nb00s0.nbnet.nb.ca>

S. Robert James wrote:
> Thanks for the replies. I see how this will solve problem #2. But
> what's the solution to problem #1? An order can be specified either for
> a product (Toaster), without specifying a particular subproduct (any
> Toaster); or an order can be specified for a particular subproduct (GE
> Toaster 3000). How are both of those orders modelled?
>
>
> Bob Badour wrote:
>

>>Meanwhile, you have quickly and easily described an entire solution for

>
> the allegedly complex solution.
>
> Neo wrote:
>

>>>1) An order may be for a product (eg Toaster), or for a subproduct (GE
>>>Toaster 3000).
>>>
>>>2) An order may be marked Rush. If it's marked Rush, you may optionally
>>>specify a reason (from a list).
>>
>>How about this schema?
>>
>>T_Category (id, name, ...)
>>T_Product (id, name, ...)
>>T_ProductCat (prod_id, cat_id)
>>
>>T_Order (id, name, ... )
>>T_OrderProduct (order_id, prod_id)
>>
>>T_RushJustification (id, descr, ...)
>>T_OrderRush (order_id, rush_id)

First, I should warn you that Neo is a crank. After many long arduous years, he may have improved ever so slightly, but he is still a crank.

Second, a proper design for your problem will depend on ALL of the requirements, and I do not know those. Thus, you must keep in mind what I write below is mostly speculation.

It seems to me you use the same name, ie. product, to refer to two very different concepts:

1. A product is something one can order.
2. A product is something one can ship.

It seems some products are both. However, it seems one can order (generic) products that will result in some other specific product shipping. (The retail term for this I find familiar is 'substitute'.)

This suggests to me your problem is not in the logical model but in your conceptual model.

Suppose we have the three relations: catprod, invprod and substitute for catalog products one can order, inventory products one can ship and allowed substitutions respectively.

catprod = { { p#, name ... }, key (p#) } invprod = { { p#, weight ... }, key (p#)

         , foreign key (p#) refernces catprod(p#) }
substitute = { { p#, subst# }, key (p#,subst#)

            , foreign key (p#) references catprod(p#)
            , foreign key (subst#) references invprod(p#)

}

You might have data like:

catprod = {

           { 1, 'Toaster', ... }
         , { 2, 'Toaster 3000', ... }
         , { 3, 'Spatula 200', ...}
         , { 4, 'Toaster 4000' ... }

}
invprod = { { 2, 1.3 ... }, { 3, 0.75 ... }, { 4, 1.5 ... } } substitute = { {1, 2}, {2, 2}, {3, 3}, {1, 4}, {2, 4}, {4, 4} }

The inventory side of your design might flesh out the quantities on hand and the bin numbers where one can find inventory etc. The catalog side of your design might flesh out order codes and catalog codes etc.

Remember that much of what I wrote above is pure unadulterated speculation. Should inventory products and catalog products key on the same domain? Should every inventory product be a catalog product? Heck if I know. Should you use the names I used above? Probably not.

What you called a category product is simply a catalog product like Toaster than is not an inventory product (reflected too by the lack of {1,1} in substitute.)

Note that although Toaster 3000 is not strictly what you called a category product, in the above, one can substitute a Toaster 4000 for it when out of stock. Received on Wed Jan 10 2007 - 14:48:07 CET