Many-to-Many
Why two tables can't link many-to-many directly, and how an associative (junction) table between them turns it into two one-to-many relationships.
Journeyman · SQL · Database · Updated
A many-to-many relationship cannot exist as a direct link between two tables. This is the concept that many rows in one table can be related to many rows in another. Adding this type of a PK to FK relationship is not possible.
For instance, one order could contain many products. But a product could be ordered by many customers, or even by the same customer on multiple occasions. In other words, one product can be contained in many orders. This is a many-to-many relationship.
This direct relationship presents a challenge though, that needs to be addressed. Which table should contain the details of the relationship?
If an order can have many products, then should the product table contain the order_id foreign key along with all related data, such as quantity? But then each product would be limited to existing for just one order.
If a product can be ordered many times, then should the order table contain the product_id foreign key as well as all associated data, such as quantity? With this design, each order could then only contain a single product. A customer would need to create a new order for each product they want to purchase.
Two One-to-Many Relationships
The solution is a third table between them, called an associative table (or junction table). It holds one row for each order and product pair, with that pair's details, such as the quantity. The many-to-many relationship becomes two one-to-many relationships: an order has many line items, and a product appears in many line items.
