ER Diagrams & Data Modeling Cheat Sheet
Entity-relationship notation (crow's foot, Chen), cardinality, keys, normalization, and schema design patterns.
Databases
er-diagram
data-modeling
database
An entity-relationship diagram maps the things a system stores (entities), their properties (attributes), and how they connect (relationships). A clear ER model precedes a clean relational schema.
Core concepts
Table
| Concept | Meaning |
|---|---|
| Entity | A thing with identity (e.g. Customer). |
| Attribute | A property of an entity (e.g. email). |
| Primary key (PK) | Uniquely identifies a row. |
| Foreign key (FK) | References another table's PK. |
| Relationship | A link between entities (e.g. places). |
| Cardinality | How many on each side (1:1, 1:N, N:M). |
Cardinality (crow's foot)
Table
| Notation | Meaning |
|---|---|
1 ─── 1 | One-to-one. |
1 ───< | One-to-many (one customer, many orders). |
>───< | Many-to-many (students ↔ courses). |
0..1 | Optional (zero or one). |
1..* | One or more. |
Resolving many-to-many
A many-to-many relationship becomes a junction (associative) table holding two foreign keys.
sql
CREATE TABLE enrollment (
student_id INT REFERENCES student(id),
course_id INT REFERENCES course(id),
PRIMARY KEY (student_id, course_id)
);
Normalization
Table
| Form | Rule |
|---|---|
| 1NF | Atomic values; no repeating groups. |
| 2NF | 1NF + no partial dependency on a composite key. |
| 3NF | 2NF + no transitive dependency (non-key → non-key). |
| BCNF | Every determinant is a candidate key. |
Design patterns
Table
| Pattern | When to use |
|---|---|
| Junction table | Many-to-many. |
| Self-reference | Hierarchies (manager → employees). |
| Soft delete | Preserve history with deleted_at. |
| Audit columns | created_at, updated_at on every table. |
| Polymorphic FK | One column referencing multiple tables (use sparingly). |