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

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?

NoSQL vs SQL Cheat Sheet

Stored Procedures & Triggers Cheat Sheet

Explore Topics

#DatabaseDesignERDiagrams#DatabaseDesignERDiagramsCheatSheet#Database#Beginner#ERDiagramNotation#Normalization#ERDiagramToSQLSchema#KeyTypes#Databases#CheatSheet#SkillVeris

Sri Hayavadhana Info-Tech

Professional Web Designing Services