Modeling Relationships: One-to-Many, Many-to-Many, and Entity-Relationship Diagrams

Why Relationships Matter as Much as Entities

The entities and attributes discussed earlier in this series capture what things exist in a domain, but a database also needs to capture how those things connect to one another — a customer places orders, a book has authors, a course has students. These connections are called Relationships, and modeling them correctly is just as important as identifying the entities themselves.

Cardinality: How Many on Each Side

Cardinality describes how many instances of one entity can be associated with how many instances of another. Every relationship falls into one of three cardinality categories, and correctly identifying which one applies is the foundation for translating a relationship into an actual table structure.

One-to-One Relationships

A One-to-One (1:1) relationship means each instance of one entity is associated with exactly one instance of another, and vice versa.

Example: employee and parking_spot
Each employee is assigned exactly one parking spot,
and each parking spot belongs to exactly one employee

One-to-one relationships are the least common of the three, and are often a signal that two entities could potentially be merged into a single table, unless there is a specific reason to keep them separate, such as different access permissions or the related data being optional for many rows.

One-to-Many Relationships

A One-to-Many (1:N) relationship means one instance of an entity can be associated with many instances of another, but each instance of the second entity relates back to only one instance of the first. This is by far the most common relationship type in real databases.

Example: customer and orders
One customer can place many orders,
but each order belongs to exactly one customer

Implementing a one-to-many relationship uses the foreign key mechanism introduced earlier in this series: the foreign key is placed on the "many" side of the relationship, pointing back to the primary key of the "one" side.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,     -- foreign key, the "many" side
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Many-to-Many Relationships

A Many-to-Many (M:N) relationship means many instances of one entity can be associated with many instances of another.

Example: books and authors
One book can have multiple authors,
and one author can write multiple books

This is exactly the situation that a multi-valued attribute, discussed earlier in this series, points toward — it cannot be represented by simply placing a foreign key in either table, since a single column can only reference one value. Instead, a many-to-many relationship requires a separate Junction Table (also called a Bridge Table or Associative Table), which holds foreign keys to both related entities.

CREATE TABLE book_authors (
    book_id INTEGER,
    author_id INTEGER,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

Each row in this junction table represents one specific book-author pairing. A book with three authors would have three rows in this table, and an author who wrote five books would appear in five rows — this structure naturally supports any combination without duplicating data in the books or authors tables themselves.

Entity-Relationship Diagrams: Visualizing the Design

An Entity-Relationship Diagram (ERD) is the standard visual notation for representing entities, their attributes, and the relationships between them before any SQL is written. Entities are typically drawn as boxes, attributes as smaller labels attached to those boxes, and relationships as lines connecting entities, annotated with their cardinality.

Simplified ERD notation:

[Customer] ----1------N---- [Order]
   |
  attributes: customer_id, name, email

[Book] ----N------N---- [Author]
   |                        |
  attributes:            attributes:
  book_id, title          author_id, name

The "1" and "N" markings on each end of a relationship line indicate its cardinality, making it immediately clear from the diagram which side of a one-to-many relationship needs the foreign key, or that a many-to-many relationship will require a junction table once implemented.

Why Drawing This Diagram Before Writing SQL Matters

Sketching relationships visually before creating any tables catches design problems far more cheaply than discovering them after implementation. A relationship that looks like it should be one-to-many but turns out, on closer inspection of the actual business rules, to sometimes be many-to-many (such as realizing that a book can, in fact, have multiple authors, when the initial design assumed only one), is far easier and cheaper to correct on a diagram than in a live database with existing data and application code already depending on the original structure.

Why Relationships Are the Heart of Relational Design

The name "relational database" itself points to how central these connections are to the entire discipline. Correctly identifying cardinality and choosing the right implementation strategy — a simple foreign key for one-to-many, a junction table for many-to-many — is what allows a database to accurately model complex real-world domains, connect data efficiently through the JOINs discussed earlier in this series, and avoid the data redundancy and inconsistency problems that motivate the normalization rules covered next in this series.