databasedev.co.uk - database solutions and downloads for microsoft access

Why And How To Create A Many-To-Many Relationship

Create a many-to-many relationship between orders and products

Our task this time is to create a database to keep track of customers, orders, and products. We already know how to create a one-to-many relationship between customers and orders.

Now, we need to create a relationship between orders and products. Whenever we design a database, the setup of relationships is the cornerstone of the design process. We do have an ally in this process, which is our logic.

In this respect, we examine and make conclusions on the relation of each table to the other or of relations among entities because this is how we refer to tables in database parlance.

From the orders point of view, we conclude that each order in the orders table can contain more than one product. It is only logical that a customer can order multiple products in one order.

From the products point of view, we conclude that each product can participate in more than one order. It is logical that we can sell the same product to multiple customers through their orders.

When this is the case, we need to establish a many-to-many relationship between Orders and Products.

To create a many-to-many relationship between the Orders and Products tables, we need to create a join table between them like the Products_Orders table in this example. The primary key of the join table is the combination of the primary keys of the tables that we would like to join in a many-to-many relationship.

In other words, the primary key of any join table in a many-to-many relationship is a composite key consisting of two fields. As we know already, the values of a primary key in a relational table must always be unique, and this uniqueness is expressed in this case by the combination of the values of OrderID and ProductID. For example, the value (1,2) of the first record in the Products_Orders table is different from the value (2,2) in the second record. This is how we obtain uniqueness of primary key values of join tables in many-to-many relationships.

To create a many-to-many relationship in Access 2007, we follow these steps:

1. Make sure that the two tables that you are about to join in a many-to-many relationship already have primary keys. These primary keys should be of the autonumber data type.
2. Create a new table whose name is the combination of the names of the two tables that you would like to join - in this case "Products_Orders." You can follow any other naming convention you prefer as long as you can remember in the future that this is a join table between Orders and Products.
3. In the join table "Products_Orders", create two fields whose names are: ProductID and OrderID. The data types of both OrderID and ProductID should be "Number". This is because we need to join these fields with the corresponding keys in the Orders and Products tables, and we cannot join two fields with dissimilar data types. Your table should look like this:
   
   
4. We are not finished yet, however. Let us assume that a customer orders two units of product (A) and three units of product (B). This customer is ordering multiple quantities of the same product in the same order. In addition, we might want to give discounts on particular products in the same order while extending no discounts for other products. How do we accomplish this? The answer is to include additional fields in the Products_Orders table so that we can enter this information. Our final table will look like this:
   
   
5. The next step is to join the Orders table to the ProductsOrders table by dragging the OrderID field from the Orders table on the OrderID field in the ProductsOrders table. We do the same by dragging the ProductID field from the Products table on the ProductID field in the ProductsOrders table. Our relationships window will now look like the figure below:
   
   

How to "read", understand, and use a many-to-many relationship

The meaning of many-to-many relationship between orders and products

The important goal in any database work is to understand what we are doing and not so much the process of doing it. If we do not remember the series of clicks to achieve a task, we can always resort to a handy reference. However, if we do not understand how many-to-many relationships work, we are reluctant to use them or, at the very least, cannot take full advantage of them.

Let us examine the meaning of a many-to-many relationship from A to Z. The figure below depicts a many-to-many relationship between the Orders and Products tables. Let us try to answer a couple of questions:

What specific products were included in John’s order?

To answer this question, we should go to John’s record in the customer table. There, we see that John’s primary key value is 1 (PK=1). Then, we proceed to the Orders table, which is joined with the customer table through a one-to-many relationship. There, we see that John appears in the third record of the table where CustID = 1. In database parlance, this translates to foreign key value = 1 or FK=1. Next, we see that the corresponding OrderID value for FK=1 is 3 (OrderID =3). From there, we are looking for OrderID = 3 in the ProductsOrders table. We see that we have one OrderID with the value of 3 in the ProductsOrders table. The corresponding ProductID value is 1. Next, we go to the Products table and see that ProductID = 1 corresponds to product A. Since the quantity for the pair (3,1) in the Products_Orders table is 3, we can finally answer that John ordered three units of product (A). This is exactly how relational databases use associations (relationships) to store and retrieve information.

What specific products were included in Mary’s orders?

Mary’s PK is 2. For FK=2 in the orders table, the corresponding PK values are 1 and 2. We now know that Mary placed two orders. For OrderID 1 and 2 in the ProductsOrders table, the corresponding ProductIDs are 2 and 2. The quantities are 2 and 5. Therefore, we know right away that Mary ordered seven product Bs in two separate orders. We also note that Mary has a pattern of ordering only product Bs, which allows us to direct our marketing efforts.