Many-to-many relationships and bridge tables

One order contains many products, and one product appears in many orders. Neither table can hold a foreign key to the other: orders would need a list of product ids, products a list of order ids. Relational databases solve this with a third table, a bridge (also called a junction, link or associative table), with one row per pair:

orders 1 ─ * order_items * ─ 1 products

order_items has a foreign key to each side (order_id, product_id). A many-to-many relationship is really two one-to-many relationships that meet in the bridge. The bridge often carries facts about the pair itself: here the quantity, the price paid and the discount. A bridge with its own measures is the most important table in the schema; don't think of it as plumbing.

In the freight database, purchase_order_lines bridges purchase orders and products in the same way, and purchase_orders itself links suppliers and warehouses: each supplier delivers to several warehouses, each warehouse buys from several suppliers.