relationships

[pipes]

Please help
I understand how to make a REALIONSHIP say a one to many, but when there are
quite a few tables involved I do not know which would be the one table and
which would be the many side for example you could have two tables one named
products and one named customers so which is the one and which is the many
side.

When I look at the northwind database I am confused regarding the
relationship,again whatare the one side and what are the many, how were the
decisions made to make the one or many sides.

What are the consequences of getting them the wrong way around.

 

[Douglas J. Steele]

Normally, the relationship between products and customers is a many-to-many:
one product can be sold to many customers, and one customer can purchase
many products. That means you need to introduce another table to resolve the
many-to-many.

Look a little more closely at Northwinds. There, there's a one-to-many
relationship between Customers and Orders (one customer places many orders,
each order is placed by one, and only one, customer). There's then a
many-to-many relationship between Orders and Products (one order can be made
for many products, one product can appear on many orders), with the
many-to-many relationship being resolved by the Order Details table. (one
product can appear on many order details, each order detail relates to one
product. one order can have many order details, each order detail relates to
one order.)

 

[Roger Carlson]

I find it useful to write the relationship out in words -- in both
directions (that is from the viewpoint of each table). Something like this:

Each Customer can place one or more Orders
Each Order can be for one and only one Customer.

If both of these are true, then it is a One-to-Many relationship. In order
to create this relationship in the database, you put the primary key field
from the one-side table (Customer) into the many-side table (Order) and
create the relationship on those two fields.

A Many-to-Many relationship is more complex. The relationship between Order
and Products is an example.

Each Order can contain one or more Products.
Each Product can be written to one or more Orders

Since each order can have many products and each product can have many
orders, it's a M:M. To create this relationship requires a third table, one
in which the primary keys of both of the other tables are in the third table
as foreign keys. Creating relationships from each primary key to the
appropriate foreign key, creates two One-to-Many relationships with the
many-side of each in the third (linking) table.

On my website (www.rogersaccesslibrary.com), I have a number of tutorials
you might want to look through. You can find them here:
http://www.rogersaccesslibrary.com/Tutorials.html and select Database
Design.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L