One-to-Many and Many-to-Many

The two shapes of relationships

Almost every schema is built from two relationship shapes:

One-to-many. One customer has many orders, one order belongs to one customer. You already built this in lesson 5-1: put a foreign key on the many side (orders.customer_id).

Many-to-many. One post can have many tags, and one tag can label many posts. Neither side can hold a single foreign key, because each side needs several pointers. The fix is a third table, a junction table (also called a join table), where each row records one connection:

CREATE TABLE post_tags (
  post_id INTEGER,
  tag_id INTEGER
);

You met one already without the name: enrollments in lesson 6-2 connected students to courses many-to-many, and carried extra data about the connection (the grade).