Quick answer
Draw an entity-relationship diagram (ERD): one box per table listing its columns, with the primary key marked, and lines between tables for each foreign key. Mark each line's cardinality — one-to-one, one-to-many or many-to-many — usually with crow's-foot notation, and resolve many-to-many relationships with a junction table.
Once a database has more than a handful of tables, it's hard to keep the structure in your head. Which table owns which data? What happens to orders when a customer is deleted? An ERD answers those questions at a glance, which makes it invaluable when designing a schema, reviewing changes or onboarding someone new.
In this article
The building blocks
| Term | Meaning | Example |
|---|---|---|
| Entity | A thing you store data about — usually a table | customers, orders, products |
| Attribute | A property of the entity — a column | email, created_at, price |
| Primary key (PK) | The column(s) that uniquely identify a row | customers.id |
| Foreign key (FK) | A column that refers to another table's primary key | orders.customer_id → customers.id |
| Relationship | How rows in two tables connect | A customer places orders |
| Cardinality | How many rows can be on each side | One customer, many orders |
The three kinds of relationship
One-to-many (1:N) — the most common
One customer can place many orders; each order belongs to one customer. The foreign key goes on the “many” side: orders.customer_id.
One-to-one (1:1)
Each user has at most one profile. Put a foreign key with a UNIQUE constraint on one side, e.g. profiles.user_id. Often used to split rarely used or sensitive columns into their own table.
Many-to-many (M:N)
An order contains many products, and a product appears in many orders. Relational databases model this with a junction table (also called a join or bridge table) holding two foreign keys:
CREATE TABLE order_items (
order_id INT NOT NULL REFERENCES orders(id),
product_id INT NOT NULL REFERENCES products(id),
quantity INT NOT NULL DEFAULT 1,
unit_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);The junction table is a good place for data about the relationship itself — here, quantity and the price at the time of the order.
Reading crow's-foot notation
Crow's-foot notation puts symbols at each end of a relationship line. Each end shows a minimum and a maximum:
| Symbol at the line end | Meaning |
|---|---|
| Two bars ( || ) | Exactly one (one and only one) |
| Circle + bar ( o| ) | Zero or one (optional) |
| Bar + crow's foot ( |< ) | One or many |
| Circle + crow's foot ( o< ) | Zero or many |
So customers ||——o< orders reads: “each order belongs to exactly one customer; a customer has zero or more orders.” Read each symbol from the other table's point of view: the symbol next to orders says how many orders one customer can have.
Worked example: a small online shop
customers (id PK, name, email UNIQUE, created_at)
orders (id PK, customer_id FK → customers.id, status, placed_at)
products (id PK, name, price, sku UNIQUE)
order_items(order_id FK → orders.id, product_id FK → products.id,
quantity, unit_price, PK(order_id, product_id))- customers ||——o< orders: a customer can exist with no orders yet.
- orders ||——|< order_items: every order has at least one line.
- products ||——o< order_items: a product may not have sold yet.
Drawn as an ERD, the orders → order_items ← products shape immediately shows the many-to-many relationship between orders and products.

How to draw a clear ERD
- 1List the entities — the nouns in your requirements (customer, order, product).
- 2Add key attributes and mark the primary key of each.
- 3Connect related entities and decide the cardinality at both ends.
- 4Resolve many-to-many relationships with junction tables.
- 5Add foreign keys on the “many” side and decide what happens on delete (restrict, cascade or set null).
- 6Arrange for readability: central tables in the middle, few crossing lines, related tables grouped.
For an existing database, you can work backwards: export the schema as SQL and import it into a diagramming tool instead of drawing by hand.
Try the free ERD MakerDraw tables and crow's-foot relationships, import SQL or DBML, and generate SQL for several databases.Related diagrams
An ERD shows how data is structured. To show how data moves — from a form to an API to a database and out to a report — use a data-flow diagram. For application logic, a flowchart. Together they document a system far better than any one of them alone. See how to organize information visually for choosing between them.
Try the free Data Flow Diagram MakerMap processes, data stores and external entities.Try the free SQL FormatterTidy the CREATE TABLE statements behind your diagram.Frequently asked questions
What is an entity-relationship diagram?
A diagram of a database's structure: each entity (usually a table) is a box listing its attributes, and lines between them show relationships and their cardinality.
How do you represent a many-to-many relationship?
With a junction table that holds foreign keys to both tables, such as order_items linking orders and products. Its primary key is often the pair of foreign keys.
Which side gets the foreign key in a one-to-many relationship?
The “many” side. In customers-to-orders, orders.customer_id refers to customers.id.
What does crow's-foot notation mean?
Symbols at each end of a relationship line show the minimum and maximum number of related rows: a bar means one, a circle means zero, and the three-pronged “crow's foot” means many.



