Relationships between tables: one-to-many, many-to-many, one-to-one

Three Relationship Types

Relationships describe how rows in one table connect to rows in another. Getting cardinality right determines your entire schema structure.

One-to-Many

One customer can have many orders. Store the PK of the "one" side as a foreign key in the "many" side table.

CREATE TABLE orders (
  order_id    INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date  DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Many-to-Many

One order can contain many products; one product can appear in many orders. You cannot store this in either table alone. Create a junction table.

CREATE TABLE order_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL DEFAULT 1,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id)   REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

One-to-One

One user has exactly one profile. Use this to split a wide table or isolate sensitive columns. Add a UNIQUE constraint to the foreign key so the relationship cannot become one-to-many accidentally.

CREATE TABLE user_profiles (
  user_id  INT PRIMARY KEY,
  bio      TEXT,
  avatar   VARCHAR(255),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);