Entity-Relationship Diagrams
An entity-relationship diagram, almost always shortened to ER diagram, is a visual model of the entities in a system, the attributes that describe each one, and the relationships between them. It is a design tool used before a single CREATE TABLE statement is written — a way to think through what data needs to exist and how it connects, on paper or in a diagramming tool, while changes are still cheap.
The three building blocks
ER diagram concept | What it represents | Becomes in SQL |
|---|---|---|
Entity | A distinct "thing" the system needs to track — a customer, a product, an order. | A table. |
Attribute | A property that describes an entity — a customer's name, a product's price. | A column. |
Relationship | How two entities are connected — a customer places an order. | A foreign key (one-to-many, one-to-one) or a junction table (many-to-many). |
Every ER diagram, no matter how complex, is built from just these three ingredients. A customer entity has attributes like customer_id, name, and email. An order entity has attributes like order_id, order_date, and amount. The relationship "a customer places orders" is a one-to-many relationship, drawn as a line connecting the two entities — and it is exactly the relationship types covered on the previous page (one-to-one, one-to-many, many-to-many) that determine how that line gets translated into actual foreign keys or a junction table.
Crow's foot notation
Most modern ER diagrams use crow's foot notation to show, at each end of a relationship line, how many rows on that side can participate. The symbols look like a bird's footprint — hence the name — and combine a "one or many" indicator with an "optional or mandatory" indicator.
Symbol (drawn at the end of a line) | Meaning |
|---|---|
Single tick mark | Exactly one — this side must have exactly one related row. |
Crow's foot (three-pronged fork) | Many — this side can have zero, one, or many related rows. |
Circle | Zero — this side is optional, the relationship may not exist. |
Circle plus tick | Zero or one — optional, and at most one related row if it does exist. |
Tick plus crow's foot | One or many — mandatory, and there must be at least one related row. |
Reading a diagram is a matter of reading each end of the line separately. For a customers-to-orders relationship, the "customers" end typically gets a single tick (one customer), and the "orders" end gets a circle plus a crow's foot (zero or many orders) — read together as "a customer places zero or many orders, and each order belongs to exactly one customer."
From diagram to schema
Once entities, attributes, and relationships are settled on the diagram, translating it into SQL is mostly mechanical:
Each entity becomes a CREATE TABLE statement.
Each attribute becomes a column, with a data type chosen to match the kind of value it holds.
Each one-to-one or one-to-many relationship becomes a foreign key on the "many" (or optional) side.
Each many-to-many relationship becomes a junction table with a foreign key to each of the two related entities.
A small diagram translated into SQL
-- Entities: customers, orders — a one-to-many relationship CREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(150) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE, amount DECIMAL(10, 2), customer_id INT NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );
Why design on paper first
It is tempting to skip the diagram and go straight to CREATE TABLE, especially for a small schema. The trouble is that mistakes made in a diagram cost a few minutes to fix — erase a line, redraw it, move on. The same mistake discovered after tables are built and populated with real data can mean a schema migration, a data backfill, and coordinating changes across every piece of application code that touches the affected tables. Spotting that a "one-to-many" is actually a "many-to-many" is a five-second fix on paper and can be a multi-day fix in production.