Northwind Primary Keys (Access 2003)

[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

 

[Allen Browne]

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.)

 

[Tom van Stiphout]

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.

 

[Sandy]

I presume then that a combination of the two fields is always unique.

Thanks Allen
Sandy

 

[Sandy]

Thanks Tom, I think I get the gist of it

Sandy

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.

 

[Allen Browne]

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.

 

[Sandy]

Allen

Many Thanks
Sandy

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.

 

[David Cressey]

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.

 

[Administrator]

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.

 

[Guest]

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.