ER Diagrams
Entities, Attributes, Relationships, and Translating to Tables
LinkedIn Hook
"Your team spent six weeks building a feature, then realized the schema could not represent a customer with two shipping addresses. Nobody drew an ER diagram first."
Most teams skip the ER diagram step. They jump straight to CREATE TABLE statements, hardcode foreign keys based on whichever requirement is loudest in the room, and discover the missing relationship three sprints later when a user files a bug. ER diagrams are not academic ceremony — they are the cheapest place in the entire software lifecycle to catch a modelling mistake. Drawing a box and a line takes thirty seconds. Migrating a 200-million-row table because the relationship was wrong takes a weekend and an incident report.
The friction is partly notation. Chen notation, the original from 1976, uses diamonds for relationships and ovals for attributes — clear in a textbook, awful on a whiteboard with twenty entities. Crow's foot notation, the de facto industry standard, replaces the diamond with a line and encodes cardinality as little forks and circles at the line ends. UML class diagrams add a third dialect, common in Java and enterprise shops. They all describe the same thing: entities, attributes, relationships, and the multiplicity at each end.
The translation rules from ER diagram to relational tables are mechanical. One-to-many becomes a foreign key on the many side. Many-to-many becomes a junction table. One-to-one becomes a foreign key with a unique constraint. Multi-valued attributes become their own table. If you can read crow's foot, you can hand-translate any ER diagram to a clean PostgreSQL schema in minutes — and more importantly, you can argue about the model before any code is written.
In Lesson 8.4, I break down ER diagrams end to end: entity and attribute notation, the three popular dialects, how to read and draw crow's foot diagrams, and the mechanical rules for translating an ER diagram into CREATE TABLE statements.
Read the full lesson -> [link]
#SQL #Database #PostgreSQL #DataModeling #ERDiagram #BackendDevelopment #InterviewPrep
What You'll Learn
- What entities, attributes, and relationships mean in the ER (Entity-Relationship) model
- The three popular ER notation dialects: Chen, crow's foot, and UML class diagrams
- How to read crow's foot cardinality marks: the fork, the bar, and the circle
- How to draw a clean ER diagram from a written requirement
- The mechanical rules for translating an ER diagram into CREATE TABLE statements
- How weak entities, multi-valued attributes, and derived attributes show up on a diagram
- Why ER diagrams catch modelling bugs that pure SQL review never catches
The Architectural Blueprint Analogy — Boxes Before Bricks
Before a builder lays a single brick, an architect draws a blueprint. The blueprint shows every room as a rectangle, labels each one (kitchen, bedroom, bathroom), draws doors as gaps in the walls, and marks load-bearing connections. None of it is real wood or concrete — it is just lines on paper. But the blueprint is the cheapest place in the whole project to discover that the kitchen cannot fit a refrigerator, that two bedrooms share a wall with a bathroom, or that the staircase lands in the middle of the living room. Fixing a line on a blueprint costs a pencil and an eraser. Fixing the same problem after the foundation is poured costs a backhoe and a month.
An ER diagram is exactly the same thing for a database. Each box is a table you have not built yet. Each line is a foreign key you have not written yet. The little forks and circles at the line ends are the cardinality rules that will become NOT NULL, UNIQUE, and ON DELETE constraints in the CREATE TABLE. The diagram is a thinking tool — you sketch it, argue about it, redraw it, and only when the boxes and lines tell a story that matches the business do you translate it into SQL.
+---------------------------------------------------------------+
| ER DIAGRAM == DATABASE BLUEPRINT |
+---------------------------------------------------------------+
| |
| BLUEPRINT ER DIAGRAM |
| -------------------- ----------------------- |
| Room rectangle -> Entity box |
| Room label -> Entity name |
| Furniture in room -> Attribute list |
| Door between rooms -> Relationship line |
| Door width / direction -> Cardinality (1..N, M..N) |
| Load-bearing wall -> NOT NULL / required FK |
| |
| FIXING A MISTAKE: |
| On paper: pencil + eraser, 30 seconds |
| In SQL: ALTER TABLE on 200M rows, weekend |
| |
+---------------------------------------------------------------+The Three Building Blocks: Entity, Attribute, Relationship
Peter Chen's 1976 paper introduced the Entity-Relationship model and gave us three building blocks. Every ER diagram you will ever see — whether drawn in Chen, crow's foot, UML, or a napkin sketch — is made of these three things.
Entity. A real-world thing the business cares about and wants to remember. A customer, an order, a product, a shipment, a payment. Entities become tables in the relational world. Each instance of an entity (a specific customer, order #1234, the blue mug SKU) becomes a row in that table.
Attribute. A piece of information about an entity. A customer has a name, an email, a date of birth. An order has a total, a status, a created_at timestamp. Attributes become columns. Some attributes are simple (a single value), some are composite (street + city + zip together form an address), some are multi-valued (a customer can have many phone numbers), and some are derived (age can be computed from date_of_birth, so it is not stored).
Relationship. An association between two or more entities. A customer places an order. An order contains products. A product belongs to a category. Relationships become foreign keys (for one-to-many and one-to-one) or junction tables (for many-to-many). Each relationship has a cardinality — how many instances of one entity relate to how many of the other.
+---------------------------------------------------------------+
| THE THREE BUILDING BLOCKS |
+---------------------------------------------------------------+
| |
| ENTITY -> becomes a TABLE |
| ATTRIBUTE -> becomes a COLUMN |
| RELATIONSHIP -> becomes a FOREIGN KEY or JUNCTION TABLE |
| |
| Identifier (the PK): |
| Underlined attribute on a Chen diagram |
| PK marker on a crow's foot diagram |
| {id} stereotype on a UML class diagram |
| |
+---------------------------------------------------------------+Three Notation Dialects: Chen, Crow's Foot, UML
The three dialects describe the same model with different shapes. Knowing all three matters because you will see all three in the wild — Chen in textbooks, crow's foot in real projects, UML in Java and enterprise environments.
Chen Notation (1976, original)
Chen uses rectangles for entities, ovals for attributes (drawn floating around the entity, connected by lines), diamonds for relationships, and underlines for primary key attributes. Cardinality is written as 1, N, or M near the line ends.
+---------------------------------------------------------------+
| CHEN NOTATION SAMPLE |
+---------------------------------------------------------------+
| |
| (name) (email) (total) (status) |
| \ / \ / |
| \ / \ / |
| +----------+ 1 <places> N +---------+ |
| | CUSTOMER |--------/ \------| ORDER | |
| +----------+ \ / +---------+ |
| | \-----/ | |
| (id*) (id*) |
| |
| * = underlined in real diagrams = primary key |
| |
+---------------------------------------------------------------+Chen is precise and theoretically clean, but it does not scale visually. A schema with twenty entities and forty attributes each becomes a forest of ovals nobody can read. Use Chen for teaching and for very small diagrams. Avoid it for real projects.
Crow's Foot Notation (industry standard)
Crow's foot puts attributes inside the entity box (like a table layout) and uses line endings to encode cardinality. The endings are tiny pictograms:
- A single bar
|means "exactly one" - A circle
omeans "zero" (optional) - A fork (the crow's foot, looks like
<or>) means "many"
You combine them: o| is "zero or one", || is "exactly one", o< is "zero or many", |< is "one or many". The mark closest to the entity describes that side of the relationship.
+----------------------------------------------------------------+
| CROW'S FOOT NOTATION SAMPLE |
+----------------------------------------------------------------+
| |
| +-----------------+ +--------------------+ |
| | CUSTOMER | | ORDER | |
| +-----------------+ +--------------------+ |
| | PK id |||----o< | PK id | |
| | name | | FK customer_id | |
| | email | | total | |
| | created_at | | status | |
| +-----------------+ | created_at | |
| +--------------------+ |
| |
| Read the line endings: |
| Customer side: || -> exactly one customer |
| Order side: o< -> zero or many orders |
| |
| "A customer has zero or many orders. |
| An order belongs to exactly one customer." |
| |
+----------------------------------------------------------------+Crow's foot is the dominant industry notation. Tools like dbdiagram.io, DrawSQL, Lucidchart, ERDPlus, and most reverse-engineering tools default to crow's foot. Learn to read it fluently — it is the vocabulary of every database design conversation in industry.
UML Class Diagrams
UML uses the same boxes-and-lines style as crow's foot but borrows from object-oriented modelling. Cardinality is written as a numeric range like 1, 0..1, 1..*, 0..*, or 2..5. The asterisk * means "many". UML also distinguishes association (a plain line), aggregation (a hollow diamond, "has-a"), and composition (a filled diamond, "owns and lifecycle-controls").
+----------------------------------------------------------------+
| UML CLASS DIAGRAM SAMPLE |
+----------------------------------------------------------------+
| |
| +-----------------+ +--------------------+ |
| | Customer | | Order | |
| +-----------------+ +--------------------+ |
| | -id: int {PK} | 1 0..* | -id: int {PK} | |
| | -name: string |---------------| -total: decimal | |
| | -email: string | places | -status: string | |
| +-----------------+ +--------------------+ |
| |
| Multiplicity reads: |
| 1 on Customer side |
| 0..* on Order side |
| Same as crow's foot ||----o< |
| |
+----------------------------------------------------------------+UML is common in Java/enterprise environments and in tools like StarUML, PlantUML, and Visual Paradigm. The semantics map cleanly onto crow's foot — you can translate one to the other without losing information.
Reading a Crow's Foot Diagram
Cardinality has two parts: the maximum (one or many) and the minimum (zero or one). The mark closest to the entity tells you the maximum; the mark just before it tells you the minimum.
+----------------------------------------------------------------+
| CROW'S FOOT CARDINALITY KEY |
+----------------------------------------------------------------+
| |
| ENDING MIN MAX READING |
| ------ --- --- ----------------- |
| || 1 1 exactly one (mandatory) |
| o| 0 1 zero or one (optional) |
| |< 1 N one or many (mandatory many) |
| o< 0 N zero or many (optional many) |
| |
| The mark NEAREST the entity = max. |
| The mark JUST BEFORE it = min. |
| |
+----------------------------------------------------------------+A line A ||----o< B reads in two directions:
- Left to right: "A is associated with zero or many B."
- Right to left: "B is associated with exactly one A."
This is a classic one-to-many: every B has exactly one A, every A has zero or many B. In the relational world, this becomes a NOT NULL foreign key on B referencing A.
A Worked Example: Online Store ER Diagram
Let us walk through a small online store domain end to end. Requirements:
- A customer has a name and an email and many shipping addresses.
- A customer places zero or many orders.
- An order belongs to exactly one customer and contains one or many line items.
- A line item references exactly one product and stores its quantity and unit price.
- A product belongs to exactly one category, and a category contains zero or many products.
- A product can have many tags, and a tag can apply to many products (many-to-many).
Here is the crow's foot diagram:
+-------------------------------------------------------------------+
| ONLINE STORE ER DIAGRAM (CROW'S FOOT) |
+-------------------------------------------------------------------+
| |
| +---------------+ +-----------------+ |
| | CATEGORY | | ADDRESS | |
| +---------------+ +-----------------+ |
| | PK id | | PK id | |
| | name | | FK customer_id | |
| +---------------+ | street | |
| | | city | |
| | || | postal_code | |
| | +-----------------+ |
| | contains ^ |
| | | |< |
| | o< | |
| v | |
| +---------------+ +-----------------+ |
| | PRODUCT | | CUSTOMER | |
| +---------------+ +-----------------+ |
| | PK id | | PK id | |
| | FK category_id| | name | |
| | name | | email | |
| | price | | created_at | |
| +---------------+ +-----------------+ |
| ^ | |
| | || | || |
| | | |
| | o< | o< |
| v v |
| +---------------+ +-----------------+ |
| | LINE_ITEM | >|--------|| | ORDER | |
| +---------------+ +-----------------+ |
| | PK id | | PK id | |
| | FK order_id | | FK customer_id | |
| | FK product_id | | total | |
| | quantity | | status | |
| | unit_price | | created_at | |
| +---------------+ +-----------------+ |
| |
| (PRODUCT to TAG is many-to-many, see junction below) |
| |
| +---------------+ o< >o +-----------------+ |
| | PRODUCT |---------------| TAG | |
| +---------------+ +-----------------+ |
| (resolved with PRODUCT_TAG junction table) |
| |
+-------------------------------------------------------------------+Read the lines aloud and they should sound exactly like the requirements. "A customer has zero or many addresses, each address belongs to exactly one customer. A customer places zero or many orders, each order belongs to exactly one customer. An order has one or many line items, each line item belongs to exactly one order. A line item references exactly one product, a product can appear in zero or many line items. A category contains zero or many products, a product belongs to exactly one category. A product has zero or many tags, a tag applies to zero or many products."
Napkin AI Visual Prompt: "Dark navy gradient (#0a0f1f -> #111a2e). Crow's foot ER diagram with seven entity boxes: CUSTOMER (center top), ADDRESS (top right), ORDER (right), LINE_ITEM (bottom right), PRODUCT (bottom center), CATEGORY (bottom left), TAG (far right). Sky blue (#4fc3f7) connector lines with crow's foot endings showing all relationships. Each box has a header bar in rose (#ff5c8a) with the entity name in white monospace and attribute list below. Small rose labels above each line: 'has', 'places', 'contains', 'references', 'belongs to', 'tagged with'. PK and FK badges on attributes. White monospace text throughout."
Translating an ER Diagram to CREATE TABLE
The translation rules are mechanical. Memorize them once and you can convert any ER diagram into a relational schema without thinking.
+----------------------------------------------------------------+
| TRANSLATION RULES |
+----------------------------------------------------------------+
| |
| 1. Each entity -> one CREATE TABLE |
| 2. Each attribute -> one column with a type |
| 3. Identifier -> PRIMARY KEY |
| 4. One-to-many (1..N) -> FK on the MANY side |
| Mandatory side -> NOT NULL on the FK |
| Optional side -> nullable FK |
| 5. One-to-one (1..1) -> FK on either side + UNIQUE |
| 6. Many-to-many -> JUNCTION TABLE with two FKs |
| Junction PK -> composite (fk1, fk2) |
| 7. Multi-valued attr -> separate child table with FK |
| 8. Composite attr -> multiple columns or single JSONB |
| 9. Derived attr -> NOT stored, computed in queries |
| 10. Weak entity -> composite PK including parent FK |
| |
+----------------------------------------------------------------+Example 1 — One-to-Many: Customer and Order
-- Translate the CUSTOMER ||----o< ORDER relationship.
-- Rule 4: FK on the many side (orders), NOT NULL because each
-- order MUST have a customer (the customer side is "exactly one").
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
total NUMERIC(12,2) NOT NULL,
status TEXT NOT NULL DEFAULT 'pending',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Index the FK so customer-order joins are fast.
CREATE INDEX idx_orders_customer ON orders(customer_id);
INSERT INTO customers (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');
INSERT INTO orders (customer_id, total, status) VALUES
(1, 120.00, 'shipped'),
(1, 55.50, 'pending'),
(2, 999.00, 'shipped');
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY revenue DESC NULLS LAST;
-- name | order_count | revenue
-- ------+-------------+---------
-- Bob | 1 | 999.00
-- Alice | 2 | 175.50Example 2 — Multi-Valued Attribute: Customer and Address
A customer "has many addresses" is a multi-valued attribute. Rule 7: it becomes its own table with a foreign key back to the parent.
-- The ADDRESS box on the diagram is really a multi-valued attribute
-- of CUSTOMER. We model it as a child table with a FK and an index.
CREATE TABLE addresses (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
street TEXT NOT NULL,
city TEXT NOT NULL,
postal_code TEXT NOT NULL,
is_default BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE INDEX idx_addresses_customer ON addresses(customer_id);
-- Enforce "at most one default address per customer" with a partial unique index.
CREATE UNIQUE INDEX idx_addresses_one_default
ON addresses(customer_id) WHERE is_default;
INSERT INTO addresses (customer_id, street, city, postal_code, is_default) VALUES
(1, '1 Apple St', 'Cupertino', '95014', TRUE),
(1, '2 Banana Ave', 'San Jose', '95110', FALSE),
(2, '99 Cherry Ln', 'Seattle', '98101', TRUE);
SELECT c.name, a.street, a.city, a.is_default
FROM customers c
JOIN addresses a ON a.customer_id = c.id
ORDER BY c.name, a.is_default DESC, a.id;
-- name | street | city | is_default
-- ------+--------------+-----------+-----------
-- Alice | 1 Apple St | Cupertino | t
-- Alice | 2 Banana Ave | San Jose | f
-- Bob | 99 Cherry Ln | Seattle | tExample 3 — Many-to-Many: Product and Tag
A product can have many tags and a tag can apply to many products. Rule 6: introduce a junction table whose primary key is the composite of the two foreign keys.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC(12,2) NOT NULL CHECK (price >= 0)
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
-- The junction table. Composite PK guarantees no duplicate (product, tag) pairs.
-- Both FKs are NOT NULL by virtue of being part of the PK.
CREATE TABLE product_tags (
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (product_id, tag_id)
);
-- Index the reverse direction for "find all products with this tag" queries.
CREATE INDEX idx_product_tags_tag ON product_tags(tag_id);
INSERT INTO products (name, price) VALUES
('Blue Mug', 12.00),
('Red Shirt', 29.99),
('Green Hat', 18.50);
INSERT INTO tags (name) VALUES
('sale'), ('summer'), ('clearance'), ('new');
INSERT INTO product_tags (product_id, tag_id) VALUES
(1, 1), (1, 4), -- Blue Mug: sale, new
(2, 1), (2, 2), (2, 3), -- Red Shirt: sale, summer, clearance
(3, 2); -- Green Hat: summer
SELECT p.name, STRING_AGG(t.name, ', ' ORDER BY t.name) AS tags
FROM products p
LEFT JOIN product_tags pt ON pt.product_id = p.id
LEFT JOIN tags t ON t.id = pt.tag_id
GROUP BY p.id, p.name
ORDER BY p.name;
-- name | tags
-- ----------+----------------------------
-- Blue Mug | new, sale
-- Green Hat | summer
-- Red Shirt | clearance, sale, summerExample 4 — One-to-One with Optional Side: Customer and Profile
A customer "may have" exactly one profile (an optional one-to-one). Rule 5: foreign key with a UNIQUE constraint, nullable on the optional side.
CREATE TABLE customer_profiles (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL UNIQUE REFERENCES customers(id) ON DELETE CASCADE,
bio TEXT,
avatar_url TEXT,
date_of_birth DATE
);
INSERT INTO customer_profiles (customer_id, bio, date_of_birth) VALUES
(1, 'Coffee enthusiast', '1990-04-12'),
(2, 'Mountain climber', '1985-11-03');
-- Derived attribute (rule 9): age is computed on the fly, not stored.
SELECT c.name,
p.bio,
EXTRACT(YEAR FROM age(p.date_of_birth))::INT AS age_years
FROM customers c
LEFT JOIN customer_profiles p ON p.customer_id = c.id
ORDER BY c.name;
-- name | bio | age_years
-- ------+-------------------+----------
-- Alice | Coffee enthusiast | 35
-- Bob | Mountain climber | 40The UNIQUE on customer_id is what enforces the "exactly one" side. Without it, you would have a one-to-many in disguise.
Napkin AI Visual Prompt: "Dark navy gradient (#0a0f1f -> #111a2e). Side-by-side comparison: LEFT half labeled 'ER Diagram' shows three entities (CUSTOMER, ORDER, ADDRESS) connected by sky blue (#4fc3f7) crow's foot lines. RIGHT half labeled 'PostgreSQL Schema' shows the equivalent CREATE TABLE statements in white monospace with PRIMARY KEY and FOREIGN KEY clauses highlighted in rose (#ff5c8a). A bold rose arrow labeled 'translate' connects the two halves. Subtitle in white monospace: 'Boxes become tables. Lines become foreign keys.'"
Weak Entities, Composite Keys, and Edge Cases
A weak entity is one that cannot exist independently of another and does not have its own natural primary key. The classic example is order_line — a line item only makes sense as part of an order, and its identity is "line N of order M". On a Chen diagram, weak entities are drawn with a double-bordered rectangle. On a crow's foot diagram, they are normal boxes whose primary key includes the parent's foreign key.
-- LINE_ITEM is a weak entity: identified by (order_id, line_no).
CREATE TABLE order_lines (
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
line_no INTEGER NOT NULL,
product_id INTEGER NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(12,2) NOT NULL,
PRIMARY KEY (order_id, line_no)
);Composite attributes (an address that has street, city, postal_code) become multiple columns. Derived attributes (age, total_spent) are not stored — they are computed in views or on demand. Multi-valued attributes become separate child tables, as shown in the addresses example.
Common Mistakes
1. Skipping the diagram and going straight to SQL. The diagram exists precisely to find modelling bugs before they are expensive. Teams that skip it routinely discover six weeks in that they cannot represent a customer with two addresses, an order with multiple shipments, or a product with variants — and the fix is a painful migration on a populated table. Spend twenty minutes drawing boxes and lines first. Always.
2. Confusing many-to-many with one-to-many.
"A user has many roles, a role has many users" is many-to-many and needs a junction table. Putting role_id directly on the users table forces every user to have exactly one role and silently breaks the requirement. The crow's foot test: if both ends of the line have a fork, you need a junction table.
3. Forgetting the minimum side of cardinality.
"One or many" and "zero or many" are different requirements with different SQL. The first translates to a NOT NULL foreign key (every row must point at a parent), the second to a nullable foreign key. Drawing only the maximum (always < for many) hides the choice and produces schemas where the NOT NULL decisions look arbitrary.
4. Modelling derived data as stored attributes.
Storing age instead of date_of_birth makes the column wrong every January. Storing order.total instead of computing it from line items creates a denormalization trap that can drift out of sync. The diagram should mark derived attributes explicitly (a dashed oval in Chen, a / prefix in UML) so the reviewer can challenge whether storing them is worth the maintenance cost.
5. Mixing notations within one diagram. Pick one dialect — Chen, crow's foot, or UML — and stick with it for an entire diagram. Mixing diamonds and forks confuses every reader and produces ambiguous cardinality. If your team standardizes on crow's foot (the right answer for almost every project), enforce it in tooling.
Interview Questions
1. "What are the building blocks of an ER diagram, and how do they map to a relational schema?"
The three building blocks are entities, attributes, and relationships. An entity is a real-world thing the business cares about — a customer, an order, a product — and becomes a table in the relational world. An attribute is a piece of information about an entity, like a name or a created_at timestamp, and becomes a column. A relationship is an association between entities, like "customer places order", and becomes either a foreign key (for one-to-many and one-to-one) or a junction table (for many-to-many). The identifier attribute on the ER diagram (underlined in Chen, marked PK in crow's foot) becomes the PRIMARY KEY of the table. Multi-valued attributes become separate child tables, composite attributes become multiple columns, and derived attributes are computed on the fly rather than stored.
2. "Compare Chen notation, crow's foot, and UML class diagrams. Which would you use for a real project?"
Chen notation is the original 1976 dialect. It uses rectangles for entities, ovals for attributes (drawn as a halo around the entity), and diamonds for relationships, with cardinality written as 1, N, or M near the line ends. It is precise but does not scale visually — a schema with twenty entities becomes a forest of ovals nobody can read. Crow's foot puts attributes inside the entity box and encodes cardinality as little forks, bars, and circles at the line ends, making the diagram compact and readable. UML class diagrams use the same boxed style as crow's foot but write cardinality as numeric ranges like 1..* or 0..1 and distinguish association, aggregation, and composition. For a real project, I use crow's foot — it is the de facto industry standard, supported by every major modelling tool, and the line endings make cardinality unambiguous at a glance.
3. "Walk me through the rules for translating an ER diagram into CREATE TABLE statements."
Each entity becomes one table, each attribute becomes a column, and the identifier becomes the primary key. For a one-to-many relationship, put the foreign key on the many side, NOT NULL if the relationship is mandatory and nullable if optional. For a one-to-one relationship, put the foreign key on either side and add a UNIQUE constraint to prevent it from becoming one-to-many. For a many-to-many relationship, create a junction table with two foreign keys, and make the composite of those two foreign keys the primary key of the junction. Multi-valued attributes (like a customer's many phone numbers) become a separate child table with a foreign key back to the parent. Composite attributes become multiple columns. Derived attributes are not stored at all; they are computed in queries or views. Weak entities get a composite primary key that includes the parent's foreign key. These rules are mechanical, which is exactly the point — the diagram does the thinking, the SQL is just transcription.
4. "On a crow's foot diagram, what does the line ending o< mean, and how is it different from |<?"
The two characters at the line end encode cardinality. The mark closest to the entity is the maximum, and the mark just before it is the minimum. A fork (the crow's foot) means many, a bar means exactly one, and a circle means zero. So o< means "zero or many" — the entity at the other end of the line may be associated with zero, one, or many of these. |< means "one or many" — at least one is required. The difference matters in SQL: o< translates to a foreign key that does not require a corresponding parent row, while |< translates to a NOT NULL foreign key (and, in some shops, a CHECK constraint or trigger to enforce that the parent has at least one child). Mixing them up is one of the most common modelling bugs and produces schemas where the NOT NULL choices look random.
5. "How would you draw and translate a many-to-many relationship between students and courses, where each enrollment also stores a grade?"
On the diagram, students and courses are two entity boxes connected by a line with a fork on each end (>o----o< for "students take zero or many courses, courses have zero or many students"). The grade is an attribute of the relationship itself, not of either entity, which is the giveaway that the junction is more than a pure linking table. In the relational schema, I create three tables: students, courses, and an enrollments junction table. The junction has student_id and course_id as foreign keys, a composite primary key on (student_id, course_id) to prevent duplicate enrollments, and a grade column to store the relationship attribute. I also add an index on course_id so "find all students in this course" queries do not require a sequential scan, and I usually add an enrolled_at timestamp because relationship attributes love to grow over time.
Quick Reference — ER Diagram Cheat Sheet
+----------------------------------------------------------------+
| ER DIAGRAM CHEAT SHEET |
+----------------------------------------------------------------+
| |
| BUILDING BLOCKS: |
| Entity -> rectangle -> table |
| Attribute -> oval (Chen) or row inside box (crow's foot) |
| -> column |
| Relationship -> diamond (Chen) or line (crow's foot) |
| -> FK or junction table |
| Identifier -> underlined attr or PK marker -> PRIMARY KEY |
| |
| CROW'S FOOT NOTATION KEY: |
| || exactly one (min=1, max=1) |
| o| zero or one (min=0, max=1) |
| |< one or many (min=1, max=N) |
| o< zero or many (min=0, max=N) |
| Mark NEAREST entity = max |
| Mark JUST BEFORE it = min |
| |
| TRANSLATION RULES: |
| 1..1 (mandatory) -> FK + UNIQUE + NOT NULL |
| 0..1 -> FK + UNIQUE, nullable |
| 1..N (mandatory) -> FK NOT NULL on many side |
| 0..N -> FK nullable on many side |
| M..N -> junction table, composite PK |
| Multi-valued -> child table with FK |
| Composite attr -> multiple columns |
| Derived attr -> NOT stored, compute on query |
| Weak entity -> composite PK including parent FK |
| |
| DIALECTS: |
| Chen -> textbooks, teaching, small diagrams |
| Crow's foot -> industry standard, dbdiagram, DrawSQL |
| UML -> Java/enterprise, PlantUML, StarUML |
| |
+----------------------------------------------------------------+
+----------------------------------------------------------------+
| KEY RULES |
+----------------------------------------------------------------+
| |
| 1. Draw the diagram BEFORE the CREATE TABLE |
| 2. Each entity = one table, each attribute = one column |
| 3. The identifier becomes the PRIMARY KEY |
| 4. One-to-many -> FK on the many side |
| 5. Many-to-many -> junction table, never a single FK column |
| 6. NOT NULL on FK = "mandatory" side of the relationship |
| 7. UNIQUE on FK = "exactly one" upgrade from many to one |
| 8. Multi-valued attribute = child table, never an array column |
| 9. Derived attribute = compute in queries, do not store |
| 10. Pick one notation per diagram and never mix dialects |
| |
+----------------------------------------------------------------+| Concern | Wrong Way | Right Way |
|---|---|---|
| Modelling first | Jump to CREATE TABLE | Draw the ER diagram first |
| Many-to-many | Single FK column | Junction table with composite PK |
| Optional vs mandatory | Always nullable FK | NOT NULL on mandatory side |
| Multi-valued attribute | Comma-separated string | Child table with FK |
| Derived attribute | Stored column | Computed in query or view |
| One-to-one | Plain FK | FK + UNIQUE constraint |
| Notation | Mixing Chen and crow's foot | One dialect per diagram |
| Weak entity | Surrogate PK only | Composite PK with parent FK |
Prev: Lesson 8.3 -- Many-to-Many Relationships Next: Lesson 8.5 -- Cardinality and Ordinality
This is Lesson 8.4 of the Database Interview Prep Course -- 12 chapters, 58 lessons.