M:N Conversion

Imagine that you are designing a database for a typical retail store. You will probably have tables for Customers, Wholesale Suppliers, Products, Shopping Trips, Employees, Paychecks, Work Schedules, etc.

What kind of relationship exists between the table of Shopping Trips and the table of Products? The conditions that describe the relationship might be:

1. Any number of products can be purchased in a single shopping trip.
2. Every shopping trip must have at least one item.
3. Each product can be purchased by many different people in many different shopping trips.
4. Products do not have to be purchased by anyone. (Broccoli and Beets with Blueberry Sauce for only $19.95 a can. Mmmmm, yum.)

The four conditions tell us that there is a M:N relationship between Products and Shopping Trips. The first two conditions tell us the minimum and maximum number of products that a shopping trip can have (one and infinity). The other two conditions tell us the minimum and maximum number of shopping trips that each product can have (zero and infinity). Together these four numbers are known as the cardinalities of the relationship.

This M:N relationship must be decomposed into a bridge table and a couple of 1:N relationship. How is this done?

The tblPurchaseMaster table represents anything that a shopping trip can have at most one of. (Bad grammar; good logic.) A shopping trip can have one customer, one date/time, etc. All of that goes in tblPurchasemaster.

The tblPurchaseDetail table is the bridge that represents the M:N relationship between Shopping Trips (tblPurchaseMaster) and Products (tblProduct). It has a FK pointing to the shopping trip (PMID) and another FK pointing to the product (ProductID).

More FAQs