Chapter 18: From ER Diagram to Tables — Translating Your Design into DDL

Where you are: Part III, Chapter 18 of 40. Chapter 17 gave you a conceptual ER model. This chapter is the mechanical bridge — a small set of learnable rules that turn any ER diagram into concrete tables, keys, and foreign keys (the DDL of Chapter 14).

Learning paths: 💻 📊 🔬 🏗️ — everyone. The mapping rules are the same everywhere; 🔬 CS students will recognize them as the standard ER-to-relational algorithm.

A model is not yet a schema

An ER diagram describes your domain; a schema is tables PostgreSQL can build. Translating one to the other is not guesswork — it's a handful of rules applied consistently. Learn them once and you can turn any ER model (yours, a textbook's, a whiteboard's) into correct DDL almost mechanically. We'll apply each rule to Mercado, so you can see the model behind the schema you've been using since Chapter 2.

The mapping rules

Rule 1 — Each (strong) entity becomes a table

Map each entity to a table; its attributes become columns; its key attribute becomes the primary key (prefer a surrogate IDENTITY key, Chapter 3). The customers entity →:

CREATE TABLE customers (
    customer_id  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name   text NOT NULL,
    last_name    text NOT NULL,
    email        text NOT NULL UNIQUE,    -- a natural key, protected with UNIQUE
    ...
);

Pick a concrete PostgreSQL type for each attribute (Chapter 14) and add constraints that encode the attribute's rules (NOT NULL for required, CHECK for valid ranges/sets).

Rule 2 — Composite attributes → component columns

A composite attribute (an address = line + city + state + postal_code) becomes several columns, not one. Mercado's addresses table splits the address into line1, line2, city, state, postal_code, country — each independently queryable and constrainable (you can filter by state, which a single address text blob couldn't do well).

Rule 3 — Multi-valued attributes → a separate table

A multi-valued attribute (a customer's several addresses) can't be columns on the entity — it becomes its own table with a foreign key back to the owner. That's exactly why addresses is a separate table linked by customer_id, not three address-columns on customers:

CREATE TABLE addresses (
    address_id  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers (customer_id) ON DELETE CASCADE,
    ...
);

Rule 4 — One-to-many relationship → foreign key on the "many" side

A 1:N relationship is implemented by putting a foreign key on the "many" side, pointing to the "one" side. One customer has many orders → orders carries customer_id:

CREATE TABLE orders (
    order_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers (customer_id),   -- the FK on the "many" side
    ...
);

Participation decides nullability: total (every order must have a customer) → NOT NULL; partial (an order may have no rep) → nullable (orders.employee_id). And set the referential action deliberately (ON DELETE CASCADE/RESTRICT/SET NULL, Chapter 14) based on what should happen when the "one" is deleted.

Rule 5 — Many-to-many relationship → a junction table

An M:N relationship cannot be a single foreign key — it becomes a junction table whose primary key is the combination of the two foreign keys. Orders ↔ products → order_items:

CREATE TABLE order_items (
    order_id   integer NOT NULL REFERENCES orders (order_id)   ON DELETE CASCADE,
    product_id integer NOT NULL REFERENCES products (product_id),
    quantity   integer NOT NULL CHECK (quantity > 0),     -- relationship attributes live here
    unit_price numeric(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)                     -- composite PK of the two FKs
);

If the relationship carries its own attributes (quantity, unit_price, a grade, a tag-applied-date), they become columns on the junction table — that's where they belong, because they describe the pairing, not either entity alone.

Rule 6 — One-to-one relationship → FK with UNIQUE (or merge)

A 1:1 relationship is a foreign key on one side made UNIQUE (so at most one match), or — often better — the two entities are merged into one table if they always coexist. Split them (with a UNIQUE FK) when one side is optional, accessed separately, or has different security needs (e.g., users and user_secrets).

Rule 7 — Weak entity → table with a composite key including the owner's key

A weak entity (one that can't be identified alone) becomes a table whose primary key includes the owner's key. order_items and inventory are exactly this — their identity is the combination (order_id, product_id) / (product_id, warehouse_id).

Self-referencing relationships → FK to the same table

A relationship from an entity to itself (category→parent category, employee→manager) is a foreign key referencing the same table:

parent_category_id integer REFERENCES categories (category_id)   -- self-reference
manager_id         integer REFERENCES employees (employee_id)    -- self-reference

These power the recursive queries of Chapter 11.

Mapping inheritance (generalization)

Sometimes an entity has subtypes — a payment that's a card_payment or a bank_transfer, each with extra attributes. There are three standard ways to map such inheritance, each with trade-offs:

1. Single-table inheritance — one table for everything, with a type column and all subtype columns (nullable for the types that don't use them).

   payments(payment_id, type, amount, card_last4, bank_routing, ...)  -- many nullable cols

Pros: simple, no joins, easy to query all payments. Cons: many nullable columns; the database can't enforce "card payments must have card_last4" without CHECKs on type.

2. Table-per-subclass (class-table inheritance) — a base table plus one table per subtype, linked 1:1.

   payments(payment_id, amount, ...)
   card_payments(payment_id → payments, card_last4, ...)
   bank_payments(payment_id → payments, routing, ...)

Pros: clean, no wasted nulls, each subtype's attributes are required where they belong. Cons: querying a full payment needs a join.

3. Table-per-concrete-class — a separate, complete table per subtype, no shared base.

   card_payments(... all columns ...)
   bank_payments(... all columns ...)

Pros: no joins per type. Cons: no easy "all payments" query (must UNION); shared attributes duplicated.

Which to choose? Single-table for simple, few-attribute subtypes that you usually query together; table-per-subclass when subtypes have substantial distinct attributes and integrity matters; table-per-concrete rarely. (Mercado keeps payments single-table with a method column — the simplest fit for its needs.)

A worked mapping

Take a slice of Mercado's model and map it end to end:

   ER:  category ──1──< product >──N──M── order ──1──< shipment
        (1:N)          (M:N via order_items)        (1:N)

• categories (entity) → table, PK category_id, self-ref parent_category_id.
• products (entity) → table, PK product_id, FK category_id (Rule 4, the 1:N).
• orders↔products (M:N) → order_items junction, PK (order_id, product_id), with quantity/unit_price (Rule 5).
• orders (entity) → table, FK customer_id (Rule 4).
• shipments (entity) → table, FK order_id (Rule 4, the 1:N from orders).

Apply the rules in order and the full Mercado schema (sql/schema.sql) emerges — every table, key, and foreign key is a rule applied to the ER model.

Why the rules work

The mapping rules can feel arbitrary until you see the logic beneath them, and understanding why each rule is what it is turns rote application into genuine comprehension — so you can reason your way through unusual cases the rules don't quite cover. Every rule traces back to a single goal: representing the relationship faithfully using only what the relational model offers, which is tables, columns, and foreign-key values.

Consider the central rule — a 1:N relationship becomes a foreign key on the many side. Why the many side, and not the one side? Because a foreign key is a single value in a column, and it can point to exactly one row. On the many side (an order), each row relates to exactly one row on the one side (a customer), so a single foreign-key value (customer_id) captures that relationship perfectly. Try it the other way — a foreign key on the one side (a customer) pointing to its orders — and you immediately hit the wall: a customer has many orders, but a column holds one value. You'd need to cram many order-ids into one column, which violates atomicity and recreates the comma-separated-list disaster. The rule isn't a convention; it's forced by the fact that a column holds one value and the foreign key must go where "one value" is the truth. The many side is the side where each row has exactly one partner, so it's the only side a foreign key can live.

The M:N rule follows from the same logic taken one step further. In a many-to-many relationship, neither side has "exactly one partner" — an order has many products, a product appears in many orders — so a foreign key fits on neither side. There's nowhere to put the single value. The resolution is to invent a new table (the junction) whose every row represents one pairing — one specific product in one specific order. Now each junction row does have exactly one order and exactly one product, so two foreign keys (one to each) fit perfectly, and the combination is unique (a composite primary key). The junction table exists precisely because M:N has no "one" side to anchor a foreign key, so you manufacture rows that each represent a single pairing, restoring the "one partner per row" condition that foreign keys require. Seen this way, the junction table isn't a special trick to memorize — it's the inevitable consequence of needing single-valued foreign keys to represent a relationship where neither side is single-valued.

Even the multi-valued-attribute rule is the same insight wearing different clothes. A customer with several phone numbers can't store them in one column (atomicity again), so the phones become their own table with a foreign key back to the customer — which is just a 1:N relationship (one customer, many phones) resolved by the standard rule. Once you see that all of these rules are about honoring "a column holds one value" and "a foreign key points to one row," they stop being a list to memorize and become obvious consequences of how the relational model represents connections. That understanding is what lets you handle the cases the textbook rules don't name: you ask "where does the single foreign-key value belong, given who has exactly one partner?" and the structure reveals itself.

Junction tables in depth

Junction tables are worth dwelling on, because they're where many-to-many relationships live and where a surprising amount of real-world data complexity resides. A junction table at minimum holds two foreign keys, one to each entity it connects, with their combination as the primary key — order_items joining orders and products via (order_id, product_id). But the most important thing about junction tables is what else they can hold, because relationships often carry their own data.

Consider what order_items stores beyond the two foreign keys: quantity and unit_price. These attributes belong to neither the order alone nor the product alone — they describe the pairing. "How many of this product in this order" is a fact about the specific combination, not about the order in general or the product in general. This is the key insight about relationship attributes: when a relationship carries data, that data lives on the junction table, because the junction row is the only thing that represents the pairing the data describes. A student's grade in a course belongs on the enrollments junction (it's the grade for that student-course pair, not a property of the student or the course alone). A tag's date applied belongs on the item_tags junction. Recognizing that a relationship has its own attributes — and putting them where they belong — is a frequent point of confusion and an important skill.

This also illuminates the boundary between a "pure" junction and a junction that has become a full entity (the link-entity idea from Chapter 17). A pure junction has only the two foreign keys — it merely records that two things are associated. The moment the relationship gains its own attributes (quantity, a grade, a queue position, a start date), the junction has become a relationship-with-properties, and if it gains enough identity it may even deserve a surrogate key of its own and relationships to other tables. order_items sits at this boundary: it's a junction (resolving orders↔products M:N) that carries attributes (quantity, price), which is why it's sometimes called a "junction" and sometimes an "associative entity." The terminology matters less than the recognition: a relationship can be a thin link or a substantial entity, and where it falls determines whether it's a bare two-FK junction or a richer table.

A subtle design choice is whether the junction's primary key should be the composite of the two foreign keys (PRIMARY KEY (order_id, product_id)) or a separate surrogate key with a UNIQUE constraint on the pair. The composite key is the natural choice and Mercado's: it directly expresses "one row per order-product pair" and needs no extra column. A surrogate key on the junction is occasionally preferable — when other tables need to reference individual junction rows (a surrogate gives them a single column to point at), or when the junction is really a full entity in its own right. For most junctions, the composite key is right and cleaner; reach for a surrogate only when something needs to reference the junction row itself. This is the same surrogate-vs-natural-key judgment from Chapter 3, applied to the junction.

A complete worked mapping: the library model

Let's map the entire library model from Chapter 17 to DDL, applying the rules in order, because seeing a full model translated end to end cements the mechanical nature of the process. Recall the model: entities member, book, author, genre, loan, hold, fine; book↔author is M:N; book→genre is 1:N; loan links member and book (with its own attributes); hold links member and book (with a position); fine belongs to a loan.

Apply Rule 1 (entity → table) to each entity. Apply Rule 4 (1:N → FK on many side) to book→genre, putting genre_id on book. Apply Rule 5 (M:N → junction) to book↔author, creating book_authors. The loan, being a link-entity with its own attributes, becomes a table with foreign keys to both member and book (two 1:N relationships meeting at the loan) plus its own columns. Hold and fine follow the same logic.

CREATE TABLE genres (
    genre_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     text NOT NULL UNIQUE
);
CREATE TABLE books (
    book_id  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title    text NOT NULL,
    isbn     text UNIQUE,                                    -- natural key, protected
    genre_id integer NOT NULL REFERENCES genres (genre_id)   -- Rule 4: 1:N, FK on many side
);
CREATE TABLE authors (
    author_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      text NOT NULL
);
CREATE TABLE book_authors (                                  -- Rule 5: M:N junction
    book_id   integer NOT NULL REFERENCES books (book_id)     ON DELETE CASCADE,
    author_id integer NOT NULL REFERENCES authors (author_id) ON DELETE CASCADE,
    PRIMARY KEY (book_id, author_id)                          -- composite PK
);
CREATE TABLE members (
    member_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      text NOT NULL,
    email     text NOT NULL UNIQUE
);
CREATE TABLE loans (                                         -- link-entity with attributes
    loan_id     integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    member_id   integer NOT NULL REFERENCES members (member_id),  -- Rule 4
    book_id     integer NOT NULL REFERENCES books (book_id),      -- Rule 4
    loaned_on   date NOT NULL DEFAULT CURRENT_DATE,
    due_date    date NOT NULL,
    returned_on date,                                        -- nullable: not yet returned
    CHECK (due_date >= loaned_on)
);
CREATE TABLE fines (
    fine_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    loan_id integer NOT NULL REFERENCES loans (loan_id),     -- a fine belongs to a loan
    amount  numeric(8,2) NOT NULL CHECK (amount >= 0)
);

Every table, key, and foreign key is a rule applied to the model — nothing invented, nothing guessed. The 1:N relationships became foreign keys on the many side; the M:N became a junction; the link-entities (loan) became tables with foreign keys to both sides plus their own attributes; participation (a loan must have a member and book) became NOT NULL, while optionality (a loan may not be returned yet) became a nullable returned_on. This is the chapter's central claim made concrete: a sound conceptual model translates almost mechanically into a sound schema. The thinking happened in Chapter 17; here, you apply rules. That's exactly the right division of labor, and it's why the design process puts the hard conceptual work first.

Common mistakes

• Trying to store an M:N without a junction (a foreign key, an array, or comma-separated text). M:N always needs a junction table (Chapter 17's tags case).
• Putting the foreign key on the wrong side of a 1:N. The FK goes on the many side. (Putting order_id on customers makes no sense — a customer has many orders.)
• Forgetting relationship attributes belong on the junction. quantity describes the order-product pairing, so it lives on order_items, not on orders or products.
• Mapping a multi-valued attribute as repeated columns (phone1, phone2, phone3). It becomes a child table.
• Choosing single-table inheritance and then never constraining the nullable columns, so invalid combinations slip in.

Naming conventions: a small discipline with large payoff

Once you're generating tables from a model, naming becomes a real and surprisingly consequential decision, because the names you choose are ones you and your colleagues will type, read, and reason about for the life of the database. Inconsistent naming is a low-grade tax on every query, every join, every conversation about the schema. A few conventions, chosen and applied consistently, pay off enormously.

The first decision is singular versus plural for table names — customer or customers. Both have defenders; what matters is picking one and applying it everywhere. This book and Mercado use plural (customers, orders, products), which reads naturally ("select from customers") and is the more common convention. The second is primary key naming: id versus customer_id. The table-qualified form (customer_id) has a real advantage — when it appears as a foreign key in another table, it's customer_id there too, so joins read symmetrically (orders.customer_id = customers.customer_id) and the column's meaning is unambiguous even out of context. Mercado uses this convention throughout, and it makes the foreign-key relationships self-documenting: you can tell what customer_id references from its name alone.

Beyond these, consistency is the whole game. Foreign keys named after what they reference (category_id referencing categories). Junction tables named for the two things they join, or for the relationship (order_items, book_authors). Columns in snake_case (PostgreSQL's lowercase-friendly convention — avoid quoted mixed-case names that force you to quote them forever). Boolean columns named as questions or states (is_active, not active_flag). Timestamp columns with a consistent suffix (created_at, updated_at). None of these is the One True Way, but each, applied consistently, makes the schema predictable — and a predictable schema is one where you can guess a column's name correctly before looking it up, which compounds into real productivity. The cost of conventions is a moment's decision up front; the cost of no conventions is a thousand small frictions and inconsistencies accumulating over a system's life.

The deeper point is that names are part of the design, not an afterthought. A well-named schema communicates its structure — a newcomer reading orders.customer_id REFERENCES customers understands the relationship instantly, while orders.cust or orders.c_ref forces a lookup and invites error. Since the schema is the long-lived foundation everything else builds on, and since names are the interface through which everyone interacts with it, investing in clear, consistent naming is investing in the comprehensibility of the entire system. Decide your conventions before you map your model, write them down, and apply them without exception.

A field guide to mapping mistakes

Even with the rules in hand, a few mapping errors recur often enough to warrant a checklist. The foreign key on the wrong side of a 1:N: putting the FK on the "one" side instead of the "many" side, which is structurally impossible to do correctly (a column holds one value, but the one side has many partners). Always put the FK where each row has exactly one partner — the many side. M:N without a junction: trying to represent many-to-many with a single foreign key, an array, or comma-separated text — none of which can hold the relationship correctly. M:N always needs a junction table. Relationship attributes in the wrong place: putting quantity on orders or products instead of on the order_items junction where the pairing it describes actually lives. Relationship attributes go on the junction. Multi-valued attribute as numbered columns: phone1, phone2, phone3 — which caps the count arbitrarily and makes "find anyone with this phone" awkward. Multi-valued attributes become child tables. Single-table inheritance left unconstrained: choosing the simple single-table inheritance mapping but never adding the CHECK constraints that enforce "card payments must have a card number," so invalid combinations slip in. If you use single-table inheritance, constrain the type-specific columns.

The unifying cause behind most of these is forgetting that the mapping rules exist to honor the relational model's constraints (single-valued columns, single-target foreign keys) — when you violate a rule, you're usually trying to make a column do something a column can't. The defense is to apply the rules deliberately rather than improvising, and to ask, whenever a mapping feels awkward, "am I trying to put many values where one belongs?" That question catches the FK-on-the-wrong-side error, the M:N-without-junction error, and the numbered-columns error all at once — they're the same mistake in three disguises. The rules, applied faithfully, prevent all of them; the mistakes happen when intuition overrides the rules, usually in an attempt to avoid an extra table that the relational model genuinely requires.

Verifying your schema against the model

A step beginners skip but professionals never do: after mapping the model to DDL, verify the schema actually realizes the model. The translation is mechanical, but mechanical doesn't mean error-free — a forgotten foreign key, a wrong nullability, a missed junction can all slip through. A short verification pass catches them while they're still cheap to fix.

The verification is a walk back from the schema to the model, checking each correspondence. For every entity in the model, is there a table? For every 1:N relationship, is there a foreign key on the correct (many) side, with nullability matching the participation you decided (NOT NULL for total, nullable for partial)? For every M:N, is there a junction table with the right composite key? For every relationship attribute, is it on the junction where it belongs? For every multi-valued attribute, is there a child table? Are the referential actions (ON DELETE CASCADE/RESTRICT/SET NULL) the ones the domain calls for? This checklist mirrors the mapping rules, run in reverse as an audit, and it's quick — a few minutes for a modest schema — but it routinely catches the one foreign key you put on the wrong side or the participation you implemented as nullable when it should have been required.

The most valuable verification, though, is functional: can the schema store the data and answer the questions the model promised? Take the key use cases — "record a loan," "find a member's current loans," "list a book's authors" — and confirm the schema supports each with the data in the right places. Insert a few rows exercising each relationship, and try to violate each constraint (insert a loan with no member, a duplicate book-author pair, a negative fine) to confirm the database rejects what it should. This is the same "try to break it" discipline from Chapter 14, applied to the whole schema: a constraint you declared but that doesn't actually fire is no protection at all, and the only way to know it fires is to test it. A schema that's been verified structurally (against the rules) and functionally (against the use cases and constraints) is one you can build on with confidence — and confidence in the foundation is exactly what lets the rest of the project move fast. Skipping verification to save minutes now is how you discover, months later, that a relationship you thought was enforced never was.

Progressive project: turn your diagram into DDL

Take the ER diagram you drew in Chapter 17 and apply the rules:

1. Each entity → a CREATE TABLE with typed columns and a primary key (surrogate IDENTITY).
2. Each 1:N → a foreign key on the many side, with the right nullability (participation) and ON DELETE action.
3. Each M:N → a junction table with a composite PK, and any relationship attributes as columns on it.
4. Multi-valued attributes → child tables; composite attributes → component columns.
5. Self-references and weak entities as above.
6. Write it all as schema.sql, load it, and insert a few rows to confirm it works.

You now have the logical/physical schema for your project — ready to be checked for redundancy in the next chapter (Normalization).

The order of creation: dependencies matter

A practical wrinkle that bites people the first time they build a real schema: tables must be created in dependency order, because a foreign key can only reference a table that already exists. You cannot create orders with a foreign key to customers before customers exists — PostgreSQL will reject it. So the sequence in which you write your CREATE TABLE statements is not arbitrary; it must respect the dependency graph, creating referenced ("parent") tables before the tables that reference them ("children").

For Mercado, that means categories, suppliers, customers, warehouses, and employees (tables that nothing else depends on, or that are referenced) come first; then products (which references categories and suppliers); then orders (referencing customers and employees); then order_items, inventory, payments, shipments, reviews (the junction and detail tables that reference the others). The pattern is "roots first, leaves last" — create the tables at the top of the dependency tree before the ones that point to them. Most schema files are organized exactly this way, and reading one top to bottom traces the dependency structure of the whole database.

There are two escape hatches for genuinely circular dependencies (rare, but they happen — table A references B and B references A). One is to create the tables without the troublesome foreign keys, then add the foreign keys afterward with ALTER TABLE ... ADD CONSTRAINT. The other is DEFERRABLE constraints, which let you postpone foreign-key checking until the end of a transaction, so you can insert mutually-referencing rows. For dropping tables, the order reverses — children before parents — or you use DROP ... CASCADE (with the caution from Chapter 14). The dependency order also matters for loading data: you must insert parent rows before the child rows that reference them, which is why seed-sample.sql populates customers before orders before order_items. Understanding the dependency graph — what references what — is therefore essential not just for creating the schema but for loading it, backing it up, and dropping it. The graph is implicit in your foreign keys; making it explicit in your mind (or your schema file's ordering) prevents a whole class of "relation does not exist" and "violates foreign key constraint" errors.

Generating and reverse-engineering schemas

In practice, you won't always write mapping rules by hand — tools can help in both directions, and knowing they exist saves effort. Some diagramming tools (dbdiagram.io, for instance) generate CREATE TABLE DDL directly from an ER diagram, automating the very mapping rules this chapter teaches. You draw the entities and relationships, mark the cardinalities, and the tool emits the tables, keys, and foreign keys — applying Rules 1 through 7 mechanically. This is convenient, but it's exactly why understanding the rules matters even when a tool applies them: the tool only produces a correct schema if your model is correct and your cardinalities are marked right, and you can only verify its output if you understand what it should have produced. The tool automates the mechanical step; it cannot automate the judgment.

The reverse direction — reverse-engineering an ER diagram from an existing database — is equally valuable and arguably more common in working life, because you'll frequently inherit a database someone else built with no current diagram. Tools like DBeaver and pgAdmin can introspect a live database's catalog (the self-describing tables of Chapter 2) and draw its ER diagram automatically, turning a hundred opaque CREATE TABLE statements into a visual map you can actually comprehend. This is one of the fastest ways to understand an unfamiliar schema: point the tool at the database, generate the diagram, and the structure — which tables reference which, where the junctions are, how the hierarchies self-reference — becomes visible at a glance. The mapping rules run in reverse here too: a foreign key on the many side reveals a 1:N; a table that's all foreign keys reveals an M:N junction; a self-referencing foreign key reveals a hierarchy.

The lesson is that the mapping between models and schemas is bidirectional and partly automatable, but the automation is only as good as the human understanding around it. A tool can apply the rules forward (model → DDL) or backward (DDL → diagram), but it takes a person who understands the rules to design the model well in the first place, to verify the generated schema, and to interpret a reverse-engineered diagram correctly. The rules in this chapter are what make you that person — able to work fluidly with the tools rather than dependent on them, and able to catch the errors they can't. Whether you map by hand or by tool, the understanding is what makes the result trustworthy.

The rules are universal; the syntax is local

A reassuring property of everything in this chapter: the mapping rules are database-independent. The ER-to-relational mapping algorithm — entity to table, 1:N to foreign key on the many side, M:N to junction, multi-valued to child table, the three inheritance strategies — is part of relational theory, not PostgreSQL-specific lore. It works identically whether you're targeting PostgreSQL, MySQL, SQL Server, Oracle, or SQLite, because all of them are relational databases built on the same model with the same notion of tables, columns, primary keys, and foreign keys. Learn the mapping once and you can turn an ER model into a correct schema for any relational database.

What changes between databases is only the syntax of the resulting DDL — the type names (text versus varchar versus VARCHAR2), the identity-key spelling (GENERATED ALWAYS AS IDENTITY versus AUTO_INCREMENT versus IDENTITY(1,1)), and minor variations in constraint syntax. But the structure the rules produce — which tables exist, where the foreign keys go, what the junctions are — is the same everywhere, because it's dictated by the relationships in your model, not by the database's dialect. A 1:N relationship puts a foreign key on the many side in every relational database; only the exact CREATE TABLE text differs. This is why the mapping rules are taught as a database-agnostic algorithm in every database course: they're a property of the relational model itself.

The practical consequence is that this chapter's skill transfers completely across your career, even as the SQL syntax around it varies. You can sit down with an ER diagram and a database you've never used before, and produce a correct schema, because the mapping is something you carry, and only the spelling is something you look up. Combined with the conceptual modeling of Chapter 17 (also database-independent) and the normalization theory of Chapter 19 (likewise universal), you have a design methodology that works regardless of which relational database your job happens to use. The design thinking is the durable, transferable core; the DDL syntax is a thin, lookup-able layer on top. That's a comforting thing to know as you invest effort in these skills — you're learning to design databases, not just to write PostgreSQL. The mapping rules you've practiced here will serve you in any relational database you ever touch, for as long as the relational model endures — which, given its fifty-year track record (Chapter 1), is likely to be the whole of your career.

The bridge to normalization

You can now turn a model into tables — but having tables doesn't guarantee they're well-formed. A schema produced by faithfully applying the mapping rules to a good model is usually in good shape, but the mapping rules don't, by themselves, catch every kind of redundancy. A flawed model — or a schema someone built without modeling at all — can contain tables that store the same fact in multiple places, leading to the update, insertion, and deletion anomalies that corrupt data over time. The mapping rules tell you how to build tables from a model; they don't tell you whether the tables are free of redundancy. That's the job of the next chapter.

Normalization (Chapter 19) is the theory and procedure for evaluating whether your tables are well-formed and fixing them if they aren't. It introduces functional dependencies (the formalization of "this determines that," seeded back in Chapter 3) and the normal forms (1NF through BCNF) that progressively eliminate redundancy. The reassuring connection: a schema built from a good conceptual model via the mapping rules is usually already well-normalized, because good modeling naturally puts each fact in one place. Normalization then serves as both a verification (confirming your design is sound) and a repair procedure (fixing designs that aren't — including the flat, redundant tables that result when someone skips modeling entirely and just piles columns into a table).

So the design pipeline continues: Chapter 17 gave you the conceptual model, this chapter turned it into tables, and Chapter 19 will give you the rigorous criteria to judge whether those tables are well-formed and the procedure to fix them if not. Together these three chapters form the core of database design — model the domain, map it to tables, normalize for correctness — and they're the foundation on which Chapter 20's deliberate denormalization, Chapter 21's patterns, and Chapter 22's evolution all build. You're building the skill, layer by layer, to produce schemas you won't regret. The mapping you've learned here is the mechanical heart of it; normalization, next, is the theory that tells you whether the heart is sound. With modeling, mapping, and normalization together, you'll have the complete core methodology for producing schemas that are correct by construction rather than by luck.

Mechanical mapping, human judgment

A closing reflection that frames this chapter's place in the design pipeline. The mapping from ER model to tables is, as you've seen, genuinely mechanical — a finite set of rules applied in order, automatable by tools, producing a deterministic result. This is by design and it's a feature: the mechanical nature of the translation is exactly what lets you trust that a good model becomes a good schema without introducing new errors. The hard, irreducibly human work — the judgment about what entities exist, how they relate, what's an entity versus an attribute, where participation is total or partial — all happened in Chapter 17's conceptual modeling. By the time you reach this chapter, the thinking is done; you're a faithful translator.

This division of labor is worth appreciating because it tells you where to spend your care. If a schema turns out wrong, the fault almost always lies in the model, not the mapping — you mapped a flawed model faithfully and got a faithful realization of the flaw. So when reviewing a schema, the question is rarely "did I apply the rules correctly?" (the rules are mechanical and hard to get wrong once learned) but "was the model right?" — were the cardinalities correct, the participations considered, the entities properly distinguished from attributes? The mapping rules are a solved problem; the modeling judgment is the perennial challenge. This is why Chapter 17 is the longer, harder chapter and this one is shorter and more procedural: the difficulty is concentrated in the design, and the translation is comparatively easy once the design is sound.

For your own work, the lesson is to invest in the modeling and then trust the mapping. Don't rush the conceptual model to get to the "real work" of writing tables — the conceptual model is the real work, and the tables are its faithful shadow. A beautifully-applied set of mapping rules cannot rescue a model that mistook an M:N for a 1:N or buried a multi-valued fact in a column. Get the model right, apply the rules faithfully, verify the result against both the rules and the use cases, and you'll produce schemas that are correct because they were designed to be — which is the entire promise of doing database design as a discipline rather than letting a schema accrete one ad-hoc ALTER TABLE at a time.

Summary

Turning an ER model into a schema follows a small set of mapping rules: each entity → a table (key → PK); composite attributes → component columns; multi-valued attributes → a child table; 1:N → a foreign key on the "many" side (nullability from participation, with a chosen ON DELETE action); M:N → a junction table with a composite PK (relationship attributes live on the junction); 1:1 → a UNIQUE foreign key or a merge; weak entities → a composite key including the owner's; self-references → a foreign key to the same table. Inheritance maps three ways (single-table, table-per-subclass, table-per-concrete), each with trade-offs. Apply the rules in order and a correct schema — like Mercado's — emerges almost mechanically.

You can now: - Map any entity, attribute kind, and relationship to tables/columns/keys. - Implement 1:N with a foreign key on the correct side, and M:N with a junction table. - Place relationship attributes correctly (on the junction). - Map inheritance three ways and choose appropriately. - Turn a complete ER diagram into working DDL.

What's next. Chapter 19 — Normalization — now that you can build tables from a model, you'll learn the theory that tells you whether those tables are well-formed: functional dependencies, 1NF→BCNF, and the anomalies that redundancy causes. Home of the book's normalization-disaster anchor.

Practice in exercises.md, test yourself with the quiz, apply it in the case studies, review the key takeaways, and go deeper with further reading.