Re: Table Design

> We would like to capture specific information based on
> each type: number of pages in a book, volume of food, or lenght of
 playing
> time on CD. However, we couldn't(or shouldn't?) capture that on one
 table.
> Would it be better to define a table for each type?
>
> Buy(TransNo,BuyID, VendorID, Date, Qty, Cost, AccountNo)
>
> Book(BuyID, Title, Author, DatePublished)
>
> Food(BuyID, Name, FoodType, Volume)
>
> Music(BuyID, Title, Musician, Minutes, Copyright)
>
> I'd have to pull 3 queries to find each type bought under a certain
 TransNo.
> Is this right?
>

I wouldn't think you'd want to do this. Assuming this is an update intensive database I'd suggest something more like:

    purchases( purchaseID, VendorID, Date, AccountNo )

    purchaseItems ( purchaseId, ItemID, Qty, Cost )

    items( itemID, itemTypeID )

    itemTypes( itemTypeID, itemTypeDesc )

    itemAttributes( itemID, itemAttrTypeID, attrValue )

   itemAttrTypes( itemAttrTypeID, itemAttrTypeDesc )

etc. Where the items are generalized and their associated attributes are stored in another table. You may want to add "itemTypeID" to this other table. This takes 6 joins to pull all the data associated with a shipment, but at least you're not having to maintain a potentially infinite number of tables... I'm assuming other attributes for "items" (like manufacturer), if not it could be combined into purchaseItems (with a composite key)

I don't like the name "buy" for a table so it's "purchases" in the above and I believe you're going to want to track the quantity bought and the cost by item (not at the order level, although you could roll up totals into it, which is not recommended for an operational database).

The trick here, is to start thinking of your purchased items as static data that gets described only once (in a generic manner) and the purchasers as only referencing that data... Received on Fri Apr 14 2000 - 00:00:00 CEST