Database Design

[dhstein]

I want to keep track of customer orders. So I'll have a customer table with
name address etc. I'll also have an "OrderDate" table with CustomerID,
orderID and order date. Then I'll have an order table with OrderID ProductID
Qty. I think the Order table will have multiple rows with the same OrderID
and different products - one product per record. I think this works, but the
multiple lines with the same OrderID seems a little wrong so I'm wondering if
there might be a better way. Thanks for any help you can provide.

 

[John Spencer (MVP)]

Your design seems to be correct.

You might want to add a primary key your order table. The primary key could
be just an autonumber or you could use two fields (OrderID and ProductID) as
the primary key. IF you use the two fields as the primary key, you will not
be able to enter the same product twice for any one record in the OrderDate table.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[dhstein]

Thanks John.

Your design seems to be correct.

You might want to add a primary key your order table. The primary key could
be just an autonumber or you could use two fields (OrderID and ProductID) as
the primary key. IF you use the two fields as the primary key, you will not
be able to enter the same product twice for any one record in the OrderDate table.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Armen Stein]

You might want to add a primary key your order table. The primary key could
be just an autonumber or you could use two fields (OrderID and ProductID) as
the primary key. IF you use the two fields as the primary key, you will not
be able to enter the same product twice for any one record in the OrderDate table.

I'm going to more strongly recommend an autonumber key for each table.
It will be easier to relate tables together with one field instead of
multiple fields. And as John says, it's possible that some Orders
could have the same Product more than once. It's even possible to
have order detail lines that *don't* have a Product (a comment line,
for example). Using a "meaningless" or surrogate key like an
autonumber gives you the most flexibility in the long run.

If you do want to make sure that there are no duplicate Products in
the same Order, you can add a unique index on those two fields
together in the Order Detail table.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John W. Vinson]

I want to keep track of customer orders. So I'll have a customer table with
name address etc. I'll also have an "OrderDate" table with CustomerID,
orderID and order date. Then I'll have an order table with OrderID ProductID
Qty. I think the Order table will have multiple rows with the same OrderID
and different products - one product per record. I think this works, but the
multiple lines with the same OrderID seems a little wrong so I'm wondering if
there might be a better way. Thanks for any help you can provide.

No, it's exactly correct. You have a Many (orders) to Many (products)
relationship, and therefore you need many records; each order will have as
many rows as there are products in that order.

If you look at the Northwind sample database that comes with Access, it's got
*EXACTLY* this scenario: a table of Customers related one-to-many to a table
of Orders, and that related one to many to a table named OrderDetails, which
has an OrderID and ProductID.