Re: Item Properties

"ciari" <ciari_at_hotmail.com> wrote in message news:i7B46.47870$Z36.266153_at_typhoon.hawaii.rr.com...
> Aloha,
>
> In the works of creating our store database. Would appreciate any
 comments
> on what we should keep an eye for regarding our design. We order
 different
> types of items: books, cds, computers with different properties. We'd
 like
> to capture properties that are unique to each class: author, RAM, number
 of
> tracks. The only thing we're scared of is that the Order_Property table
> will grow fast. Ah, nothing like working under the gun aka management
> motto: "Just get it done."
>
> Item(ItemID, Name, Description)
> Part(PartID, ItemID, Make, Model, P/N, Size)
> Order(OrderID, PartID, Qty, Date, Cost)
> Property(PropertyID, Property, Description)
> Item_Property(ItemID, PropertyID)
> Order_Property(OrderID, PropertyID, Value)
>

If primary key of the part-table is (PartID, ItemID), you must include ItemID in Order as well to maintain referential integrity. If one order spans several items, you end up storing the order-date several times. Usuall you would do like this:

Item( i-id, ...)
Part( p-id, i-id, ... )
Order( o-id, date, ... )
Order_line( o-id, p-id, i-id, ...)

You have a n-n relationship between item and property and between order and property. Is that right? Is it so in the real world that one property may be shared between many items and orders? I doubt it. I am not sure what you intend to store in the propery table, and the order_property table has a "value" field as well (item-property has not). This opens for a lot of confusion in the model.

--

Thomas