Northwind Primary Keys (Access 2003)

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I notice in the Northwind Sample Database that the 'Products' table has a
Primary Key of "ProductID" and is related to the 'Order Details' table by a
One to Many relationship using 'ProductID'.
The 'Order Details' table shows 'ProductID' as a Primary Key - how is this
possible on a one to many?
The same is true of the 'Orders' table related to the 'Order Details' table;
again related on a One to Many and also shows a Primary Key in the 'Order
Details' table, thereby resulting in the 'Order Details' table having two
Primary Keys - how can a table have two Primary Keys?

I'm confused.
Sandy
 
The Order Details table has a 2-field primary key. It is the combination of
OrderID + ProductID.

It is not 2 primary keys; it is a primary key consisting of 2 fields in
combination.

(I think MS did this just to demonstrate how to have a 2-field key.)
 
On Fri, 21 Dec 2007 10:06:39 -0000, "Sandy"

The way I think about this is that there is a many-to-many (M:M)
between Orders and Products. Each order can have many products. Each
product can appear on many orders. The OrderDetails is the "junction"
table that connects the two. It has a single PK over both foreign-key
values. This is a classic M:M, expressed as two 1:M relations.

-Tom.
 
Thanks Tom, I think I get the gist of it

Sandy

Tom van Stiphout said:
On Fri, 21 Dec 2007 10:06:39 -0000, "Sandy"

The way I think about this is that there is a many-to-many (M:M)
between Orders and Products. Each order can have many products. Each
product can appear on many orders. The OrderDetails is the "junction"
table that connects the two. It has a single PK over both foreign-key
values. This is a classic M:M, expressed as two 1:M relations.

-Tom.
 
Yes. That's why I suggest its a demo only. In an on-line database, for
example, you would not want to design it so that a customer cannot enter
multiple rows for the same product in the one order.
 
Allen

Many Thanks
Sandy


Allen Browne said:
Yes. That's why I suggest its a demo only. In an on-line database, for
example, you would not want to design it so that a customer cannot enter
multiple rows for the same product in the one order.
 
Allen Browne said:
Yes. That's why I suggest its a demo only. In an on-line database, for
example, you would not want to design it so that a customer cannot enter
multiple rows for the same product in the one order.

That depends on the requirements. The requirements, in turn, depend on the
business rules.

There exists a set of business rules for which designing it in this way is
precisely right. The question of whether such a set of business rules is
good or bad is a business question, not a database design question.
 
David Cressey said:
That depends on the requirements. The requirements, in turn, depend on
the
business rules.

There exists a set of business rules for which designing it in this way is
precisely right. The question of whether such a set of business rules is
good or bad is a business question, not a database design question.
 
Allen Browne said:
Yes. That's why I suggest its a demo only. In an on-line database, for
example, you would not want to design it so that a customer cannot enter
multiple rows for the same product in the one order.
 
Back
Top