skip to content

Walk through the standard algorithm for turning a finished entity-relationship diagram into a set of relational tables: what does each construct — entity, attribute, relationship — become?

level: middleimportance: must knowfreq 55%

answer

  1. entities first, keys exist before FKs
  2. composite -> flatten to columns
  3. multivalued -> child table
  4. 1:N -> FK on the many side
  5. M:N -> its own table

basics

~20 s

Each strong entity becomes a table keyed by its own key. Composite attributes become their component columns. Multivalued attributes become child tables. Weak entities become tables keyed by owner key plus discriminator. One-to-many relationships become a foreign key on the many side; many-to-many becomes its own table.

solid answer

~60 s

The algorithm runs in a fixed order so keys exist before anything refers to them. 1. **Strong entities** → one table each, taking all simple attributes; choose its key. 2. **Composite attributes** → flattened into their component columns; the composite itself has no column. 3. **Derived attributes** → normally not stored, or stored deliberately with a refresh mechanism. 4. **Weak entities** → one table each, keyed by the owner's key plus the discriminator, with the owner key as a foreign key. 5. **Multivalued attributes** → a child table holding the owner key plus the value, keyed by both. 6. **1:N relationships** → a foreign key on the many side, referencing the one side. 7. **1:1 relationships** → a foreign key on one side, normally the side whose participation is mandatory. 8. **M:N relationships** → their own table holding both keys. 9. **Relationship attributes** → wherever the relationship landed: the many-side table for 1:N, the junction table for M:N. Afterwards I sanity-check keys and nullability, and only then consider physical adjustments.

code

sql · 19 lines
sql
CREATE TABLE customer (
  customer_id   BIGINT PRIMARY KEY,
  name          VARCHAR(200) NOT NULL,
  street        VARCHAR(200),
  city          VARCHAR(100),
  postcode      VARCHAR(20)
);

CREATE TABLE customer_phone (
  customer_id   BIGINT NOT NULL REFERENCES customer(customer_id),
  phone         VARCHAR(30) NOT NULL,
  PRIMARY KEY (customer_id, phone)
);

CREATE TABLE sales_order (
  order_id      BIGINT PRIMARY KEY,
  customer_id   BIGINT NOT NULL REFERENCES customer(customer_id),
  placed_at     TIMESTAMP NOT NULL
);

go deeper

for a junior

Recite the core mappings: entity to table, one-to-many to a foreign key on the many side, many-to-many to a junction table.

for a middle

Give the ordered algorithm including composite, multivalued, derived and weak-entity handling, and justify each choice from cardinality.

for a senior

Add the review pass — nullability against declared optionality, key workability, and constraints from the diagram that no ordinary constraint can express.

for a principal

Position the algorithm as the neutral baseline and discuss which deviations are legitimate, who owns the rules the schema cannot hold, and how model errors surface as schema arguments.

## Why an algorithm at all An ER diagram is deliberately implementation-free: it has entities, attributes and relationships, and no notion of tables, foreign keys or nulls. The mapping algorithm is a mechanical procedure that turns it into relations, so two people mapping the same diagram get the same schema and no construct is silently dropped. Interviewers use it because it exposes whether a candidate understands *why* a many-to-many needs an extra table rather than just remembering that it does. ## Step 1 — strong entities Every strong (regular) entity becomes one table. Its simple attributes become columns; its key attribute becomes the primary key. If the entity has no convincing natural key, this is where a surrogate key is introduced. ## Step 2 — composite attributes A composite attribute has no column of its own. Its leaves become columns: an Address composite yields street, city and postcode columns. Flattening is what makes the parts queryable; keeping the composite as one text field is the standard way to lose that. ## Step 3 — derived attributes A derived attribute is computable from other data, so by default it gets no column and is produced by a query or a view. Storing it is a deliberate performance decision that comes with an obligation to keep it in step with its inputs. ## Step 4 — weak entities A weak entity becomes a table whose primary key is the owner's primary key plus its own discriminator, and whose owner-key columns are also a foreign key to the owner. That composite key is the direct expression of "unique only within its owner". ## Step 5 — multivalued attributes A multivalued attribute becomes its own table holding the owner's key and one value per row, keyed by the pair, with a foreign key back to the owner. This is what removes repeating columns and delimited strings from the design. ## Step 6 — binary relationships by ratio **1:N**: put a foreign key on the many side referencing the one side. No extra table is needed, because each row on the many side has at most one partner. If the many side's participation is total the column is not nullable; if partial it is. **1:1**: also a foreign key, on one of the two sides, plus a uniqueness constraint so "at most one" holds in both directions. Placing it on the side with mandatory participation avoids a column that is mostly empty. **M:N**: neither side can hold a single foreign key, because both sides have many partners, so the relationship gets its own table containing the two keys, with foreign keys to both parents and a key over the pair. ## Step 7 — relationship attributes Attributes of a relationship follow the relationship. For 1:N they become columns on the many-side table, since that row already represents the single association. For M:N they become columns in the junction table, the only place the pair exists. Putting a per-pair attribute on either parent is the classic error and forces one value to serve all partners. ## Step 8 — higher-degree relationships A ternary relationship becomes its own table holding the three participants' keys plus any relationship attributes, keyed by whichever combination the cardinality constraints allow — all three in the general case. ## After the mechanical pass The output is a correct but naive schema, and it is worth a review pass. Check that every foreign key's nullability matches the optionality the diagram declared. Check that natural composite keys are still workable where they will be referenced repeatedly. Check that constraints the diagram stated in words — "at least one", cardinality on a ternary — have somewhere to live, since some of them cannot be expressed as ordinary constraints. Only then consider physical decisions such as indexing. ## The property that makes it trustworthy The algorithm is designed so nothing is lost and nothing is invented: every diagram construct produces exactly one schema construct, and each choice is forced by cardinality and participation rather than by taste. When two engineers disagree about the resulting schema, the disagreement is almost always about the diagram — the cardinality was wrong — not about the mapping.

  • Why is a one-to-many relationship mapped with a foreign key rather than its own table?
    Because each row on the many side participates in at most one such association, so a single column on that row records it without repetition. An extra table would store exactly the same pairs at the cost of a join, and would need a uniqueness constraint on the many-side key to stop it representing a many-to-many. The foreign key is both smaller and more constrained.
  • What does the mapping algorithm do with a derived attribute?
    By default it produces no column, since the value follows from other data and can be computed by a query or exposed through a view. Storing it is a deliberate exception made for read cost or because the value must be frozen at a point in time, and it commits you to a refresh path and to accepting possible drift from its inputs.

saying these in an interview costs you the question

  • Creating a junction table for a one-to-many relationship because 'relationships get tables'
  • Giving a composite attribute a single column instead of flattening it into its parts
  • Storing a multivalued attribute as repeated columns or a delimited string rather than a child table
  • Ignoring optionality, so every foreign key ends up nullable regardless of what the diagram said
  • Treating the mechanical output as final without checking keys, nullability and unenforceable constraints

context

resolving links…