order-shipment database tables

Question

I am designing a simple auto parts database for my friend to learn more about database design. I was stuck with the ORDER, ORDERITEM, SHIPMENT, PACKING LIST, INVOICE and CONTAINER relationship.

The ORDER contains ORDERITEM which is individual parts (can be more than one in amount on one entry). When all or part of ORDERITEM are available, SHIPMENT will happen. For each shipment, multiple PACKING LISTs are possible. For each PACKING LIST, one INVOICE is needed. A PACKING LIST will contain one or more CONTAINERs and inside each CONTAINER there will be at least one ORDERITEM.

I don't know if it is a good idea to split data into above tables, and what is a better way to connect them together?

---

EDIT:

Here is my draft of the database design: http://www.flickr.com/photos/oldyoungguy88/3595283724/sizes/o/

There are two parts. One is PARTS table for 'inventory' and one is CUSTOMER, ORDERS, INVOICES, SHIPMENTS tables for 'documents'.

Answer 1

What your talking about is called normalization, and what you need to do that is a foreign key. Wikipedia article on Foreign Keys This is great approach, and can help you organize things efficiently in the database.

There is also a really good article on database normalization over here, that appears to describe this better then what I can do. Below follows my stab at describing what you want to do.

If you have a one to many relationship, the items can be stored in different tables. Let's say that we have the following ORDERTABKE. I've added a primary key to the ORDERTABLE so that I have a distinct Identifier for each row in the table. Wikipedia article on Primary Keys

OrderID - Primary Key
OrderDate
OrderStatus
etc...

Your table for OrderItems could then look like

OrderItemID - Primary Key
OrderID - Foreign Key
PartDescription
Qty

The ORDERITEM table is linking to the ORDERTABKE since it has a foreign key to the OrderID of the parent row. So, each OrderTable can have one or more OrderItems that link to it. You could even put your parts in their own table such as PARTS. That way, you only have to store the parts descriptions once, and can make your OderItem table as simple as possible.

Also, just because a table has a foreign key, does not mean it can't have a primary key that another table references through it's foreign key. It's also possible to go crazy with normalization to where it starts make it difficult to code for. When you should and should not normalize your data is something that you learn through trial and error.

I would recommend reading some references on database design and normalization. There are numerous books on this topic. Try to find one that focuses more on practical examples then the theory behind why you do that.

I hope this helps.