Translating ER Diagrams to Relational Tables

What it is#

ER-to-relational translation is the mechanical procedure for turning a finished ER diagram into a set of CREATE TABLE statements. The rules have been stable since the late 1970s and every relational DBMS expects schemas to be shaped this way. There are seven of them — one per entity kind and relationship kind — and once internalised they let you sketch on a whiteboard in ER and write SQL straight from the sketch.

The translation is not a creative step. The creative work happened during ER design (picking entities, choosing keys, deciding cardinalities). Translation just applies the rules. When two engineers produce different SQL from the same ER diagram, one of them broke a rule.

When to use it#

The translation procedure is the right move whenever:

• You’ve finished an ER sketch and need to commit it to DDL.
• You’re reviewing someone else’s schema and want to reverse-engineer the ER it was supposed to encode — to spot where it deviated.
• You’re answering “design a schema for X” in an interview and want the SQL to come out consistent with the diagram you just drew.
• You’re translating a JSON / document / domain model into a relational store and want a defensible mapping.

Skip the procedure when the system is genuinely non-relational (a graph database doesn’t need this; a document store benefits from a different normalisation). For relational targets, the rules are the contract.

How it works#

Take a worked example. ER diagram for a simple e-commerce model:

Customer  ──places──< Order  >──contains──< LineItem
   1         (1, N)    (1, N)            (weak, owned by Order)
                          │
                          │ contains  >──── Product
                          │  (M, N)

Rule 1 — Strong entities become tables#

Each strong entity becomes a table; its attributes become columns; its primary key becomes the table’s primary key.

CREATE TABLE customer (
  customer_id bigserial PRIMARY KEY,
  email       text UNIQUE NOT NULL,
  name        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE product (
  product_id bigserial PRIMARY KEY,
  sku        text UNIQUE NOT NULL,
  name       text NOT NULL,
  unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0)
);

Rule 2 — Weak entities become tables with composite PK#

A weak entity’s primary key is (owner_pk, partial_key). The FK to the owner is part of the PK and almost always ON DELETE CASCADE.

CREATE TABLE line_item (
  order_id    bigint NOT NULL,
  line_number int    NOT NULL,
  product_id  bigint NOT NULL REFERENCES product (product_id),
  quantity    int    NOT NULL CHECK (quantity > 0),
  unit_price  numeric(12, 2) NOT NULL,
  PRIMARY KEY (order_id, line_number),
  FOREIGN KEY (order_id) REFERENCES "order" (order_id) ON DELETE CASCADE
);

Rule 3 — One-to-one relationships#

Add the FK on either side and declare it UNIQUE. The natural choice is the side that’s optional or added later.

-- 1:1 between User and UserProfile
CREATE TABLE user_profile (
  user_id    bigint PRIMARY KEY REFERENCES customer (customer_id),
  bio        text,
  avatar_url text
);

Rule 4 — One-to-many relationships#

The FK goes on the “many” side. Nullable if the cardinality allows zero on the one side (partial participation on the many side); NOT NULL if total participation is required.

CREATE TABLE "order" (
  order_id    bigserial PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customer (customer_id),
  placed_at   timestamptz NOT NULL DEFAULT now(),
  status      text NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled'))
);

Rule 5 — Many-to-many relationships#

A junction (associative) table with one FK per side and a composite primary key on the pair. Relationship attributes become extra columns.

-- M:N between Customer and Product via Wishlist (with added_at attribute)
CREATE TABLE wishlist (
  customer_id bigint NOT NULL REFERENCES customer (customer_id) ON DELETE CASCADE,
  product_id  bigint NOT NULL REFERENCES product (product_id)  ON DELETE CASCADE,
  added_at    timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (customer_id, product_id)
);

Rule 6 — Multi-valued attributes#

A multi-valued attribute (phone_numbers) becomes a separate table with a FK back to the owning entity.

CREATE TABLE customer_phone (
  customer_id bigint NOT NULL REFERENCES customer (customer_id) ON DELETE CASCADE,
  phone       text   NOT NULL,
  PRIMARY KEY (customer_id, phone)
);

Rule 7 — Subclass / specialisation#

A Person with subtypes Employee and Customer can be translated three ways — see Variants below.

Variants#

The rules above are deterministic except for two open choices: how to represent inheritance and how to represent ternary relationships.

Inheritance — three patterns#

• Single-table inheritance. One table for the supertype, all subtype columns added as nullable. Simple, fast joins, lots of NULLs. Right when subtypes differ by a few columns each.
• Class-table inheritance. One table per type — Person with shared columns, Employee and Customer with subtype-specific columns, all FK’d to Person. No NULLs, but every read touches two tables.
• Concrete-table inheritance. One table per concrete subtype, no shared parent table. Each repeats the shared columns. Fastest reads, awkward when a query needs all “persons” regardless of subtype.

Ternary relationships#

A ternary R between A, B, C usually becomes a junction table R(a_id, b_id, c_id, ...attrs) with composite PK (a_id, b_id, c_id). Some ternaries decompose losslessly into binaries; most don’t. Check before assuming.

Unary (recursive) relationships#

Employee —reportsTo→ Employee becomes an Employee table with a self-referencing FK column (manager_id). Hierarchy queries use recursive CTEs.

Identifying-relationship choice#

For weak entities, prefer the composite PK from rule 2. If many other tables will reference the child, a surrogate ID with a UNIQUE (owner_id, partial_key) constraint is an acceptable variant — see Weak Entities.

Trade-offs#

Other trade-offs worth naming:

• Junction tables — composite PK vs surrogate PK. Composite PK directly prevents duplicate edges and is the ER-faithful translation. Surrogate PK with UNIQUE (a_id, b_id) is sometimes added when the edge needs to be referenced from elsewhere.
• Nullable FK columns. A nullable FK is fine and often correct — it encodes partial participation. But many “nullable for a few rows” cases are actually a missed subtype or a missed weak entity; check before defaulting to nullable.
• Inheritance pattern choice. No universally right answer; depends on read patterns. Single-table for many small subtypes; class-table for subtypes with very different attribute sets and lots of subtype-specific queries.
• Denormalisation at translation time. Tempting to fold a 1:1 into the parent table or pre-aggregate a 1:N. Almost always premature — translate faithfully first, denormalise only with a measured workload reason.

Common pitfalls#

• Flattening M:N into a comma-separated column. tags VARCHAR(255) storing 'red,blue,green' is not a junction table. It breaks indexes, breaks uniqueness, breaks every query that wants “all rows tagged X”. Always a junction table for M:N.
• Missing ON DELETE on weak entity FKs. A weak entity orphaned by its owner is data corruption. ON DELETE CASCADE should be the default for FKs from weak children.
• Wrong side of the FK for 1:1. Putting the FK on the side that exists first means every insert into that side has to provide a value for the side that doesn’t exist yet. Put the FK on the optional or later side.
• Forgetting the UNIQUE on a 1:1 FK. Without it, the schema technically allows 1:N — a silent constraint loss.
• Translating multi-valued attributes as TEXT. A single phones TEXT column stores '+1-555,+1-666' and breaks every downstream lookup. Make it a separate table.
• Dropping participation constraints. Total participation translates to NOT NULL on the FK; partial leaves it nullable. Forgetting this defaults silently to nullable and lets orphaned-on-one-side rows in.
• Treating ternaries as three binaries without checking. Decompose only when you’ve verified the three pairwise facts can never be inconsistent with the three-way fact. Most ternaries can’t be decomposed this way.

Related building blocks#

• Entity-Relationship Model — Entities, Attributes, Keys
• ER Relationships and Constraints
• Weak Entities and Identifying Relationships
• Relational Model Concepts
• Keys — Primary, Candidate, Foreign, Composite