Many-to-Many Relationships (1 Viewer)

[hicksjt]

I am new to database design and curious about relationships. In a hypothetical situation, if I have four tables: tblProducts, tblOrders, tblEmployees, and tblCustomers, how could the relationship be set up?

The Products are stored to keep track of the quantity on hand and the employees will also need to be tracked as to how many products they sell. Customers are recorded and Orders wrap up all three tables with the fields: OrderID, ProductID, EmployeeID, and CustomerID.

Would it make sense to say that many products can be bought by many customers and/or many employees can place many orders.

 

[FoFa]

Yes.
Also Many employees can place many orders for one customer?
And many customers can place many orders?
But can Many employees place the same order for many customers (the same ORDER ID)?
Typically order have line items.
Can many employees place the same order/line item?
Usually you have One Employee place One order (with one or more line items) for One customer.
If you have many employees placing one order for one customer, than you usually track the employee at the line item level.
Confused?

 

[quest4]

Sense you are new at this, always use the KISS method. Don't over think things. Most relationships are one-to-many and there are a couple one-to-one relationships. Many-to-many relationships ar rare, thank god, they can be a real pain. I have not seen you tables so it is a little hard to tell you what relationship goes where, but I always try and use only what is needed, so if I need one, then i would only use one. If suck with a many-to-many relationship, prey, no try this site it has a good example:
http://www.microsoft-accesssolutions.co.uk/many_to_many_example.htm
This should give you a feel for what you would be getting into. Remember most relatinships are one-to-many. hth.

 

[The_Doc_Man]

To do many-to-many linkages, you have to remember what you are asking.

Access supports two basic types of relationships: One to One and One to Many. (Remember that relationships are bidirectional, so Many to One is just One to Many seen from the other side.)

Many to Many is not a possible choice in ONE step - but quite possible in TWO steps. You make an overlap table. This table MIGHT be so simple as just the prime key from table 1 and the prime key from table 2, to indicate that there exists an actual link between the two records.

If you want to picture it symbolically in your head,

Table 1 is a circle of all things in that table.

Table 2 is a circle of all things in that table.

If the circles overlap, the lens-shaped area is the overlap table that shows you things that appear in both tables at once. Since it is inside table 1's circle, it has to contain data for table 1. Since it is inside table 2's circle, it has to contain data for table 2. Access allows you to just store the prime keys for each table and use JOIN queries to get the rest.

 

[Pat Hartman]

and there are a couple one-to-one relationships. Many-to-many relationships ar rare

Quite the opposite. It is the true one-to-one that is exceptionally rare. The mistake made by many new developers is to create 1-1 relationships because they have repeating groups and that causes too many columns for a single table to hold. Many-to-Many won't appear in every database but they will occur in most databases. I haven't created a db with a 1-1 in years. If you think you need a 1-1, think again. Your db is probably not normalized.

 

[leemorris2005]

I've come across quite a few many-to-manys before, which I always turn into two one-to-manys and a table linking them. I agree with pat about the one-to-ones also.

 

[The_Doc_Man]

There is a special case for 1-1 relationships, and Pat should remember it. She made the suggestion once herself. When one of the tables includes a big MEMO field, that field type is very often involved in table corruption. Having a 1-1 relationship for the memo field to its parent prevents you from corrupting the parent quite so often. But as Pat points out, it ain't something you see every day.

 

[Pat Hartman]

Yes I did recommend it but the problem is caused by a Jet bug. It wouldn't apply to any other relational database and I wouldn't recommend it unless you are having problems with the table becoming corrupted. Most databases never experience this problem.

 

[quest4]

Doc Man, thanks for that tip on how to handle memo fields. I might have a good use for that sometime, I use three or four memo fields in a couple of dbases, so it is nice to know a way of handling them if they ever start giving me trouble.

 

[The_Doc_Man]

Thank Pat. It was originally her info. I just have a memory like an elephant. Now if I could just get a waistline that WASN'T like an elephant...

 

[quest4]

Oh,sorry, thank you Pat very much for first finding that information. Thank you Doc for passing it on, and stay out of those French Quarter restaurants.