Database Design & ER Diagrams Cheat Sheet
Explains entity-relationship modeling, cardinality notation, normalization rules, and translating an ER diagram into physical table schemas.
2 PagesBeginnerFeb 25, 2026
ER Diagram Notation
The building blocks of an entity-relationship diagram.
- Entity- A real-world object or concept represented as a table (e.g., Customer, Order); drawn as a rectangle
- Attribute- A property of an entity (e.g., Customer.email); becomes a column in the resulting table
- Relationship- An association between two entities (e.g., Customer places Order); drawn as a diamond or a line with a verb label
- Cardinality- Describes how many instances relate: one-to-one (1:1), one-to-many (1:N), or many-to-many (M:N)
- Weak entity- An entity that cannot exist without a parent (e.g., OrderLine depends on Order); identified by the parent's key plus a partial key
- Participation constraint- Whether every instance of an entity must participate in the relationship (total/mandatory) or not (partial/optional)
Normalization
The standard normal forms and why they exist.
- 1NF (First Normal Form)- Every column holds atomic values, no repeating groups or arrays in a single cell
- 2NF (Second Normal Form)- 1NF plus every non-key column depends on the whole primary key, not just part of a composite key
- 3NF (Third Normal Form)- 2NF plus no transitive dependencies — non-key columns depend only on the key, not on other non-key columns
- BCNF (Boyce-Codd Normal Form)- A stricter 3NF: every determinant (column that determines another) must be a candidate key
- Denormalization- Deliberately duplicating data to reduce joins and improve read performance, trading write complexity/storage for speed
ER Diagram to SQL Schema
Translating entities and a many-to-many relationship into tables.
sql
-- Entities: Customer, Product; Relationship: Order (M:N via a junction table)CREATE TABLE customers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL);CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, price NUMERIC(10,2) NOT NULL);CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INT NOT NULL REFERENCES customers(id), created_at TIMESTAMP DEFAULT now());-- Junction table resolves the M:N between orders and productsCREATE TABLE order_items ( order_id INT REFERENCES orders(id), product_id INT REFERENCES products(id), quantity INT NOT NULL CHECK (quantity > 0), PRIMARY KEY (order_id, product_id));
Key Types
Different roles keys play in a relational schema.
- Primary key- Uniquely identifies each row in a table; must be non-null and unique
- Foreign key- A column referencing another table's primary key, enforcing referential integrity
- Composite key- A primary key made of two or more columns together (common in junction tables)
- Candidate key- Any column set that could uniquely identify a row; one is chosen as the primary key, others become unique constraints
- Surrogate key- An artificial key (e.g., auto-increment ID or UUID) with no business meaning, used instead of a natural key
Supertype/Subtype (Inheritance) Modeling
Model an 'is-a' hierarchy of entities using three common table strategies.
sql
-- Strategy 1: Single Table Inheritance (one table, nullable subtype columns)CREATE TABLE vehicles ( id SERIAL PRIMARY KEY, type VARCHAR(20) NOT NULL CHECK (type IN ('car', 'truck')), seats INT, -- only meaningful for 'car' cargo_capacity INT -- only meaningful for 'truck');-- Strategy 2: Class Table Inheritance (a table per subtype, sharing a PK)CREATE TABLE vehicles_base (id SERIAL PRIMARY KEY, make VARCHAR(100));CREATE TABLE cars (id INT PRIMARY KEY REFERENCES vehicles_base(id), seats INT);CREATE TABLE trucks (id INT PRIMARY KEY REFERENCES vehicles_base(id), cargo_capacity INT);-- Strategy 3: Concrete Table Inheritance (no shared base table, duplicate columns)CREATE TABLE cars_concrete (id SERIAL PRIMARY KEY, make VARCHAR(100), seats INT);CREATE TABLE trucks_concrete (id SERIAL PRIMARY KEY, make VARCHAR(100), cargo_capacity INT);
Self-Referencing (Recursive) Relationships
Modeling hierarchies like org charts or category trees within a single table.
sql
-- Adjacency list: each row points to its parentCREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, manager_id INT REFERENCES employees(id));-- Recursive CTE walks the hierarchy from a rootWITH RECURSIVE org_chart AS ( SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, oc.depth + 1 FROM employees e JOIN org_chart oc ON e.manager_id = oc.id)SELECT * FROM org_chart ORDER BY depth;-- Alternative: closure table for fast ancestor/descendant queries at scaleCREATE TABLE employee_paths ( ancestor_id INT REFERENCES employees(id), descendant_id INT REFERENCES employees(id), depth INT NOT NULL, PRIMARY KEY (ancestor_id, descendant_id));
Referential Integrity: ON DELETE/UPDATE Actions
How foreign keys should behave when a referenced row changes or disappears.
- CASCADE- Deleting/updating the parent automatically deletes/updates matching child rows (e.g., deleting an order deletes its order_items)
- RESTRICT- Blocks the delete/update on the parent if any child row still references it; the strictest, safest default for most business data
- SET NULL- Sets the child's foreign key to NULL when the parent is deleted; requires the FK column to be nullable
- SET DEFAULT- Resets the child's foreign key to a predefined default value instead of NULL
- NO ACTION (deferred)- Like RESTRICT but the check can be deferred to transaction commit (DEFERRABLE INITIALLY DEFERRED), useful for circular references within one transaction
Temporal Tables & Soft Deletes
Preserving history and avoiding destructive deletes in a normalized schema.
sql
-- Soft delete: never physically remove rows, filter them out insteadALTER TABLE customers ADD COLUMN deleted_at TIMESTAMP;CREATE INDEX idx_customers_active ON customers (id) WHERE deleted_at IS NULL;SELECT * FROM customers WHERE deleted_at IS NULL;-- Bitemporal / history table pattern: track valid_from/valid_to per versionCREATE TABLE customer_history ( id INT, name VARCHAR(255), address VARCHAR(255), valid_from TIMESTAMP NOT NULL, valid_to TIMESTAMP NOT NULL DEFAULT 'infinity', PRIMARY KEY (id, valid_from));-- Postgres 17+ / SQL:2011 system-versioned tables (where supported)-- automatically maintain row history without hand-rolled triggers
When to Deliberately Break Normal Form
Reasons a relational schema intentionally departs from strict 3NF/BCNF.
- Read-heavy reporting tables- Pre-joining and flattening data into a reporting/star-schema table avoids expensive joins on every dashboard query
- Derived/calculated columns- Storing a computed total (with a trigger or GENERATED ALWAYS AS column) trades storage and write cost for avoiding recomputation on every read
- Snapshot columns- Storing customer_name on an order row at time of purchase preserves historical accuracy even if the customer later renames their account
- Star/snowflake schema (OLAP)- Data warehouses intentionally denormalize into fact and dimension tables optimized for aggregation, not transactional integrity
- Materialized views- A middle ground: keep the base schema normalized but persist a denormalized, indexable snapshot that's refreshed on a schedule or trigger
Pro Tip
Model many-to-many relationships with an explicit junction table from the start, even if today it looks like a simple pair — adding metadata later (quantity, timestamp, status) to a bare M:N join is far easier than migrating away from an implicit relationship.
Was this cheat sheet helpful?
Explore Topics
#DatabaseDesignERDiagrams#DatabaseDesignERDiagramsCheatSheet#Database#Beginner#ERDiagramNotation#Normalization#ERDiagramToSQLSchema#KeyTypes#Databases#CheatSheet#SkillVeris
