Understanding ER → SQL
From boxes-and-lines to CREATE TABLE.
The mechanical rules for turning entities into tables, the three-way decision on many-to-many, and the naming conventions that survive a refactor.
One entity, one table.
The basic rule. Each entity in the ER diagram becomes a table; each attribute becomes a column; the primary key is the identifying attribute or a surrogate id. A "User" entity with id, email, name becomes CREATE TABLE users (id BIGSERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL, name TEXT). Plural table name is convention; singular column names are convention; the choice is consistent within a database.
One-to-many — foreign key in the child.
"A user has many posts" becomes a user_id column in the posts table, referencing the users table's primary key. The column is the foreign key. Modelling the other direction (a posts column on the user table containing a list) almost always works worse: variable-length, harder to query, breaks the relational model. The FK lives on the "many" side; the join is from posts up to users.
Many-to-many — junction table.
"Users and roles, each user has many roles, each role has many users." Neither side can hold a list of FKs cleanly. The answer: a third table — user_roleswith user_id and role_id columns, composite primary key on both. Junction tables can have their own columns too (assigned_at, assigned_by); when they do, the relationship has been promoted to a first-class entity in its own right.
A worked schema.
Three entities: Users, Roles, Posts. Users-Roles many-to-many; Users-Posts one-to-many. SQL output: 4 tables — users, roles, user_roles (junction), posts (with user_id FK). Constraints: FK on each side of user_roles, FK from posts.user_id to users.id with ON DELETE CASCADE (delete user, posts disappear) orON DELETE RESTRICT (delete user fails if posts exist), whichever matches the business rule.
3 entities → 4 tables
users, roles, posts + junction
One-to-many puts FK on child ; many-to-many gets a junction.
users, roles, user_roles, posts(user_id FK)
= Normalised, indexed, joinable
Inheritance — three patterns.
When ER diagrams show "Employee is-a Person, Customer is-a Person", SQL has three mappings. Single-table: one table with all columns, a "type" discriminator, nullable fields. Class-table: separate Person, Employee, Customer tables with FKs to Person. Concrete-table: only Employee and Customer tables, no shared parent. Single-table is fastest to query, leaks columns; class-table is normalised, costs joins; concrete-table is simplest, duplicates the shared columns. Pick by query pattern.
Indexes follow queries.
The schema isn't done when the tables exist; the indexes decide whether it runs. Every FK column gets an index. Columns used in WHERE clauses get indexes. Composite indexes for multi-column queries. Don't over-index — every index slows writes and consumes disk. Run the queries against EXPLAIN before declaring the schema done; the query plan tells you which index helped.