You Can’t Handle the Bridge Table

Bridge tables are usually introduced as the solution to one specific database-design problem: the many-to-many relationship.

A student can enroll in many courses. A course can contain many students. Neither table can represent that relationship with a single foreign key, so we insert another table between them:

CREATE TABLE enrollments (
    student_id bigint NOT NULL REFERENCES students(id),
    course_id bigint NOT NULL REFERENCES courses(id),

    PRIMARY KEY (student_id, course_id)
);

The bridge table breaks one many-to-many relationship into two one-to-many relationships.

That is correct, but it is an incomplete explanation of what bridge tables are useful for.

A bridge table does more than resolve cardinality. It turns a relationship into a row.

And once the relationship has a row, the relationship can have data of its own.

Data Belonging to the Edge

Consider the relationship between a student and a course.

A student has a name. A course has a title. But what about these values?

enrolled_at
enrollment_status
final_grade
completion_date
enrollment_source

A final grade is not a property of the student in general. It is not a property of the course in general.

It belongs to one student’s relationship with one course.

In graph terminology, the student and course are nodes. The enrollment is the edge connecting them.

The grade, status, and enrollment date are properties of that edge.

The bridge table gives those properties somewhere to live:

CREATE TABLE enrollments (
    student_id bigint NOT NULL REFERENCES students(id),
    course_id bigint NOT NULL REFERENCES courses(id),

    enrolled_at timestamptz NOT NULL DEFAULT now(),
    status text NOT NULL,
    final_grade text,
    completed_at timestamptz,

    PRIMARY KEY (student_id, course_id)
);

These additional columns are not merely convenient extras attached to a normalization artifact. They expose the deeper purpose of the table.

The relationship is part of the data model.

Bridge Tables Are Not Only for Many-to-Many Relationships

Many-to-many relationships are where bridge tables are normally required. They are not the only place bridge tables can be useful.

Consider an ordinary one-to-many relationship between employees and departments.

Each employee belongs to one department. A department can contain many employees.

The conventional design puts the foreign key on the many side:

CREATE TABLE employees (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    department_id bigint NOT NULL REFERENCES departments(id)
);

For many applications, that is exactly the right design.

But now suppose we need to store information about the assignment:

assigned_at
assigned_by
assignment_reason
approval_status
effective_until

Where does that information belong?

It does not describe the department itself. It does not necessarily describe the employee independently of the department.

It describes the edge between them.

We could put all of those columns on employees:

CREATE TABLE employees (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,

    department_id bigint NOT NULL REFERENCES departments(id),
    department_assigned_at timestamptz NOT NULL,
    department_assigned_by bigint REFERENCES users(id),
    department_assignment_reason text,
    department_assignment_status text
);

That can work. If the employee has exactly one department and the assignment information is inseparable from the employee’s current state, there may be no reason to introduce another table.

But we can also make the relationship explicit:

CREATE TABLE employee_department_assignments (
    employee_id bigint PRIMARY KEY REFERENCES employees(id),
    department_id bigint NOT NULL REFERENCES departments(id),

    assigned_at timestamptz NOT NULL DEFAULT now(),
    assigned_by_user_id bigint REFERENCES users(id),
    assignment_reason text,
    approval_status text,
    effective_until timestamptz
);

This is still a one-to-many relationship between departments and employees.

The intermediate table has not magically turned it into many-to-many. The primary key on employee_id ensures that each employee can have at most one current department assignment.

The table exists because the connection deserves to exist as its own row.

That leads to a more useful question than simply asking about cardinality:

Does this connection deserve to exist as its own row?

Cardinality and Relationship Modeling Are Different Decisions

Database design discussions often combine two separate questions.

The first is cardinality:

How many records may be connected on each side?

The second is relationship ownership:

Does any data belong specifically to the connection?

These questions influence each other, but they are not the same question.

A many-to-many relationship usually makes a bridge table structurally necessary.

A one-to-many relationship does not require a bridge table, but it may still benefit from one when the edge has its own attributes, lifecycle, identity, or behavior.

The same principle can even apply to a one-to-one relationship.

Suppose every user may have one current security clearance, and every current clearance belongs to one user. If the relationship needs an approval chain, effective date, expiration date, issuing authority, and revocation history, it may deserve its own record rather than being represented by a single foreign key.

Many-to-many tells us when a bridge table is necessary.

Edge data tells us when a bridge table is useful.

Why This Matters for Polymorphic Relationships

The edge-oriented view becomes especially useful in polymorphic situations.

Suppose several types of records can be connected to documents:

customers
vendors
employees

A customer can have documents. A vendor can have documents. An employee can have documents.

The same document might even be connected to multiple types of records for different reasons.

The relationship may need information such as:

purpose
visibility
attached_at
attached_by
approval_status
notes

Those values do not necessarily belong to the document.

A document might be public in one context and confidential in another. It might be proof of identity for a customer, a signed contract for a vendor, and an employment record for an employee.

The properties belong to the particular connection.

One approach is to create a separate association table for each type:

CREATE TABLE customer_documents (
    customer_id bigint NOT NULL REFERENCES customers(id),
    document_id bigint NOT NULL REFERENCES documents(id),

    purpose text NOT NULL,
    visibility text NOT NULL,
    attached_at timestamptz NOT NULL DEFAULT now(),

    PRIMARY KEY (customer_id, document_id)
);

CREATE TABLE vendor_documents (
    vendor_id bigint NOT NULL REFERENCES vendors(id),
    document_id bigint NOT NULL REFERENCES documents(id),

    purpose text NOT NULL,
    visibility text NOT NULL,
    attached_at timestamptz NOT NULL DEFAULT now(),

    PRIMARY KEY (vendor_id, document_id)
);

This provides ordinary foreign-key enforcement, but it creates multiple structurally similar tables.

Another option is a single polymorphic association table:

CREATE TABLE document_associations (
    entity_type text NOT NULL,
    entity_id bigint NOT NULL,

    document_id bigint NOT NULL REFERENCES documents(id),

    purpose text NOT NULL,
    visibility text NOT NULL,
    attached_at timestamptz NOT NULL DEFAULT now(),
    attached_by_user_id bigint REFERENCES users(id),

    PRIMARY KEY (entity_type, entity_id, document_id)
);

A row might contain:

entity_type = 'customer'
entity_id = 17
document_id = 42

Another row might contain:

entity_type = 'vendor'
entity_id = 8
document_id = 42

SQL has no problem storing or querying this structure. We can join whatever compatible values we want:

SELECT
    customer.id,
    customer.name,
    association.purpose,
    document.filename
FROM document_associations AS association
JOIN customers AS customer
    ON association.entity_type = 'customer'
   AND association.entity_id = customer.id
JOIN documents AS document
    ON document.id = association.document_id;

The limitation is not that a relational database cannot represent a polymorphic association.

The limitation is that a conventional foreign-key constraint cannot dynamically choose its target table based on the value of another column.

We cannot declare a normal constraint that means:

If entity_type is 'customer',
entity_id must exist in customers.

If entity_type is 'vendor',
entity_id must exist in vendors.

A foreign key points to one fixed relation.

We can leave the constraint off and enforce correctness elsewhere. The application can validate the reference before inserting it. Writes can go through a stored procedure. A trigger can perform the appropriate lookup. Database permissions can prevent callers from bypassing those controlled write paths.

All of those approaches are possible.

The tradeoff is that we have exchanged declarative referential integrity for flexibility.

That may be worthwhile. It may not. The important point is that the association table gives the polymorphic edge a place to store its own data.

The Edge Can Have a Lifecycle

Moving the relationship into a table also allows the connection to have a lifecycle distinct from either entity.

Consider an employee’s department assignment again.

If we store only the current department_id on employees, changing departments overwrites the previous connection:

UPDATE employees
SET department_id = 12
WHERE id = 47;

The database now knows the employee belongs to department 12. It no longer knows that the employee previously belonged to department 4.

We could add audit logging, but an assignment table can represent the history directly:

CREATE TABLE employee_department_assignments (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    employee_id bigint NOT NULL REFERENCES employees(id),
    department_id bigint NOT NULL REFERENCES departments(id),

    effective_at timestamptz NOT NULL,
    ended_at timestamptz,

    assigned_by_user_id bigint REFERENCES users(id),
    assignment_reason text
);

Now an employee can have many assignment records over time while still having only one active department at any given moment.

That final rule can be enforced with an appropriate constraint, such as a partial unique index:

CREATE UNIQUE INDEX one_active_department_per_employee
ON employee_department_assignments (employee_id)
WHERE ended_at IS NULL;

The historical model has more rows, but those rows represent something real: relationships that existed during different periods.

The edge has its own beginning, end, source, and state.

Why Not Put the Edge Data in JSON?

A JSON column can contain all the same values:

{
  "department_id": 12,
  "assigned_at": "2026-07-28T15:30:00Z",
  "assigned_by_user_id": 8,
  "assignment_reason": "Reorganization",
  "approval_status": "approved"
}

PostgreSQL can query and index JSONB. The data is not inaccessible merely because it is stored inside a document.

The distinction is structural.

With JSONB, the database sees one value belonging to the employee row.

With an association table, the database sees an assignment row connected to an employee and a department.

The association table makes ordinary relational operations more natural:

foreign-key enforcement
uniqueness constraints
joins
aggregations
partial indexes
row-level permissions
independent updates
independent deletion
history
auditing

JSONB is useful when the structure is flexible, imported, temporary, sparse, or primarily consumed as one document.

It becomes less attractive when the embedded object has stable fields, references other entities, is queried independently, or develops a lifecycle of its own.

The question is not whether JSONB can hold the data.

It can.

The question is whether JSONB is hiding a relationship that the database should understand as a relationship.

When the Direct Foreign Key Is Better

None of this means that every foreign key deserves an association table.

This is often the best design:

CREATE TABLE employees (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    department_id bigint NOT NULL REFERENCES departments(id)
);

A direct foreign key is simple, obvious, and easy to query.

Adding a relationship table introduces another join, another table, another set of indexes, and another object that application code must manage.

The direct foreign key is usually preferable when:

• the relationship has no meaningful attributes of its own;
• the relationship has no lifecycle separate from the child row;
• no relationship history is required;
• the foreign key accurately represents the child’s current state;
• the extra table would add structure without adding meaning.

A bridge table should not be added merely because it might someday become useful.

It should be added when the edge is already carrying information that does not cleanly belong to either endpoint—or when the edge needs identity, history, constraints, or behavior of its own.

Does the Connection Deserve a Row?

The standard lesson remains useful:

Use a bridge table to resolve a many-to-many relationship into two one-to-many relationships.

But it should not be the end of the lesson.

A better mental model is:

A bridge table promotes a relationship into a record.

That record may be required because the relationship is many-to-many.

It may also be useful because the relationship has attributes, history, permissions, provenance, status, or behavior.

The cardinality tells us how many connections are allowed.

The bridge table tells us that the connections themselves matter.

So the next time a foreign key begins accumulating columns with names like:

assigned_at
assigned_by
relationship_status
relationship_type
relationship_source
relationship_notes

stop and ask:

Does this data belong to the entity, or does it belong to the edge?

And then ask the more important question:

Does this connection deserve to exist as its own row?