ER Relationships and Constraints

Summary#

After entities and attributes, the second half of the ER model is relationships — named associations between entity sets — and the constraints that pin down how those associations can be populated. Three constraint families do almost all the work: degree (how many entity sets participate — binary, ternary), cardinality (the min/max number of entities on each side — one-to-one, one-to-many, many-to-many), and participation (whether every entity on one side must be in the relationship — total or partial).

Get these three right at the ER stage and the relational translation is mechanical. Get them wrong and you discover the bug months later, when a unique constraint refuses an insert that shouldn’t fail.

Why it matters#

Relationships are where most schema bugs live. The customer table is fine, the order table is fine, but the join between them encodes a business rule — “an order has exactly one customer; a customer can have many orders” — that has to be enforced somewhere. Choose the wrong cardinality and you’ve allowed orderless customers when the business requires every order to have a buyer, or you’ve allowed multi-buyer orders when the platform only supports one.

In interviews, “design a schema for X” answers always include relationship cardinalities, usually drawn or said aloud as “one-to-many between users and orders”. Senior interviewers push further: “is the user side total or partial?” — meaning, can a user exist with zero orders, or must every user have at least one? That’s the participation constraint, and it determines whether the FK column is nullable.

How it works#

Relationship type vs instance#

A relationship type is the named association at the schema level — OrderedBy between Order and User. A relationship instance is a specific pairing — order #42 ordered-by user #7. Same model/schema/instance distinction as for entities.

Degree#

The number of entity sets a relationship connects:

• Unary (recursive) — a relationship from an entity set to itself. Employee —reportsTo→ Employee. Translation: an Employee table with a manager_id FK pointing at the same table.
• Binary — two distinct entity sets. The vast majority of real-world relationships. User —places→ Order.
• Ternary — three entity sets at once. Supplier —supplies→ (Product, Project) — a fact that says “this supplier supplies this product to this project”. Cannot be decomposed into three binaries without losing information.
• N-ary — degree four or higher. Rare; usually means the model needs a separate associative entity.

Cardinality#

How many entities on one side can be associated with one entity on the other. The four classic shapes:

• One-to-one (1:1). Each User has at most one UserProfile. Translation: put the FK on either side with a UNIQUE constraint.
• One-to-many (1:N). Each User has many Orders; each Order belongs to one User. Translation: FK from Order to User, no extra constraint.
• Many-to-one (N:1). Same shape as 1:N, just from the other side. Convention is to write it the same way.
• Many-to-many (M:N). A User follows many Users; a User is followed by many Users. Translation: a junction (associative) table with two FKs and usually a composite primary key.

   ┌─────────┐  1   N  ┌─────────┐
   │  User   │─────────│  Order  │   one-to-many
   └─────────┘         └─────────┘

   ┌─────────┐  M   N  ┌─────────┐
   │  User   │────×────│  User   │   many-to-many (follow)
   └─────────┘   │     └─────────┘
                 ▼
            ┌─────────┐
            │ Follows │  junction
            └─────────┘

Participation (existence)#

Whether every entity on one side must participate in the relationship at all:

• Total participation. Every entity must participate. “Every order has a customer.” Drawn as a double line in Chen notation; expressed as NOT NULL on the FK column in the relational translation.
• Partial participation. Some entities may not. “Not every user has placed an order.” Single line; the FK on the other side is NULLable where applicable, but more often this is captured by the absence of a row in the junction table.

Attributes on relationships#

A relationship can carry its own attributes — facts about the association, not about either entity. Enrolled between Student and Course has grade and enrolled_on — these belong to the enrollment, not to the student or the course. In the relational translation, attributes on a many-to-many relationship become extra columns on the junction table; attributes on a one-to-many usually fold into the “many” side.

Relationship role names#

When the same entity set appears twice in one relationship (unary, or a binary like Marriage between two Persons), role names disambiguate the sides. Employee —reportsTo→ Employee has roles subordinate and manager. The FK column should be named for the role (manager_id), not for the type (employee_id).

Variants and trade-offs#

Other choices that come up:

• One-to-one — which side gets the FK? Usually the side that’s optional or that adds the relationship later. If every User has a UserProfile from day one, FK on either is fine; if UserProfile is added later, put the FK on UserProfile so existing Users aren’t touched.
• Many-to-many — composite key vs surrogate key on the junction. Composite (user_id, role_id) is natural and prevents duplicates by definition. A surrogate id makes the row referenceable from elsewhere (an audit log, a soft-delete row) but lets duplicates slip in unless you also add a unique constraint on the pair.
• Enforcing total participation. SQL has no direct “every row in A must appear in B” constraint. The patterns are: a deferred FK check inside a transaction, a circular FK created last and validated, or a trigger. Usually it’s enforced in the application instead.
• Cardinality drift over time. A relationship that started as 1:1 (one credit card per user) often becomes 1:N when product requirements change. Designing for the looser cardinality up front avoids a migration.

When this is asked in interviews#

Two main shapes. As a schema design — “model the relationships for an e-commerce site” — the expected motion is to draw or describe each relationship with cardinality and participation, then translate to tables. Senior interviewers will probe specifically: “is User —> Address one-to-many or many-to-many? what if two users share an address?” — pushing you to think about cardinality rather than guess.

As a constraints question — “how would you enforce that every order has a customer in the database?” — the answer is a NOT NULL FK column, which directly encodes total participation on the Order side.

A common trap is implementing M:N as a single column with comma-separated IDs (tags: 'red,blue,green'). The expected response is “junction table” and a one-sentence explanation of why the comma-separated version breaks every constraint and index argument.

Related concepts#

• Entity-Relationship Model — Entities, Attributes, Keys
• Weak Entities and Identifying Relationships
• Translating ER Diagrams to Relational Tables
• Keys — Primary, Candidate, Foreign, Composite
• Data Models, Schemas, and Instances