An entity-relationship diagram gives a shared picture of the data model. Its boxes identify entities, and its connectors explain how many rows can be related. The diagram is useful only when readers know the notation and the business meaning. This reference uses a small shop model to explain cardinality, optionality, primary keys, and foreign keys. A readable text diagram is included so the relationships remain clear without a drawing tool. You will translate the picture into SQL constraints and learn which promises a simple foreign key cannot enforce by itself, such as every order having at least one item.
Read the entities and relationships first
A customer is an account or person under the application's definition; an order is a purchase event; a product is a catalog item; an order item records a product within an order. Put stable keys and important attributes in each entity box. Draw a connector only when the business recognizes a relationship. A foreign key is the implementation of many such relationships, but the conceptual diagram comes from requirements. Name relationships with verbs such as customer places order and order contains item. Avoid lines that merely mean two tables are sometimes queried together. Write definitions for ambiguous words such as account, member, and owner so the diagram does not conceal contradictory assumptions.
Use a legend for cardinality and optionality
The notation below writes minimum and maximum counts explicitly instead of relying on unexplained symbols. Reading from customer to order, one customer may have zero or many orders. Reading from order to customer, each order belongs to exactly one customer. An order may temporarily have zero items while in a draft state; a confirmed order's minimum-item rule requires extra business enforcement.
CUSTOMER
customer_id PK
1 ---------------- 0..many
ORDER
order_id PK
customer_id FK, NOT NULL
1 ----------- 0..many
ORDER_ITEM
order_id PK/FK
product_id PK/FK
quantity
many ------ 1
PRODUCT
product_id PKThe ORDER_ITEM primary key is the pair (order_id, product_id) only if the business allows one line per product per order. If separate lines can share a product, introduce a line identifier or line number and preserve the intended rule.
Translate one-to-many into a child foreign key
Place customer_id in orders because many orders point to one customer. Mark it NOT NULL when every order must have a customer. The foreign key requires the referenced customer to exist, and the primary key on customers identifies the parent. Do not also store a comma-separated list of order IDs in customers; the relationship can be queried from orders. A nullable customer_id means an order can have no customer under the schema, which may fit guest orders only if the business agrees. A foreign key does not require every customer to have an order. That matches the optional zero minimum on the customer side. Check the direction of each rule when translating the diagram.
Translate one-to-one and many-to-many carefully
A one-to-one relationship uses a foreign key with uniqueness on the referencing side, often combined with a shared primary key. NOT NULL makes the child-to-parent link required, but it does not force every parent to have a child row. A many-to-many relationship needs a junction entity such as order_items or enrollments. The junction contains two foreign keys and usually a unique pair or composite primary key to prevent duplicate associations. Attributes belonging to the relationship, such as quantity or enrollment date, belong in that junction. Do not put a single product_id in orders if orders can contain many products. The resulting schema would contradict the diagram's maximum cardinality.
Separate diagram promises from enforcement limits
The notation may say a confirmed order contains one or more items, yet a foreign key from items to orders only prevents orphan items. It does not stop an empty order. Enforce that minimum through the confirmation transaction, a reviewed database mechanism, or a design where the state transition checks required items. Aggregate capacity rules and temporal overlap rules have similar limits. Document which constraints enforce each relationship and which application operations enforce additional rules. Referential actions such as CASCADE or RESTRICT describe deletion behavior, not ordinary cardinality. Choose them from business retention requirements. A neat diagram should never create the false impression that every connector automatically becomes a complete database guarantee.
Maintain the diagram alongside the schema
Keep the logical diagram and actual migration definitions synchronized. An ER tool can reverse-engineer a schema, but that shows implemented keys rather than every intended business rule. Review nullable foreign keys and missing unique constraints because they often reveal mismatches. Use a fixture with an optional parent, several children, and a forbidden orphan to verify interpretation. Record dialect assumptions: foreign-key enforcement settings, supported table engines, identity syntax, and constraint behavior. Use clear layout and a legend, and avoid crowding every technical index into a beginner conceptual diagram. More detailed physical diagrams can include indexes and types when they help engineers operate the system.
Practice task and worked solution
Draw an ER diagram for students and scheduled sessions where many students attend many sessions. Add an enrollment junction with enrollment_date and status. The solution labels minimum and maximum counts in both directions, chooses a unique student-session pair under a one-enrollment policy, and uses non-null foreign keys in the junction. Explain whether canceled enrollments are retained and how that changes the uniqueness rule. Translate the diagram into DDL and test an orphan enrollment and a duplicate pair. Then state the extra rule for a session requiring at least five confirmed students before it runs; a simple foreign key cannot enforce that aggregate minimum.
Continue learning
Review relationships, constraints, and table creation.
The PostgreSQL constraint documentation describes the keys and nullability used to implement relationships.
Entity-Relationship (ER) Diagrams Examples
Each example shows the code, the output it produces and what each part does.
Enforce the customer-to-order link
The generic SQL fixture uses explicit IDs. Foreign-key enforcement must be active in the selected engine, including SQLite connection configuration.
Ada has two orders while Sam has none. Both states are allowed by customer 1 to order 0..many. An order referring to customer 999 would be rejected when the foreign key is enforced.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Sam');
INSERT INTO orders VALUES (10, 1), (11, 1);
SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;name | orders
Ada | 2
Sam | 0Entity-Relationship (ER) Diagrams FAQ
Does NOT NULL mean every parent has a child?
No. A NOT NULL foreign key requires a value in each child row, so every booking must belong to a customer, but it says nothing about the parent side: a customer can still have no bookings. Diagrams show the two sides' optionality separately.
Why a junction table?
Relational tables can't store a list of related ids in one column cleanly. A junction table represents many-to-many pairs, one row per pair, and holds attributes of the relationship itself, such as the date a student enrolled on a course.
Can a foreign key enforce one child maximum?
Not on its own. A foreign key allows many children per parent. Add uniqueness on the child reference, UNIQUE (customer_id), to make it one-to-one, so each parent can have at most one matching child.
Should the diagram contain every index?
Not necessarily. A conceptual diagram can stay focused on entities, relationships and keys so the business structure is easy to discuss. A detailed physical design or the schema itself documents indexes, types and storage choices.
Which tools can I use to draw ER diagrams?
General diagram tools such as draw.io, Lucidchart and Miro have ER shapes, and dbdiagram.io lets you describe tables as text. Database tools such as DBeaver, pgAdmin and MySQL Workbench can generate a diagram from an existing database, which is a good way to understand an unfamiliar schema.
Entity-Relationship (ER) Diagrams Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
A diagram promises every confirmed order has an item. What is needed beyond the child foreign key?
Entity-Relationship (ER) Diagrams Quiz
2 questions to check what you learned.
What implements the required order-to-customer link?
- A nullable foreign key with no required-reference rule
- A customer name copied into the order without a key
- A non-null foreign key in orders
- A list of order IDs stored in the customer row
What enforces at most one profile for a customer?
- A foreign key alone with repeated customer values permitted
- NOT NULL alone on the profile name
- A report that uses DISTINCT to hide duplicate profiles
- A unique customer reference in the profile table