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