Types of table relationships

One-to-One, One-to-Many, Many-to-Many

The strength of relational databases lies in how tables connect to one another. These connections enable us to maintain organized and performant database systems. Typically, the dependent table contains a foreign key column that references the primary key of the parent table, creating an enforced relationship between the two.

Relationship Categories

Relational databases support 3 fundamental relationship categories:

1. One-to-one

2. One-to-many

3. Many-to-many

One-to-one

A one-to-one relationship typically appears as one or more columns within a table row. For instance, an employee record will contain exactly one social_security_number.

Profile information provides another illustration of a one-to-one relationship. An employee will have exactly one department_assignment and exactly one hire_date.

---

One-to-Many

When discussing connections between tables, a one-to-many relationship represents the most frequently encountered pattern.

A one-to-many relationship exists when a single row in one table associates with zero or more rows in another table.

The one→many connection operates in a single direction; a row in the second table cannot associate with multiple rows in the first table!

Examples of One-to-Many Relationships

• An authors table and a books table. Each author has written 0, 1, or multiple books.

• A departments table and an employees table. Each department contains 0, 1, or multiple employees working within it.

CREATE TABLE authors (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE books (
    id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT,
    CONSTRAINT fk_authors
    FOREIGN KEY (author_id)
    REFERENCES authors(id)
);

---

Many-to-Many

A many-to-many relationship exists when multiple rows in one table can associate with multiple rows in another table.

Examples of Many-to-Many Relationships

• A movies table and an actors table - Movies may feature 0 to many actors, and actors can appear in 0 to many movies.

• A projects table and an employees table - Employees can work on multiple projects simultaneously, and projects can have multiple employees assigned to them.

Junction Table

Junction tables facilitate many-to-many relationships between data entities in a database. As an illustration, when establishing the relationship described above between movies and actors, we would create a junction table named movie_actors that stores the primary keys from both tables being connected.

Subsequently, when we need to determine if an actor appears in a particular movie, we can query the junction table to check if the identifiers appear together in a row.

Composite Unique Constraint

When implementing specific schema rules, we may need to apply the UNIQUE constraint across multiple columns.

CREATE TABLE movie_actors (
  movie_id INT,
  actor_id INT,
  UNIQUE(movie_id, actor_id)
);

This configuration allows multiple rows to share the same movie_id or actor_id, but it prohibits any two rows from containing identical values for both movie_id and actor_id simultaneously.

---