Hi,
Your order table should have only one record
Containing order id,customer id, date of  order etc.
ie Only atributes which exclusively refer to the order. No item details.
Your line Item table has two records
1 ItemID for Particular shoe, price shoe was actually sold at, storeid that stock was shipped from, etc
2 Itemid for Particular book,ditto
ie Only info which exclusively refers to items
Your stock file has two records
1 Blue shoe,ItemType,current stock level,storeid,etc
2 War and peace,ItemTypeID
so the two records in the line Item table will have 1 and 2 respectively in the ItemID field.
The item type table will hold records that list the type of stock. Books,shoes,etc
This is called 3rd form normalisation from memory and a lot has been written on it.
HTH
regards
Bob
-----Original Message-----
From:	shawnc@u... [SMTP:shawnc@u...]
Sent:	Wednesday, February 27, 2002 1:44 PM
To:	sql language
Subject:	[sql_language] Table design

In the past, I've occasionally designed tables like this and always felt 
there has to be a better way.

tblOrders: order_id,item_type,quantity
sample tblOrders data:
1,shoe,2
1,book,4

tblShoes: shoe_id,order_id,size,color
sample tblShoes data:
3,1,medium,red

tblBooks: book_id,order_id,author,num_pages
5,1,Joe Smith,450

Relationships are: tblOrders->>tblShoes, tblOrders->>tblBooks

item_type can be any value that corresponds to a type of item (e.g. shoe, 
book, etc.) 

This creates the case where the first record in the Orders table has to 
be joined with the Shoes table and the second record has to be joined 
with the Books table to get all the needed data.

This has worked for cases where the number of different item types is 
small, but what has me feeling it's not the right way to do it is that if 
the number of different item types was large, it would get unmanageable.

So what's the proper way to structure this so it's scaleable when each 
item type has different attributes/properties?

Is there a better way other than to end up with an Orders, OrderDetails, 
Items and ValidItemAttributes tables, where the Items table is (item, 
attribute-name, attribute-value) and the ValidItemAttributes is (item, 
attribute-name)?