What is an ER diagram, and how do you go from a conceptual model to a physical schema?
5 minintermediateer-diagramdata-modelingschema-design
Quick Answer
An Entity-Relationship (ER) diagram models the *business domain* — entities (things), their attributes, and the relationships between them (one-to-one, one-to-many, many-to-many) — independent of any specific database engine. Going to a physical schema means translating entities into tables, attributes into typed columns, and relationships into foreign keys or junction tables, then applying normalization, indexing, and engine-specific type choices.
Detailed Answer
The three modeling levels
- Conceptual model — high-level entities and relationships, no attributes or types yet: "a
CustomerplacesOrders," "anOrdercontainsProducts." Aimed at communicating with non-technical stakeholders. - Logical model (ER diagram) — adds attributes, keys, and cardinality (1:1, 1:N, N:M) to each entity/relationship, still independent of any specific database engine's syntax.
- Physical model — the actual
CREATE TABLEstatements for a specific engine, with concrete data types, indexes, constraints, and partitioning decisions.
Translating ER concepts into tables
| ER concept | Physical schema equivalent |
|---|---|
Entity (e.g., Customer) | A table |
Attribute (e.g., email) | A column, with a chosen data type |
| One-to-many relationship | A foreign key on the "many" side pointing to the "one" side's primary key |
| Many-to-many relationship | A junction table with foreign keys to both sides |
| One-to-one relationship | A foreign key on either side with a UNIQUE constraint (or merge into one table if the split has no real justification) |
| Weak entity (can't exist without its parent) | A table whose primary key includes (or is entirely derived from) the parent's key, often with ON DELETE CASCADE |
Example walkthrough
Conceptual: "A Customer places many Orders; each Order contains many Products (and each Product can be in many Orders)."
CREATE TABLE customers (id SERIAL PRIMARY KEY, name VARCHAR(100));
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(id), -- one-to-many
ordered_at TIMESTAMP NOT NULL DEFAULT now()
);
CREATE TABLE products (id SERIAL PRIMARY KEY, name VARCHAR(200), price NUMERIC(10,2));
CREATE TABLE order_items ( -- junction table for the many-to-many
order_id INT NOT NULL REFERENCES orders(id),
product_id INT NOT NULL REFERENCES products(id),
quantity INT NOT NULL DEFAULT 1,
unit_price NUMERIC(10,2) NOT NULL, -- price at time of order (denormalized, deliberately)
PRIMARY KEY (order_id, product_id)
);
Practical process
- Identify entities and their natural-language relationships with stakeholders — resist jumping straight to tables.
- Assign cardinality to every relationship (1:1, 1:N, N:M) — this alone determines whether you need a foreign key or a junction table.
- Normalize to at least 3NF as a starting point.
- Choose primary keys (surrogate vs natural — see that question) and concrete data types.
- Add constraints (
NOT NULL,CHECK,UNIQUE) to encode business rules directly in the schema. - Only then consider indexes and any deliberate denormalization, based on known query patterns — not before the logical model is solid.