Chapter 3: Data Modelling & Database Design¶
Before any data is stored, someone has to decide what the tables, documents and keys should look like. This chapter walks from requirements to conceptual, logical and physical models; ER diagrams, keys and modern ID choices; functional dependencies and every normal form with worked examples; when to denormalise; dimensional modelling, slowly changing dimensions, Kimball, Inmon and Data Vault for analytics; access-pattern-first modelling for NoSQL; safe schema migrations; naming conventions, classic anti-patterns and choosing data types well.
3.1 From Requirements to Conceptual, Logical and Physical Models¶
Database design turns a plain-language description of a business into tables a computer can store. Designers work in three levels: a conceptual model (the things and how they relate), a logical model (tables, columns and keys, independent of any product) and a physical model (the exact database product, data types, indexes and storage).
Think of it like…
It is like building a house: first a napkin sketch of rooms and how they connect, then a measured floor plan, and finally the builder's construction drawings that name the exact bricks, pipes and wiring.
Real-world example
Amara designs a loans system for the fictional Greenleaf Libraries. Interviews give her nouns (member, book, copy, branch, loan) and rules ("a member may hold at most 5 loans"). Her conceptual ER diagram shows Member borrows Copy. The logical model adds keys and normalises to 3NF. The physical model picks PostgreSQL, a date column for due_date, an index on (member_id, returned_at) and a CHECK on loan length.
How it works¶
- Gather requirements: interview users, collect forms, reports and the questions the system must answer, and write down business rules.
- Build a conceptual model: identify entities, relationships and cardinalities in an ER diagram that business people can read.
- Build a logical model: add attributes, primary and foreign keys, and normalise (usually to 3NF) without choosing a product.
- Build a physical model: map to a specific DBMS with data types, constraints, indexes, partitioning and naming conventions.
- Validate with sample data and the real queries, review with stakeholders, then generate DDL and manage later changes as migrations.
Who uses it
Data modellers, data architects, backend developers, analytics engineers and DBAs all design or review models; business analysts help capture the requirements.
Key takeaway
Memorise the one-liner: conceptual = what the business cares about, logical = how it is structured independent of product, physical = how one specific DBMS will store it.
Going deeper
- The ANSI/SPARC three-schema architecture (1975-78) separates external views, a conceptual schema and an internal storage schema, so applications survive storage changes; this is the idea of data independence.
- Business rules become structure: "each loan is for exactly one copy" is a cardinality, "at most 5 open loans" needs a trigger or application check, and "due within 28 days" becomes a CHECK constraint.
- Physical design depends on the workload: estimate row counts, growth per year, the read/write ratio and the top ten queries, because indexes and partitioning are chosen for those queries, not for the diagram.
- Modelling tools forward-engineer a diagram into DDL and reverse-engineer an existing database into a diagram; comparing the two reveals drift between documentation and reality.
- A data dictionary or business glossary records each column's meaning, unit, allowed values and owner, which later feeds data catalogs and governance tools in the analytics world.
Common mistakes & misconceptions
- Jumping straight to CREATE TABLE: skipping the conceptual model hides misunderstood rules until data is already wrong.
- Designing without the queries: a model that ignores how data will be read often needs painful redesign or heavy denormalisation later.
- Treating the model as finished: requirements change, so plan for versioned migrations rather than a one-time design document.
Try it: A tiny physical model for the library example (PostgreSQL)¶
CREATE TABLE member (
member_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
full_name text NOT NULL
);
CREATE TABLE loan (
loan_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
member_id bigint NOT NULL REFERENCES member,
copy_id bigint NOT NULL,
loaned_at date NOT NULL DEFAULT current_date,
due_at date NOT NULL,
returned_at date,
CHECK (due_at <= loaned_at + 28)
);
CREATE INDEX ON loan (member_id) WHERE returned_at IS NULL;
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| Requirements gathering Business analysis practice · Concept · Current · Beginner · General |
Collecting what data must be stored and which questions it must answer, through interviews, existing forms, reports and process walkthroughs. | A clinic's receptionists show paper intake forms; each box on the form becomes a candidate attribute. |
| Business rule Data modelling practice · Concept · Current · Beginner · General |
A precise statement of how the organisation works that the data must respect, such as limits, required fields or allowed combinations. | "An order must have at least one line" and "discounts never exceed 50%" become constraints or validation logic. |
| Conceptual data model Data modelling practice · Concept · Current · Beginner · General |
A high-level picture of the main business entities and relationships, with no technical detail, readable by non-technical stakeholders. | Student enrols in Course; Lecturer teaches Course — drawn as three boxes and two lines on a whiteboard. |
| Logical data model Data modelling practice · Concept · Current · Beginner · Relational |
Entities become tables with attributes, primary and foreign keys and normal forms, still independent of any specific database product. | Enrolment(student_id FK, course_id FK, enrolled_on, grade) with primary key (student_id, course_id). |
| Physical data model Data modelling practice · Concept · Current · Beginner · Relational |
The logical model mapped to one DBMS: exact data types, indexes, partitions, tablespaces, constraints and naming rules. | grade becomes CHAR(2) with a CHECK list in PostgreSQL, and enrolment gets an index on course_id. |
| Data dictionary / business glossary Data governance practice · Concept · Current · Beginner · General |
A reference listing every table and column with meaning, units, allowed values, sensitivity and owner. | The glossary says revenue_gbp excludes VAT and refunds, ending a month-long argument between finance and sales. |
| Forward and reverse engineering Modelling tool feature · Concept · Current · Intermediate · General |
Generating DDL from a diagram (forward) or rebuilding a diagram from a live database's catalog (reverse). | A consultant reverse-engineers a 400-table legacy ERP into a diagram before planning a migration. |
| erwin Data Modeler Quest Software · Tool · Current · Intermediate · General |
Long-established commercial modelling tool for conceptual, logical and physical models with DDL generation for many databases. | A bank's data architecture team keeps its enterprise logical model in erwin and generates Oracle and Db2 DDL from it. |
| ER/Studio Idera · Tool · Current · Intermediate · General |
Commercial enterprise data modelling suite with model repositories, glossary links and multi-database DDL support. | An insurer's modellers share one ER/Studio repository so changes to Policy are reviewed before reaching production. |
| dbdiagram.io and DBML Holistics (DBML is open source) · Tool · Current · Beginner · Relational |
Browser tool where you type tables in Database Markup Language and it draws the ER diagram and exports SQL. | A student writes Table orders { id int [pk] } and shares the auto-drawn diagram link with her project group. |
| pgModeler Open-source project (GPLv3) · Tool · Current · Intermediate · Relational |
Open-source desktop modeller built specifically for PostgreSQL, with diagram-to-SQL generation and schema diffing. | A startup designs its PostgreSQL schema in pgModeler and exports the CREATE scripts into its migrations folder. |
| MySQL Workbench Oracle · Tool · Current · Beginner · Relational |
Free desktop tool for MySQL with EER diagrams, forward/reverse engineering, SQL editing and administration. | A web agency reverse-engineers a client's WordPress database in Workbench to understand its tables. |
| Hackolade Hackolade · Tool · Current · Intermediate · Document |
Data modelling tool aimed at NoSQL and schema-flexible systems such as MongoDB, DynamoDB, Cassandra and JSON Schema. | A team documents nested MongoDB documents and generates JSON Schema validators from the Hackolade model. |
3.2 ER Diagrams: Entities, Relationships, Cardinality and Notations¶
An Entity-Relationship (ER) diagram is a drawing of the things a business tracks (entities), their properties (attributes) and how they connect (relationships). Symbols on the lines show cardinality — how many of one thing can link to another — and whether the link is optional. Several drawing styles exist; crow's foot is the most common in industry, Chen in textbooks, UML in software design.
Three ER notations side by side
| Chen (1976) | Crow's foot | UML class diagram | |
|---|---|---|---|
| Entity | Rectangle | Rectangle with attribute list | Class box |
| Relationship | Diamond with verb | Labelled line | Association line |
| "Many" end | N or M beside line | Three-pronged foot | * or 0..* |
| Optional end | Single line (partial) | Circle (O) | 0..1 or 0..* |
| Weak entity | Double rectangle | Key includes parent FK | Composition (filled diamond) |
| Typical use | Textbooks, exams | Industry tools | Software design |
Same ideas, different symbols: learn crow's foot for work and Chen for exams.
Think of it like…
An ER diagram is like a family tree for data: boxes for people, lines for relationships, and little marks that say "exactly one mother" or "zero or more children".
Real-world example
Kwame models the fictional Ridgeway Clinic. Patient and Doctor are entities; Appointment links them many-to-many with a date and room. A Prescription has Prescription Lines that cannot exist without it, so each line is a weak entity identified by prescription number plus line number. Crow's foot marks show a patient may have zero or many appointments, but each appointment has exactly one patient.
How it works¶
- List the nouns that matter to the business; each becomes a candidate entity with attributes.
- Connect entities with verb-named relationships (Patient books Appointment).
- Set cardinality (1:1, 1:N, M:N) and optionality (minimum 0 or 1) at both ends of each line.
- Resolve many-to-many relationships into an associative entity that carries the relationship's own attributes.
- Mark weak entities, identifying relationships and any subtype/supertype hierarchies, then review the diagram with business users.
Who uses it
Students in every database course, data modellers, solution architects and backend developers drawing schemas for design reviews.
Key takeaway
Read each line in both directions as a sentence ("each order has one or more lines; each line belongs to exactly one order") — interviewers often ask you to do this out loud.
Going deeper
- Peter Chen's 1976 paper in ACM Transactions on Database Systems introduced the ER model; crow's foot notation is usually credited to Gordon Everest (1976) and spread through Oracle's Barker notation and CASE tools.
- Attributes come in kinds: simple vs composite (address = street + city), single- vs multivalued (phone numbers), stored vs derived (age from birth date); multivalued attributes become separate tables in the logical model.
- Min-max notation such as (0,N) or (1,1) states both participation and cardinality precisely and avoids confusion about which end of the line a symbol describes.
- Ternary relationships (Supplier supplies Part to Project) cannot always be replaced by three binary ones without losing meaning, which links directly to fifth normal form.
- Enhanced ER adds specialisation: a Vehicle supertype with Car and Truck subtypes, marked disjoint or overlapping and total or partial; these map to single-table, table-per-type or table-per-subclass designs.
- IDEF1X, published as US federal standard FIPS 184 in 1993, distinguishes identifying (solid) and non-identifying (dashed) relationships and is still offered by tools such as erwin.
Common mistakes & misconceptions
- Leaving many-to-many relationships unresolved: relational tables cannot store M:N directly, so you need a junction table with two foreign keys.
- Reading cardinality at the wrong end: in crow's foot the symbol nearest an entity describes how many of that entity relate to one on the other side.
- Modelling reports instead of things: "Monthly Sales Report" is an output, not an entity; model the underlying sales and derive the report.
Try it: Draw an ER diagram as code with Mermaid (renders on GitHub and in many wikis)¶
erDiagram
PATIENT ||--o{ APPOINTMENT : books
DOCTOR ||--o{ APPOINTMENT : attends
PRESCRIPTION ||--|{ PRESCRIPTION_LINE : contains
PATIENT {
bigint patient_id PK
text full_name
date born_on
}
APPOINTMENT {
bigint appointment_id PK
bigint patient_id FK
bigint doctor_id FK
timestamptz starts_at
}
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| Entity and entity type Chen ER model (1976) · Concept · Current · Beginner · General |
A distinguishable thing the business tracks; the entity type is the category, each entity is one instance. | Customer is the entity type; "Lena Fischer, customer 1042" is one entity instance. |
| Attribute kinds Chen ER model / EER · Concept · Current · Beginner · General |
Properties of an entity: simple or composite, single- or multivalued, stored or derived, and key attributes that identify it. | Address is composite, phone numbers multivalued, age derived from date_of_birth, and customer_no is the key attribute. |
| Relationship and degree Chen ER model (1976) · Concept · Current · Beginner · General |
An association between entities; degree counts participants — unary (recursive), binary or ternary. | Employee manages Employee is unary; Supplier supplies Part to Project is ternary. |
| Cardinality (1:1, 1:N, M:N) ER modelling · Concept · Current · Beginner · General |
The maximum number of instances on each side of a relationship: one-to-one, one-to-many or many-to-many. | One country has many cities (1:N); students and courses are M:N; a person and passport record are 1:1. |
| Participation / optionality ER modelling · Concept · Current · Beginner · General |
The minimum side of cardinality: total (mandatory, at least one) or partial (optional, may be zero). | Every order must have a customer (total), but a customer may have zero orders (partial). |
| Weak entity Chen ER model (1976) · Concept · Current · Intermediate · General |
An entity that cannot be identified by its own attributes alone and depends on an owner entity for its key. | Invoice line 3 only makes sense within invoice 8801, so its key is (invoice_no, line_no). |
| Identifying relationship ER modelling / IDEF1X · Concept · Current · Intermediate · Relational |
The link between a weak entity and its owner where the owner's key becomes part of the weak entity's primary key. | Building and Room: room key (building_code, room_no), so deleting a building deletes its rooms. |
| Associative (junction) entity ER modelling · Pattern · Current · Beginner · Relational |
An entity that resolves a many-to-many relationship and holds attributes belonging to the relationship itself. | Enrolment between Student and Course stores enrolled_on and grade, which belong to neither side alone. |
| Specialisation and generalisation Enhanced ER (EER) model · Concept · Current · Intermediate · General |
Supertype/subtype hierarchies: shared attributes live in the supertype, specific ones in subtypes, marked disjoint or overlapping. | Account generalises SavingsAccount (interest_rate) and CurrentAccount (overdraft_limit). |
| Chen notation Peter Chen, ACM TODS (1976) · Standard · Current · Beginner · General |
Original ER notation: rectangles for entities, diamonds for relationships and ovals for attributes. | University exam questions often ask students to draw a library system in Chen notation with cardinality labels. |
| Crow's foot notation Attributed to Gordon Everest (1976) · Standard · Current · Beginner · General |
Industry-standard ER style where line ends show one, many (a three-pronged foot) and optional (a circle). | A line ending ||--o{ reads "exactly one" on one side and "zero or many" on the other. |
| UML class diagram Object Management Group (UML 2.5.1) · Standard · Current · Intermediate · General |
Software-design diagram whose classes and associations with multiplicities like 0..* also describe data models. | A Java team's Order class with a 1..* composition to OrderLine maps directly to two tables. |
| IDEF1X US NIST, FIPS 184 (1993) · Standard · Legacy · Advanced · Relational |
Formal data-modelling notation separating identifying and non-identifying relationships, used in government and older enterprise models. | Defence contractors' legacy logical models in erwin are often drawn in IDEF1X style. |
| Barker notation Richard Barker / Oracle CASE (1990) · Standard · Legacy · Advanced · Relational |
Crow's-foot variant used in Oracle's CASE method, with dashed lines for optional and solid lines for mandatory ends. | Older Oracle Designer models and many UK textbooks use Barker-style softbox entities. |
| Mermaid erDiagram Mermaid open-source project · Tool · Current · Beginner · General |
Text syntax that renders ER diagrams in Markdown on GitHub, GitLab and documentation sites. | A pull request adds a new table and updates the Mermaid erDiagram in README.md so reviewers see the change. |
3.3 Keys and Identifiers: Natural, Surrogate, bigint, UUIDv4 and UUIDv7¶
A key is a column, or set of columns, whose values identify each row uniquely. A table may have several candidate keys; one becomes the primary key, and other tables point to it with foreign keys. You can use a real-world value (a natural key such as an ISBN) or an invented one (a surrogate key such as an auto-numbered bigint or a UUID).
bigint identity vs UUIDv4 vs UUIDv7
| bigint identity | UUIDv4 | UUIDv7 | |
|---|---|---|---|
| Size | 8 bytes | 16 bytes | 16 bytes |
| Generated by | Database sequence | Anyone, anywhere | Anyone, anywhere |
| Order | Increasing | Random | Time-ordered (ms) |
| B-tree inserts | Append at right edge | Random page splits | Mostly append |
| Guessable / leaks | Counts rows, guessable | Not guessable | Reveals creation time |
| Bits of randomness | None | 122 | Up to 74 |
UUIDv7 keeps UUID's generate-anywhere benefit while restoring index locality.
Think of it like…
A natural key is like identifying people by passport number, which can change or be missing; a surrogate key is like a membership card number your gym prints for you — meaningless, but stable and always present.
Real-world example
Ola builds the fictional ParcelHop delivery app. Customers get a bigint identity primary key, with email as a UNIQUE alternate key because people change emails. Parcels are created on phones offline, so they use UUIDv7 IDs generated on the device; because UUIDv7 starts with a timestamp, new rows still land at the end of the index instead of scattering across it.
How it works¶
- List candidate keys: minimal column sets that are unique and never null for every possible row.
- Pick a primary key that is unique, never null, stable over time and compact — often a surrogate.
- Keep natural candidate keys enforced with UNIQUE constraints so duplicates are still rejected.
- Add foreign keys in child tables, choose ON DELETE/ON UPDATE actions, and index FK columns that are used in joins.
- Choose an ID generator: database identity/sequence for single databases, UUIDv7 or Snowflake-style IDs for distributed or client-side generation.
Who uses it
Every backend developer, data modeller and DBA chooses keys; ID strategy is a classic system-design interview topic.
Key takeaway
Say it this way: a surrogate primary key plus UNIQUE constraints on natural keys gives stability and data quality; choose UUIDv7 over UUIDv4 when IDs must be generated outside the database.
Going deeper
- RFC 9562 (May 2024) replaced RFC 4122 and defined UUID versions 6, 7 and 8; UUIDv7 puts a 48-bit Unix millisecond timestamp first, then version and variant bits, then random or counter bits.
- In MySQL InnoDB the table is stored in primary-key order (a clustered index), so random UUIDv4 keys cause page splits, half-empty pages and extra buffer-pool misses; PostgreSQL B-tree indexes on UUIDv4 suffer similarly.
- PostgreSQL 18 (September 2025) added a built-in uuidv7() function; store UUIDs in a native 16-byte uuid or BINARY(16) column, never as CHAR(36) text that takes more than twice the space.
- A signed bigint tops out at 9,223,372,036,854,775,807, effectively inexhaustible, while a 32-bit int stops at 2,147,483,647 — a limit real products have hit, forcing emergency migrations.
- Twitter's 2010 Snowflake format packs a 41-bit millisecond timestamp, a 10-bit machine ID and a 12-bit sequence into 64 bits, allowing 4,096 IDs per millisecond per machine without coordination.
- Foreign-key actions — CASCADE, SET NULL, SET DEFAULT, RESTRICT and NO ACTION — decide what happens to children when a parent changes; PostgreSQL does not auto-index FK columns, whereas InnoDB requires and creates one.
Common mistakes & misconceptions
- Using a mutable natural key like email as the primary key: every change must cascade through all child tables; use a surrogate and keep email UNIQUE.
- Exposing sequential IDs in public URLs: competitors can count your orders; use UUIDs or a separate random public identifier.
- Storing UUIDs as varchar: it wastes space, slows comparisons and invites case and format mismatches; use the native uuid or 16-byte binary type.
Try it: Compare ID types in PostgreSQL 18¶
SELECT gen_random_uuid() AS uuid_v4,
uuidv7() AS uuid_v7,
uuid_extract_timestamp(uuidv7()) AS v7_time;
CREATE TABLE parcel (
parcel_id uuid PRIMARY KEY DEFAULT uuidv7(),
seq_no bigint GENERATED ALWAYS AS IDENTITY UNIQUE,
tracking text NOT NULL UNIQUE
);
SELECT pg_column_size(uuidv7()) AS uuid_bytes,
pg_column_size(1::bigint) AS bigint_bytes;
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| Primary-key selection criteria Relational design practice · Concept · Current · Beginner · Relational |
A good primary key is unique, never null, stable over time, minimal and compact, because every foreign key and index copies it. | A team rejects phone_number as PK (changes, shared by families) and picks a bigint identity instead. |
| Alternate key Relational design · Concept · Current · Beginner · Relational |
A candidate key not chosen as primary key, still enforced with a UNIQUE constraint to block duplicates. | Products use product_id as PK while sku remains UNIQUE NOT NULL as the alternate key. |
| Foreign key and referential actions SQL standard (REFERENCES) · Concept · Current · Beginner · Relational |
A column referencing another table's key, guaranteeing the target exists; ON DELETE/UPDATE actions define what happens to children. | order_line.order_id REFERENCES orders ON DELETE CASCADE removes lines automatically when an order is deleted. |
| Composite key Relational design · Concept · Current · Beginner · Relational |
A key made of two or more columns that are unique only in combination. | Seat bookings use (flight_no, flight_date, seat_no) as the primary key. |
| Natural key Relational design · Concept · Current · Beginner · Relational |
A key made of real-world data that already identifies things, such as ISBNs, country codes or VAT numbers. | Country uses ISO 3166 code 'GB' as its natural key — short, standard and rarely changing. |
| Surrogate vs natural key trade-off Relational design practice · Concept · Current · Intermediate · Relational |
Surrogates are stable and compact but meaningless; natural keys carry meaning but can change, repeat or be missing. | Orders reference customer_id 58213, while the natural email stays UNIQUE so duplicates are still caught. |
| bigint identity as default ID PostgreSQL, SQL Server, Oracle, Db2 (SQL:2003 identity) · Pattern · Current · Beginner · Relational |
The usual ID strategy for a single database: an 8-byte identity column — compact, ordered and practically inexhaustible. | ParcelHop's customer table uses bigint GENERATED ALWAYS AS IDENTITY; switching from int avoided a 2.1-billion ceiling. |
| Sequence SQL standard (CREATE SEQUENCE) · Feature · Current · Intermediate · Relational |
A database object that hands out increasing numbers; values are never rolled back, so gaps are normal. | After a failed insert, invoice numbers jump from 1041 to 1043 — the sequence does not reuse 1042. |
| UUIDv4 IETF RFC 9562 (2024; first in RFC 4122) · Standard · Current · Beginner · General |
A 128-bit identifier with 122 random bits; collisions are practically impossible, but values have no order. | A mobile app creates note IDs offline with crypto.randomUUID() and syncs them later without clashes. |
| UUIDv7 IETF RFC 9562 (May 2024) · Standard · Emerging · Intermediate · General |
A 128-bit identifier starting with a 48-bit millisecond timestamp, so values sort by creation time and index nicely. | PostgreSQL 18's uuidv7() as a primary-key default keeps inserts near the right-hand edge of the B-tree. |
| ULID Open specification (ulid/spec) · Format · Current · Intermediate · General |
128-bit sortable ID: 48-bit millisecond timestamp plus 80 random bits, written as 26 Crockford Base32 characters. | An event log uses 01J... ULIDs so files sorted by name are also sorted by time. |
| Snowflake ID Twitter (2010); variants at Discord, Instagram · Format · Current · Advanced · General |
A 64-bit ID made of timestamp, machine number and per-machine sequence, generated without central coordination. | Discord message IDs are Snowflakes, so a message's creation time can be decoded from its ID. |
| Public (external) identifier API design practice · Pattern · Current · Intermediate · General |
A separate random, non-guessable ID exposed in URLs and APIs, while the internal bigint key stays private. | Orders are shown as /orders/ord_7Fq2kZ9x while joins use order_id 1048576 internally. |
3.4 Functional Dependencies and Update Anomalies¶
A functional dependency (FD) says that knowing one value fixes another: if you know a student ID, you know the student's name. Writing these rules down shows where the same fact is stored many times. Redundancy causes anomalies — you cannot insert, update or delete one fact without disturbing another — and normalisation removes them using FDs.
Think of it like…
A postcode determines the town: if a spreadsheet repeats the town next to every customer's postcode, sooner or later someone types the town wrong in one row and the sheet contradicts itself.
Real-world example
The fictional Northgate College keeps one wide Enrolment sheet: student_id, student_name, course_id, course_title, lecturer_id, lecturer_name, grade. Because course_id determines course_title, the title is repeated 300 times; renaming a course means 300 updates. A new course cannot be added until a student enrols, and when the last student withdraws, the lecturer's name disappears too.
How it works¶
- List the attributes of the table and ask, for each pair, "if I know X, is Y always fixed?" using business rules, not just current data.
- Write FDs as X → Y (student_id → student_name; {student_id, course_id} → grade).
- Compute attribute closures (everything determined by X) to find candidate keys.
- Spot partial dependencies (on part of a composite key) and transitive ones (through a non-key attribute).
- Decompose the table so each FD's left side is a key, checking the split is lossless and preserves dependencies.
Who uses it
Database students (FDs are on every exam), data modellers fixing messy legacy tables, and data engineers profiling data quality.
Key takeaway
Memorise the three anomalies — insertion, update, deletion — and be ready to show each on a small example table; it is the standard way to motivate normalisation.
Going deeper
- Armstrong's axioms (1974) — reflexivity, augmentation and transitivity — are sound and complete: every FD implied by a set can be derived with them, plus derived rules for union, decomposition and pseudo-transitivity.
- The closure X+ is computed by repeatedly adding attributes determined by what you already have; if X+ contains every attribute of the table, X is a superkey, and if no proper subset is, X is a candidate key.
- A minimal (canonical) cover rewrites a set of FDs with single attributes on the right, no redundant FDs and no extraneous left-side attributes; 3NF synthesis algorithms start from it.
- Data can disprove an FD but never prove it: a table where course_id currently determines title may still allow two titles tomorrow, so FDs come from business rules, while data profiling tools only suggest candidates.
- Heath's theorem (1971) says that if X → Y holds in R(X, Y, Z), splitting into (X, Y) and (X, Z) is lossless; in general a binary split is lossless when the shared columns are a key of one side.
Common mistakes & misconceptions
- Reading an FD backwards: student_id → name does not mean name → student_id; two students can share a name.
- Inferring FDs from a small sample: coincidences in 50 rows are not rules; confirm each dependency with the business owner.
- Decomposing without checking losslessness: a bad split creates spurious rows when you join the pieces back together.
Try it: Test whether an FD (course_id → course_title) actually holds in your data¶
-- Any rows returned are violations of course_id -> course_title
SELECT course_id,
COUNT(DISTINCT course_title) AS titles,
array_agg(DISTINCT course_title) AS variants
FROM enrolment_raw
GROUP BY course_id
HAVING COUNT(DISTINCT course_title) > 1;
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| Functional dependency (X → Y) Codd (1971), relational theory · Concept · Current · Intermediate · Relational |
A rule that any two rows agreeing on X must agree on Y; X determines Y. | isbn → title: two rows with the same ISBN must show the same book title. |
| Determinant Relational theory · Concept · Current · Intermediate · Relational |
The left-hand side of a functional dependency — the attribute set that fixes other values. | In postcode → town, postcode is the determinant; BCNF requires every determinant to be a superkey. |
| Trivial dependency Relational theory · Concept · Current · Advanced · Relational |
An FD whose right side is part of its left side, so it is always true and tells you nothing. | {order_id, product_id} → product_id holds in every table and is ignored during normalisation. |
| Partial dependency Relational theory (2NF) · Concept · Current · Intermediate · Relational |
A non-key attribute that depends on only part of a composite candidate key; the target of second normal form. | In (order_id, product_id, order_date), order_date depends only on order_id — a partial dependency. |
| Transitive dependency Relational theory (3NF) · Concept · Current · Intermediate · Relational |
A non-key attribute determined through another non-key attribute: key → A → B; the target of third normal form. | employee_id → dept_id → dept_name means dept_name is stored redundantly for every employee. |
| Multivalued dependency (X →→ Y) Fagin (1977) · Concept · Current · Advanced · Relational |
X determines a set of Y values independently of other attributes; the basis of fourth normal form. | A course has several teachers and several textbooks, chosen independently: course →→ teacher and course →→ textbook. |
| Armstrong's axioms William W. Armstrong (1974) · Concept · Current · Advanced · Relational |
Three inference rules — reflexivity, augmentation, transitivity — that derive all FDs implied by a given set. | From A → B and B → C, transitivity gives A → C; augmentation then gives AD → CD. |
| Attribute closure (X+) Relational theory · Algorithm · Current · Advanced · Relational |
All attributes functionally determined by X; used to test keys and implied dependencies. | With A→B, B→C, CD→E: {A,D}+ = {A,B,C,D,E}, so AD is a key of R(A,B,C,D,E). |
| Minimal (canonical) cover Relational theory · Algorithm · Current · Advanced · Relational |
A simplified equivalent set of FDs with no redundant rules or extra attributes; the input to 3NF synthesis. | {A→BC, B→C, AB→C} reduces to {A→B, B→C} because the other rules are implied. |
| Insertion anomaly Database design theory · Concept · Current · Beginner · Relational |
Being unable to record one fact without inventing another, because facts share a single table. | A new course cannot be stored until at least one student enrols, since student_id is part of the key. |
| Update anomaly Database design theory · Concept · Current · Beginner · Relational |
A fact repeated in many rows must be changed everywhere; missing one leaves the data contradictory. | Renaming course CS101 in 299 of 300 rows leaves the college with two titles for one course. |
| Deletion anomaly Database design theory · Concept · Current · Beginner · Relational |
Deleting one fact accidentally destroys another that was only stored alongside it. | When the last student leaves course CS205, the only record of its lecturer disappears too. |
| Lossless-join decomposition Relational theory (Heath 1971) · Concept · Current · Advanced · Relational |
Splitting a table so that joining the parts gives back exactly the original rows, no more and no fewer. | Splitting enrolment on course_id into course and enrolment tables is lossless because course_id keys the course table. |
| Dependency preservation Relational theory · Concept · Current · Advanced · Relational |
A decomposition where every original FD can still be checked inside a single resulting table, without joins. | BCNF splits can lose a dependency, forcing a trigger to enforce it; 3NF synthesis always preserves them. |
| Data profiling Data quality practice (e.g. pandas, Great Expectations) · Tool · Current · Intermediate · General |
Analysing actual data to discover candidate keys, null rates and likely FDs before or during redesign. | Profiling a 2-million-row CSV shows postcode → town holds in 99.7% of rows, revealing 6,000 typos. |
3.5 Normalisation Step by Step: 1NF, 2NF and 3NF¶
Normalisation is a step-by-step method for splitting tables so that each fact is stored once. First normal form (1NF) means one value per cell and no repeating groups; 2NF removes facts that depend on only part of a composite key; 3NF removes facts that depend on other non-key columns. Most business (OLTP) databases are designed to at least 3NF.
Think of it like…
It is like tidying a messy drawer into labelled boxes: socks in one, chargers in another — you always know where each thing lives, and you never have to update two boxes when one thing changes.
Real-world example
The fictional Bluebird Bakery tracks orders in a sheet: order 77, 2026-03-02, customer C9 'Tomás Ruiz', 'Leeds', products 'Croissant×2, Bagel×1'. 1NF: one row per product line with key (order_id, product_id). 2NF: order_date and customer_id move to Orders; product name and list price move to Products. 3NF: customer name and city move to Customers, because they depend on customer_id, not on the order.
How it works¶
- Start from the unnormalised data and identify repeating groups and multi-value cells.
- 1NF: make every cell atomic, give each row a primary key, and turn repeating groups into separate rows.
- 2NF: for composite keys, move attributes that depend on only part of the key into their own table.
- 3NF: move attributes that depend on another non-key attribute (transitive dependencies) into their own table.
- Re-link the tables with foreign keys and check the joins reproduce the original data (lossless).
Who uses it
Every database student, backend developer designing OLTP schemas, and interviewer asking "normalise this table" on a whiteboard.
Key takeaway
Memorise Bill Kent's summary: every non-key attribute must depend on the key (1NF), the whole key (2NF) and nothing but the key (3NF).
Going deeper
- Codd defined 1NF in 1970 and 2NF and 3NF in 1971; Bill Kent's 1983 Communications of the ACM article "A Simple Guide to Five Normal Forms" remains the clearest short explanation.
- Formally, a table is in 3NF if for every non-trivial FD X → A, either X is a superkey or A is part of some candidate key (a prime attribute) — the second clause is what separates 3NF from BCNF.
- 2NF only matters when a table has a composite candidate key; a table whose only key is a single column and which satisfies 1NF is automatically in 2NF.
- Storing unit_price on order_line is not a 3NF violation: it records the price actually charged on that date, a different fact from the product's current list price.
- Bernstein's 1976 synthesis algorithm builds a 3NF schema from a minimal cover that is always lossless and dependency-preserving, which is why 3NF is the practical target for most OLTP designs.
- "Atomic" is relative: C. J. Date argues arrays or JSON can satisfy 1NF if treated as single values; the practical test is whether you will search, join or constrain the parts — if so, split them.
Common mistakes & misconceptions
- Thinking 1NF just means no commas: columns like phone1, phone2, phone3 are still a repeating group and belong in a separate table.
- Over-normalising lookups: splitting every short code into its own table can add joins without removing any real redundancy.
- Moving historical values out by mistake: an invoice's address at time of sale must be copied and kept, not replaced by a link to the current address.
Try it: Bluebird Bakery in 3NF (works in PostgreSQL, MySQL and SQLite with small type tweaks)¶
CREATE TABLE customer (customer_id int PRIMARY KEY, full_name text NOT NULL, city text NOT NULL);
CREATE TABLE product (product_id int PRIMARY KEY, name text NOT NULL, list_price numeric(8,2) NOT NULL);
CREATE TABLE orders (order_id int PRIMARY KEY, order_date date NOT NULL,
customer_id int NOT NULL REFERENCES customer);
CREATE TABLE order_line (
order_id int REFERENCES orders,
product_id int REFERENCES product,
qty int NOT NULL CHECK (qty > 0),
unit_price numeric(8,2) NOT NULL, -- price actually charged
PRIMARY KEY (order_id, product_id)
);
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| Normalisation E. F. Codd (1970-71) · Concept · Current · Beginner · Relational |
A design process that decomposes tables using dependencies so that each fact is stored once, eliminating anomalies. | A 25-column spreadsheet import becomes five related tables, and renaming a product now touches one row. |
| Unnormalised form (UNF) Relational design · Concept · Current · Beginner · Relational |
Data as it arrives — lists inside cells, repeating groups and mixed facts — before any normalisation. | A sign-up sheet with 'Mon, Wed, Fri' in a days column is in unnormalised form. |
| Repeating group Relational design · Concept · Current · Beginner · Relational |
Several values of the same kind stored in one row, either as a list in a cell or as numbered columns. | Columns skill1, skill2, skill3 on Employee form a repeating group; 1NF moves skills to their own table. |
| Atomic value Relational theory · Concept · Current · Beginner · Relational |
A value treated as indivisible by the database; the requirement behind first normal form. | Store first_name and last_name separately if you sort by surname; one full_name column is atomic otherwise. |
| First normal form (1NF) Codd (1970) · Concept · Current · Beginner · Relational |
Every column holds single atomic values, there are no repeating groups, and each row is identified by a key. | Order 77's 'Croissant×2, Bagel×1' becomes two rows: (77, croissant, 2) and (77, bagel, 1). |
| Second normal form (2NF) Codd (1971) · Concept · Current · Beginner · Relational |
1NF plus no non-key attribute depends on only part of a composite candidate key. | product_name depends only on product_id, not (order_id, product_id), so it moves to Products. |
| Third normal form (3NF) Codd (1971); Zaniolo's definition (1982) · Concept · Current · Beginner · Relational |
2NF plus no non-key attribute depends on another non-key attribute — no transitive dependencies. | customer_city depends on customer_id, not on order_id, so it moves from Orders to Customers. |
| Prime attribute Relational theory · Concept · Current · Advanced · Relational |
An attribute that belongs to at least one candidate key; 2NF and 3NF rules apply only to non-prime attributes. | In Tutoring(student, subject, tutor) with keys {student, subject} and {student, tutor}, every column is prime. |
| "The key, the whole key, nothing but the key" Bill Kent, CACM (1983) · Concept · Current · Beginner · Relational |
Memorable summary of 1NF-3NF: non-key facts depend on the key, all of it, and only it. | Students recite it in exams, often adding "so help me Codd" as a joke. |
| Lookup (reference) table Relational design · Pattern · Current · Beginner · Relational |
A small table of allowed codes and descriptions referenced by foreign keys instead of repeating text. | order_status(code PK, label) holds 'SHP' = 'Shipped', so labels can change in one place. |
| Junction (bridge) table Relational design · Pattern · Current · Beginner · Relational |
A table of foreign-key pairs that implements a many-to-many relationship after normalisation. | book_author(book_id, author_id) links 12,000 books with 9,000 authors, including co-authored books. |
| Point-in-time copy Relational design · Pattern · Current · Intermediate · Relational |
Deliberately storing the value that applied at transaction time, such as price or address, because it is a separate historical fact. | order_line.unit_price stays £2.10 even after the croissant's list price rises to £2.40. |
| 3NF synthesis algorithm Philip Bernstein (1976) · Algorithm · Current · Advanced · Relational |
Builds a 3NF schema from a minimal cover of FDs, guaranteeing a lossless and dependency-preserving result. | Textbook exercises group FDs with the same left side into tables, then add a table holding a candidate key if needed. |
3.6 Beyond 3NF: BCNF, 4NF, 5NF and 6NF¶
Higher normal forms deal with rarer kinds of redundancy that 3NF misses. Boyce-Codd normal form (BCNF) requires every determinant to be a candidate key. Fourth normal form removes independent multi-valued facts stored together, and fifth normal form removes redundancy that only appears when three or more relationships are combined. Sixth normal form splits tables down to one fact per row and is used for history-heavy warehouses.
Think of it like…
If 3NF is tidying the drawer into boxes, the higher forms are checking that no box secretly contains two unrelated collections taped together — rare in a normal house, but it matters in a museum archive.
Real-world example
At the fictional Oakfield Tutoring centre each tutor teaches exactly one subject, and each student has one tutor per subject. The table Tutoring(student, subject, tutor) is in 3NF but not BCNF, because tutor → subject while tutor is not a key. Splitting into TutorSubject(tutor, subject) and StudentTutor(student, tutor) removes the redundancy, but the rule "one tutor per subject per student" now needs a trigger.
How it works¶
- Check BCNF: for every non-trivial FD X → Y, X must be a superkey; if not, split on that FD.
- Check 4NF: look for independent multi-valued facts (course →→ teacher, course →→ textbook) in one table and separate them.
- Check 5NF: if a table can be rebuilt only from three or more projections (a cyclic business rule), decompose into those projections.
- Decide whether to accept 3NF instead of BCNF when the split would lose a dependency you must enforce.
- For temporal warehouses, consider 6NF-style tables (one attribute plus time per table) when attributes change at different rates.
Who uses it
Database theory students, data architects reviewing complex enterprise models, and warehouse designers using anchor or Data Vault models.
Key takeaway
Know the textbook BCNF counter-example (student, subject, tutor) and the fact that 3NF can always be reached without losing dependencies, while BCNF sometimes cannot.
Going deeper
- BCNF was defined by Raymond Boyce and Edgar Codd in 1974; it differs from 3NF only when a table has overlapping composite candidate keys, which is why most 3NF tables are already in BCNF.
- Ronald Fagin introduced 4NF (1977) using multivalued dependencies, 5NF or project-join normal form (1979) using join dependencies, and domain-key normal form (1981), which expresses all constraints through domains and keys.
- A classic 5NF case: if a supplier supplies a part, the supplier serves a project and the project uses the part, then the supplier supplies that part to that project; storing all three pairs separately is then lossless.
- Sixth normal form, defined by Date, Darwen and Lorentzos (2002) for temporal data, allows only a key plus at most one other attribute; anchor modelling (2009) and parts of Data Vault apply the idea.
- Essential tuple normal form (Darwen, Date and Fagin, 2012) sits between BCNF and 5NF and targets exactly the redundancy that causes duplicated tuples, giving a more practical goal than 5NF.
Common mistakes & misconceptions
- Assuming higher is always better: chasing 5NF or 6NF in an OLTP app adds many joins for redundancy that may never occur.
- Forgetting lost dependencies: a BCNF split can make a business rule unenforceable by keys alone, so add a constraint, trigger or stay in 3NF.
- Confusing 4NF problems with bad 1NF fixes: flattening two independent lists into one table creates the cross-product rows that 4NF removes.
Try it: Fixing a 4NF violation: independent teachers and textbooks per course¶
-- Before: every teacher x textbook combination must be stored
-- course | teacher | textbook (3 teachers x 2 books = 6 rows)
CREATE TABLE course_teacher (course_id int, teacher_id int,
PRIMARY KEY (course_id, teacher_id));
CREATE TABLE course_textbook (course_id int, isbn text,
PRIMARY KEY (course_id, isbn));
-- After: 3 + 2 = 5 rows, and adding a book is one insert
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| Boyce-Codd normal form (BCNF) Boyce and Codd (1974) · Concept · Current · Intermediate · Relational |
Every non-trivial functional dependency has a superkey as its determinant — a stricter version of 3NF. | Tutoring(student, subject, tutor) with tutor → subject violates BCNF because tutor is not a key. |
| BCNF decomposition algorithm Relational theory · Algorithm · Current · Advanced · Relational |
Repeatedly split a table on a violating FD X → Y into (X ∪ Y) and (rest ∪ X) until all parts are in BCNF. | Splitting Tutoring on tutor → subject yields TutorSubject(tutor, subject) and StudentTutor(student, tutor). |
| 3NF vs BCNF trade-off Database design practice · Concept · Current · Advanced · Relational |
BCNF removes more redundancy but may lose dependency preservation; 3NF can always preserve dependencies losslessly. | A team keeps a 3NF design and accepts tiny redundancy so a unique constraint still enforces one tutor per subject. |
| Overlapping candidate keys Relational theory · Concept · Current · Advanced · Relational |
Two composite candidate keys that share a column — the situation where 3NF and BCNF can differ. | {student, subject} and {student, tutor} overlap on student, which is why the tutoring table needs BCNF analysis. |
| Fourth normal form (4NF) Ronald Fagin (1977) · Concept · Current · Advanced · Relational |
BCNF plus no non-trivial multivalued dependencies except on a superkey; independent lists live in separate tables. | Splitting course-teacher-textbook rows into course_teacher and course_textbook removes the cross-product of combinations. |
| Join dependency Relational theory · Concept · Current · Advanced · Relational |
A table equals the join of several of its projections; the generalisation of multivalued dependency used by 5NF. | Supplies(supplier, part, project) can be rebuilt exactly from its three two-column projections under a cyclic rule. |
| Fifth normal form (5NF / PJNF) Ronald Fagin (1979) · Concept · Current · Advanced · Relational |
Every join dependency is implied by the candidate keys; no redundancy remains that only shows up in three-way combinations. | A distributor stores supplier-part, part-project and supplier-project tables instead of one three-way table. |
| Essential tuple normal form (ETNF) Darwen, Date and Fagin (2012) · Concept · Emerging · Advanced · Relational |
A normal form between BCNF and 5NF that guarantees no duplicated tuples arise from redundancy. | Advanced textbooks use ETNF to argue that full 5NF is rarely needed in practice. |
| Domain-key normal form (DKNF) Ronald Fagin (1981) · Concept · Legacy · Advanced · Relational |
An ideal where every constraint follows from domain (type) definitions and keys alone, leaving no other anomalies. | Mostly theoretical: real schemas still need CHECK constraints and triggers for cross-row rules. |
| Sixth normal form (6NF) Date, Darwen and Lorentzos (2002) · Concept · Current · Advanced · Relational |
Tables are irreducible: a key plus at most one attribute, often with a time period, so each fact changes independently. | customer_name_hist and customer_city_hist tables each track one attribute's history with valid-from dates. |
| Anchor modelling Rönnbäck, Regardt et al. (2009) · Data model · Current · Advanced · Analytical |
A 6NF-based warehouse technique with anchors, attributes, ties and knots, designed for non-destructive schema growth. | Adding a new customer attribute creates a new attribute table; existing tables and history stay untouched. |
| Normalisation exam checklist Teaching practice · Concept · Current · Intermediate · Relational |
Find all FDs, compute candidate keys, test each normal form in order, then decompose and verify losslessness. | A typical exam gives R(A,B,C,D,E) and five FDs, asking for keys, the highest normal form and a BCNF decomposition. |
3.7 Denormalisation: Breaking the Rules on Purpose¶
Denormalisation means deliberately storing some data more than once, or storing pre-computed results, so that important reads are faster or simpler. You pay for it with extra work on every write and the risk that copies drift apart. It should be a measured decision after indexes, better queries and caching have been tried — not a starting point.
Normalised vs denormalised design
| Normalised (3NF) | Denormalised | |
|---|---|---|
| Each fact stored | Once | Several times |
| Reads | More joins | Fewer joins, faster |
| Writes | Touch one row | Touch every copy |
| Consistency risk | Low | Copies can drift |
| Storage | Smaller | Larger |
| Best fit | OLTP, money, stock | Read-heavy pages, analytics |
Denormalisation moves cost from reads to writes and adds a consistency job.
Think of it like…
It is like writing a friend's phone number in both your phone and on the fridge: quicker to find when you need it, but if they change numbers you must remember to update both.
Real-world example
The fictional ShopLane marketplace shows product cards with star ratings. Computing AVG(rating) over 40 million reviews per page took 120 ms. The team added review_count and rating_avg columns on product, updated by the review service in the same transaction, cutting the card query to 3 ms. A nightly job recomputes both columns and alerts if any product drifts.
How it works¶
- Measure first: find the slow query with EXPLAIN and real timings, and try indexes, rewrites and caching.
- Choose the smallest denormalisation that fixes it: a copied column, a counter, a summary table or a materialized view.
- Pick a single source of truth and one mechanism that maintains the copy (same transaction, trigger, refresh job or event).
- Decide the acceptable staleness: synchronous for money and stock, seconds or hours for dashboards.
- Add reconciliation checks and document the duplicate so future developers know it must be kept in sync.
Who uses it
Backend developers tuning hot pages, analytics engineers building reporting tables, and architects designing read-heavy systems.
Key takeaway
Quote the saying "normalise until it hurts, denormalise until it works" — then explain how you would keep the copies consistent; that second part is what interviewers want.
Going deeper
- Every copy needs exactly one owner and one maintenance path; the classic failure is two code paths updating the same duplicated column differently, which no constraint will catch.
- Counter columns on hot rows create lock contention: 5,000 likes per second on one post serialise on a single row, so high-traffic systems batch increments or shard counters across several rows.
- PostgreSQL materialized views refresh fully; REFRESH MATERIALIZED VIEW CONCURRENTLY avoids blocking readers but needs a unique index, and incremental maintenance requires extensions such as pg_ivm.
- SQL Server indexed views and Oracle fast-refresh materialized views (using materialized view logs) maintain aggregates automatically, trading write overhead for instant reads.
- Generated columns are a safe, database-maintained form of denormalisation; PostgreSQL 18 made virtual (computed-on-read) generated columns the default, while STORED ones are computed on write and can be indexed.
Common mistakes & misconceptions
- Denormalising before measuring: many slow queries are fixed by one missing index, with no duplicate data to maintain.
- No reconciliation: without a periodic check, drifted counters and stale copies go unnoticed until a customer complains.
- Copying data that changes often: duplicating a frequently updated value multiplies write load and lock contention.
Try it: A trigger-maintained counter column (PostgreSQL)¶
ALTER TABLE product ADD COLUMN review_count int NOT NULL DEFAULT 0;
CREATE FUNCTION bump_review_count() RETURNS trigger AS $$
BEGIN
UPDATE product SET review_count = review_count + 1
WHERE product_id = NEW.product_id;
RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER review_ai AFTER INSERT ON review
FOR EACH ROW EXECUTE FUNCTION bump_review_count();
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| Denormalisation Database design practice · Concept · Current · Intermediate · General |
Intentionally adding redundancy or precomputed data to speed up reads, accepting extra write cost and consistency risk. | A news site stores author_name on each article row to avoid a join on its busiest page. |
| Duplicated column Database design practice · Pattern · Current · Intermediate · Relational |
Copying a column from a parent table into a child table to avoid a join in common queries. | order_line stores customer_id copied from orders so per-customer product reports skip one join. |
| Counter cache Rails counter_cache and similar ORMs · Pattern · Current · Intermediate · Relational |
A stored count of child rows kept on the parent, updated on every insert and delete of a child. | post.comment_count is incremented in the same transaction that inserts each comment. |
| Summary (aggregate) table Data warehousing practice · Pattern · Current · Intermediate · Analytical |
A table of pre-aggregated results, such as daily totals, refreshed on a schedule instead of computed per query. | daily_sales(store_id, day, revenue) has 44,000 rows instead of scanning 60 million receipt lines. |
| Materialized view refresh strategy PostgreSQL, Oracle, SQL Server, Snowflake · Concept · Current · Intermediate · Relational |
Choosing how stored query results stay current: full refresh, concurrent refresh, incremental or on-commit maintenance. | A dashboard's materialized view refreshes concurrently every 10 minutes, so viewers never wait on a lock. |
| Generated column as safe denormalisation SQL:2003 standard; PostgreSQL, MySQL, SQL Server · Feature · Current · Intermediate · Relational |
Letting the database itself maintain a derived value, so the redundant copy can never drift from its source columns. | Instead of an app-maintained total, total_pence GENERATED ALWAYS AS (qty * unit_pence) STORED is always correct and indexable. |
| Pre-joined reporting table Analytics practice · Pattern · Current · Intermediate · Analytical |
A wide table built by joining several normalised tables ahead of time for simple, fast reporting queries. | A nightly job flattens orders, customers and products into order_report for the BI tool. |
| Trigger-maintained copy Relational databases · Pattern · Current · Intermediate · Relational |
A trigger keeps a duplicated value or counter in sync inside the same transaction as the change. | An AFTER UPDATE trigger on customer updates customer_name in open_tickets whenever the name changes. |
| Array or JSON column for small lists PostgreSQL arrays/JSONB, MySQL JSON · Pattern · Current · Intermediate · Relational |
Storing a short, rarely queried list inside a row instead of a child table, trading purity for simplicity. | A product row keeps image_urls as a text[] of up to eight URLs, always read together. |
| Reconciliation job Data engineering practice · Pattern · Current · Intermediate · General |
A scheduled check that recomputes denormalised values from the source of truth and fixes or reports drift. | A 02:00 job finds 14 products whose review_count disagrees with COUNT(*) and corrects them. |
| Read/write ratio Workload analysis · Metric · Current · Beginner · General |
How many reads happen per write; high ratios favour denormalisation, write-heavy workloads favour normalisation. | A product catalogue read 2,000 times per edit is a strong candidate for precomputed fields. |
| Write amplification (design) Database design practice · Metric · Current · Intermediate · General |
Extra writes caused by maintaining copies, indexes or aggregates for each logical change. | Renaming a seller updates 1 row normalised but 250,000 listing rows in the denormalised design. |
| "Normalise until it hurts, denormalise until it works" Industry saying · Concept · Current · Beginner · General |
Rule of thumb: design normalised first, then denormalise only where measured performance demands it. | A team ships a 3NF schema, profiles production for a month, then adds two summary tables. |
3.8 Dimensional Modelling: Facts, Dimensions, Star and Snowflake Schemas¶
Dimensional modelling designs databases for analysis rather than for running the business. Measurements such as sales amounts go in a central fact table; the descriptive context — date, store, product, customer — goes in surrounding dimension tables. Drawn out, it looks like a star. It makes reports simple to write and fast to run.
Think of it like…
A fact table is a till receipt roll — long, narrow and full of numbers; dimensions are the reference books you look things up in: the calendar, the store directory and the product catalogue.
Real-world example
The fictional Sunrise Grocers has 120 stores and about 40 million receipt lines a year. Fact_sales has one row per receipt line with quantity, net amount and discount, plus keys to Dim_date, Dim_store, Dim_product, Dim_customer and Dim_promotion. Analysts ask "revenue by region by month for organic products" with simple joins and GROUP BY, and the columnar warehouse answers in under a second.
How it works¶
- Choose the business process to model, such as retail sales, shipments or call-centre calls.
- Declare the grain: exactly what one fact row represents (one receipt line, one daily balance).
- Identify the dimensions that describe each fact: who, what, where, when, how.
- Identify the numeric facts (measures) and whether each is additive, semi-additive or non-additive.
- Connect processes through conformed dimensions on an enterprise bus matrix so reports can combine them.
Who uses it
BI developers, analytics engineers, data warehouse architects and data analysts who write reporting SQL every day.
Key takeaway
Always state the grain first; most dimensional-modelling mistakes in interviews come from mixing facts at different grains in one table.
Going deeper
- Ralph Kimball and Margy Ross's The Data Warehouse Toolkit (3rd edition, 2013) defines the four-step process — business process, grain, dimensions, facts — and the vocabulary used across the industry.
- Fact tables are deep and narrow (billions of rows of keys and numbers); dimension tables are wide and shallow (dozens of descriptive columns, thousands to millions of rows).
- Dimensions use integer surrogate keys rather than source-system IDs, which insulates the warehouse from source changes and enables slowly changing dimension history.
- Semi-additive measures such as account balances or stock on hand can be summed across stores but not across time; use averages or period-end values over dates instead.
- Columnar warehouses compress repetitive dimension keys extremely well and optimisers recognise star joins, so star schemas remain efficient on Snowflake, BigQuery, Redshift and ClickHouse.
Common mistakes & misconceptions
- Mixing grains: putting order-level shipping cost on every order line inflates totals when summed; allocate it or use a separate fact table.
- Snowflaking by habit: normalising dimensions saves little space and forces analysts to write more joins for no real gain.
- Using source-system keys directly: when the source recycles or changes IDs, history in the warehouse silently breaks.
Try it: A typical star-schema query¶
SELECT d.year_month, s.region, SUM(f.net_amount) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_store s ON s.store_key = f.store_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE p.is_organic AND d.year = 2026
GROUP BY d.year_month, s.region
ORDER BY d.year_month, revenue DESC;
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| Fact table Kimball dimensional modelling · Concept · Current · Beginner · Analytical |
Central table of business measurements at a declared grain, holding foreign keys to dimensions plus numeric measures. | fact_sales has date_key, store_key, product_key, quantity and net_amount for each receipt line. |
| Dimension table Kimball dimensional modelling · Concept · Current · Beginner · Analytical |
A wide table of descriptive attributes that give context to facts and are used to filter and group. | dim_product has brand, category, pack_size and is_organic for 85,000 products. |
| Grain Kimball dimensional modelling · Concept · Current · Beginner · Analytical |
The precise meaning of one fact-table row; it must be declared before choosing dimensions or measures. | "One row per product per store per day" is a different grain from "one row per receipt line". |
| Additive, semi-additive, non-additive measures Kimball dimensional modelling · Concept · Current · Intermediate · Analytical |
Whether a measure can be summed across all dimensions, some dimensions, or none. | Sales amount is additive; bank balance is semi-additive over time; margin percentage is non-additive. |
| Star schema Kimball dimensional modelling · Data model · Current · Beginner · Analytical |
A fact table joined directly to denormalised dimension tables, forming a star shape that is simple to query. | Power BI and Tableau models are built fastest on star schemas with one join per dimension. |
| Snowflake schema Dimensional modelling · Data model · Current · Intermediate · Analytical |
A star whose dimensions are normalised into sub-tables, such as product → category → department. | dim_product links to dim_category, which links to dim_department; queries need two extra joins. |
| Transaction fact table Kimball dimensional modelling · Concept · Current · Intermediate · Analytical |
One row per business event at the lowest grain; the most common and flexible fact type. | Every card payment becomes one row in fact_payment with amount, merchant and timestamp keys. |
| Periodic snapshot fact table Kimball dimensional modelling · Concept · Current · Intermediate · Analytical |
One row per entity per regular period, recording status or balances at that point in time. | fact_inventory_daily stores stock on hand for each product in each store at midnight. |
| Accumulating snapshot fact table Kimball dimensional modelling · Concept · Current · Advanced · Analytical |
One row per process instance, updated as it passes milestones, with several date keys. | An order row gains packed_date, shipped_date and delivered_date as it moves through the warehouse. |
| Factless fact table Kimball dimensional modelling · Concept · Current · Advanced · Analytical |
A fact table with keys but no numeric measures, recording that an event or coverage happened. | fact_attendance records which student attended which class on which date; COUNT(*) gives attendance. |
| Conformed dimension Kimball dimensional modelling · Concept · Current · Intermediate · Analytical |
A dimension shared with identical keys and meanings across several fact tables, enabling drill-across reports. | The same dim_customer serves sales, returns and support-ticket facts, so churn analysis combines them. |
| Enterprise bus matrix Kimball Group · Tool · Current · Intermediate · Analytical |
A grid of business processes (rows) against conformed dimensions (columns) used to plan a warehouse incrementally. | The matrix shows Shipments and Sales both use Date, Product and Store, so they are built to conform. |
| Degenerate dimension Kimball dimensional modelling · Concept · Current · Advanced · Analytical |
A dimension key stored in the fact table with no dimension table, usually a transaction number. | receipt_number sits in fact_sales so analysts can group lines into baskets. |
| Junk dimension Kimball dimensional modelling · Concept · Current · Advanced · Analytical |
One small dimension combining miscellaneous low-cardinality flags, instead of many tiny dimensions. | dim_order_flags holds every combination of gift_wrap, express and click_and_collect — just 8 rows. |
| Role-playing dimension Kimball dimensional modelling · Concept · Current · Intermediate · Analytical |
One physical dimension used several times in a fact table under different roles, often via views. | dim_date appears as order_date, ship_date and delivery_date in fact_orders. |
| Date dimension Kimball dimensional modelling · Concept · Current · Beginner · Analytical |
A pre-built calendar table with one row per day and attributes like fiscal period and holidays. | A 20-year dim_date has about 7,300 rows and flags UK bank holidays for sales comparisons. |
3.9 Slowly Changing Dimensions and Keeping History¶
Descriptive data changes over time: customers move, products change category, staff change teams. Slowly changing dimension (SCD) types are standard recipes for what a warehouse does when that happens — overwrite, add a new versioned row, or keep the old value in a spare column. Operational databases have their own history tools, such as SQL:2011 temporal tables.
Grace moves from Lagos to Abuja
| Type 1 overwrite | Type 2 new row | Type 3 extra column | |
|---|---|---|---|
| After the move | city = Abuja | Old row closed, new row Abuja | city = Abuja, prev_city = Lagos |
| Rows for Grace | 1 | 2 | 1 |
| History kept | None | Full | One previous value |
| Old sales report as | Abuja (restated) | Lagos (as it was) | Either, by choice |
| Typical use | Typo fixes | Region, segment, tier | Planned reorganisations |
Only Type 2 keeps old sales attached to the old city automatically.
Think of it like…
SCD Type 1 is correcting a pencil entry with an eraser; Type 2 is a passport stamp book where each new stamp is added and the old ones stay visible; Type 3 is writing "previously: …" next to the current entry.
Real-world example
Customer 5512, Grace Okafor, moves from Lagos to Abuja on 2026-06-01. With SCD Type 2, the warehouse closes her old row (valid_to 2026-05-31, is_current false) and inserts a new row with a new surrogate key for Abuja. Sales before June still roll up to Lagos, and sales after it to Abuja, so regional reports stay historically correct.
How it works¶
- Decide per attribute whether history matters: a typo fix needs none, a change of region usually does.
- Type 1: overwrite in place; Type 2: expire the current row and insert a new version with a new surrogate key.
- Track validity with valid_from, valid_to and an is_current flag, and load facts with the key valid at the event date.
- Use MERGE or a tool such as dbt snapshots to detect changed source rows and apply the chosen type.
- In OLTP systems, use system-versioned temporal tables or audit tables to answer "what did this row look like on date X?"
Who uses it
Data warehouse developers, analytics engineers using dbt, auditors and anyone in regulated industries who must reproduce past reports.
Key takeaway
Be able to explain Types 0, 1, 2 and 3 in one sentence each, and know that Type 2 is the default for attributes used to group historical reports.
Going deeper
- Kimball's catalogue runs from Type 0 (never change) to Types 1, 2, 3, 4 (mini-dimension for fast-changing attributes), 5, 6 (1+2+3 hybrid) and 7; other authors use Type 4 for a separate history table, so define terms in design documents.
- SQL:2011 added temporal features: system-versioned tables record when rows were stored (transaction time), while application-time periods record when facts were true in the real world (valid time).
- SQL Server (2016), MariaDB (10.3) and Db2 support system-versioned tables queried with FOR SYSTEM_TIME AS OF; Oracle offers Flashback Query, and PostgreSQL relies on triggers or extensions.
- PostgreSQL 18 added temporal constraints: PRIMARY KEY and UNIQUE with WITHOUT OVERLAPS on a range column, and foreign keys with PERIOD, so overlapping validity periods can be rejected by the database.
- Bitemporal models store both valid time and transaction time, answering "what did we believe on 1 March about Grace's address on 1 February?" — essential for insurance, finance and regulatory reporting.
Common mistakes & misconceptions
- Joining facts to the current dimension row only: Type 2 needs the version valid at the event date, or history is silently restated.
- Using NULL as an open-ended valid_to: BETWEEN filters then miss current rows; many teams use 9999-12-31 instead.
- Tracking every column as Type 2: fast-changing attributes explode row counts; use Type 1 or a mini-dimension for them.
Try it: SCD Type 2 in two statements (PostgreSQL)¶
-- 1. expire the current version when the city changed
UPDATE dim_customer d SET valid_to = current_date - 1, is_current = false
FROM stg_customer s
WHERE d.customer_id = s.customer_id AND d.is_current AND d.city <> s.city;
-- 2. insert a new current version for new or changed customers
INSERT INTO dim_customer (customer_id, full_name, city, valid_from, valid_to, is_current)
SELECT s.customer_id, s.full_name, s.city, current_date, DATE '9999-12-31', true
FROM stg_customer s
LEFT JOIN dim_customer d ON d.customer_id = s.customer_id AND d.is_current
WHERE d.customer_id IS NULL;
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| SCD Type 0 Kimball dimensional modelling · Pattern · Current · Intermediate · Analytical |
The attribute never changes after first load; the original value is retained forever. | original_signup_channel stays 'Instagram ad' even if the customer later signs in through the website. |
| SCD Type 1 Kimball dimensional modelling · Pattern · Current · Beginner · Analytical |
Overwrite the old value; no history is kept and past reports are restated with the new value. | Fixing a misspelt product name 'Choclate' to 'Chocolate' updates the one dimension row in place. |
| SCD Type 2 Kimball dimensional modelling · Pattern · Current · Beginner · Analytical |
Add a new row with a new surrogate key for each change, preserving full history with validity dates. | A salesperson moving from North to South territory gets a second row; old deals stay credited to North. |
| SCD Type 3 Kimball dimensional modelling · Pattern · Current · Intermediate · Analytical |
Keep the current and one previous value in separate columns on the same row. | After a sales-region reorganisation, dim_store holds region_2026 and region_2025 for side-by-side comparisons. |
| SCD Type 4 (mini-dimension / history table) Kimball; other authors define it differently · Pattern · Current · Advanced · Analytical |
Moves rapidly changing attributes into a separate small dimension or history table to stop the main dimension exploding. | Customer age band and credit score band live in dim_customer_profile, keyed separately in the fact table. |
| SCD Type 6 (hybrid) Kimball dimensional modelling · Pattern · Current · Advanced · Analytical |
Combines Types 1, 2 and 3: versioned rows plus a current-value column overwritten on every version. | Each historical row of Grace carries both historic_city and current_city, so either view is one column away. |
| Effective dating (valid_from / valid_to) Data warehousing practice · Pattern · Current · Intermediate · Analytical |
Start and end dates on each version row so queries can find the version valid on any date. | WHERE sale_date BETWEEN valid_from AND valid_to picks the customer's segment at the time of sale. |
| Current-row flag Data warehousing practice · Pattern · Current · Beginner · Analytical |
A boolean such as is_current marking the latest version, making "as of now" queries simple. | Dashboards filter dim_customer WHERE is_current to count today's customers by tier. |
| Valid time vs transaction time Temporal database theory · Concept · Current · Advanced · General |
Valid time is when a fact was true in reality; transaction time is when the database recorded it. | An address change effective 1 May but entered on 9 May has valid_from 1 May and system time 9 May. |
| System-versioned temporal table SQL:2011; SQL Server, MariaDB, Db2 · Feature · Current · Intermediate · Relational |
A table where the database automatically keeps old row versions with system timestamps, queryable with FOR SYSTEM_TIME. | SELECT * FROM policy FOR SYSTEM_TIME AS OF '2026-01-01' shows policies as they were stored then. |
| Bitemporal model Temporal database theory · Data model · Current · Advanced · Relational |
Stores both valid time and transaction time, so you can reproduce past beliefs about past facts. | An insurer reruns a 2025 regulatory report exactly as it was filed, despite later backdated corrections. |
| dbt snapshots dbt Labs · Feature · Current · Intermediate · Analytical |
dbt feature that implements SCD Type 2 by comparing source rows on each run and recording dbt_valid_from and dbt_valid_to. | A snapshot on the CRM customers table captures every tier change, run hourly by the scheduler. |
| MERGE statement SQL:2003 standard · Command · Current · Intermediate · Relational |
One statement that inserts, updates or deletes target rows depending on whether they match a source. | MERGE INTO dim_product USING stg_product applies Type 1 updates and inserts new products in one pass. |
| Audit (history) table Relational databases · Pattern · Current · Intermediate · Relational |
A trigger copies each old row version, with who and when, into a separate append-only table. | customer_audit records every UPDATE with changed_by and changed_at for GDPR access requests. |
3.10 Kimball vs Inmon vs Data Vault¶
There are three classic ways to organise an enterprise data warehouse. Kimball builds business-friendly star schemas process by process. Inmon builds a central, normalised (3NF) enterprise warehouse first, then feeds data marts from it. Data Vault stores raw history in hubs, links and satellites that are easy to extend and audit, then publishes star schemas on top.
Think of it like…
Kimball builds a town one useful shop at a time with a shared street plan; Inmon designs the whole city grid before opening any shop; Data Vault builds a giant, well-labelled warehouse of every delivery ever received, then sets up shop fronts from it.
Real-world example
The fictional Meridian Bank merges three acquired lenders, each with different customer systems. It loads all three into a Data Vault: Hub_Customer holds business keys, Link_Customer_Account connects customers and accounts, and satellites keep every attribute change with load timestamps. Analysts never query the vault directly; dbt builds Kimball-style star schemas for lending and risk on top of it.
How it works¶
- Clarify the goal: fast business delivery (Kimball), a single integrated enterprise model (Inmon) or auditable, extensible raw history (Data Vault).
- Land source data in staging, keeping raw copies for reprocessing.
- Integrate: conform dimensions (Kimball), model the enterprise in 3NF (Inmon) or load hubs, links and satellites (Data Vault).
- Apply business rules in a separate layer (business vault or transformation models) so raw history stays untouched.
- Publish star schemas or wide tables for BI tools and analysts.
Who uses it
Data warehouse architects, analytics engineers, BI teams and consultants in banking, insurance, retail and government.
Key takeaway
A safe interview answer: most modern teams mix them — a raw or Data Vault integration layer for history, and Kimball star schemas for consumption.
Going deeper
- Bill Inmon's Building the Data Warehouse (1992) defined the warehouse as subject-oriented, integrated, time-variant and non-volatile; his Corporate Information Factory places a 3NF enterprise warehouse before departmental marts.
- Kimball's bus architecture delivers one business process at a time, with conformed dimensions guaranteeing that separately built stars still fit together into an enterprise view.
- Dan Linstedt released Data Vault in 2000; Data Vault 2.0, described in his 2015 book with Michael Olschimke, adds hash keys, a methodology and agile delivery guidance.
- Data Vault 2.0 hashes business keys (often MD5 or SHA-1, hex-encoded) so hubs, links and satellites can load in parallel without waiting for sequence lookups, and uses hash_diff columns to detect changed attributes.
- Point-in-time (PIT) tables record which satellite row was current for each key on each snapshot date, and bridge tables pre-join hubs and links, making vault queries far cheaper.
- Cloud warehouses and dbt packages such as AutomateDV and datavault4dbt have made Data Vault loading largely code-generated, while some teams skip it in favour of simpler wide tables.
Common mistakes & misconceptions
- Letting analysts query the raw vault: the many joins make it slow and confusing; always publish marts or views for consumption.
- Choosing Inmon's big up-front model for a small team: months pass before anyone gets a report, and sponsors lose interest.
- Treating the methods as rivals: hybrids are normal, and the right mix depends on source volatility, audit needs and team size.
Try it: Minimal Data Vault tables (generic SQL)¶
CREATE TABLE hub_customer (
customer_hk char(32) PRIMARY KEY, -- MD5 of business key
customer_bk text NOT NULL, -- business key from source
load_dts timestamp NOT NULL,
record_src text NOT NULL
);
CREATE TABLE sat_customer_details (
customer_hk char(32) REFERENCES hub_customer,
load_dts timestamp NOT NULL,
hash_diff char(32) NOT NULL, -- detects changed attributes
full_name text, city text,
record_src text NOT NULL,
PRIMARY KEY (customer_hk, load_dts)
);
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| Kimball approach Ralph Kimball / Kimball Group · Pattern · Current · Intermediate · Analytical |
Bottom-up warehousing: build dimensional star schemas per business process, tied together by conformed dimensions. | A retailer delivers a sales star in six weeks, then adds inventory reusing dim_product and dim_store. |
| Inmon approach Bill Inmon, Building the Data Warehouse (1992) · Pattern · Current · Intermediate · Analytical |
Top-down warehousing: design an integrated, normalised enterprise data warehouse first, then derive departmental marts. | A government agency models all citizen services in 3NF before building marts for each department. |
| Corporate Information Factory Bill Inmon · Pattern · Legacy · Advanced · Analytical |
Inmon's architecture of operational data stores, a central 3NF warehouse and dependent data marts. | Older telecom warehouses follow the CIF, with an ODS for near-real-time customer lookups. |
| Enterprise data warehouse (EDW) Data warehousing · Concept · Current · Beginner · Analytical |
A central, integrated store of historical data from across the organisation, used as the single source for reporting. | Meridian Bank's EDW combines lending, cards and deposits so risk reports use the same customer numbers. |
| Data mart Data warehousing · Concept · Current · Beginner · Analytical |
A subject-focused subset of warehouse data, usually a star schema, built for one department or process. | The marketing mart holds campaign, customer and response stars only. |
| Data Vault 2.0 Dan Linstedt; book with Michael Olschimke (2015) · Data model · Current · Advanced · Analytical |
A warehouse modelling method and methodology using hubs, links and satellites to store auditable, extensible history. | An insurer adds a newly acquired company's policy system by adding satellites, without changing existing tables. |
| Hub Data Vault · Concept · Current · Advanced · Analytical |
A table of unique business keys for a core business concept, with load date and record source. | hub_customer stores each customer number once, whichever of three source systems it came from. |
| Link Data Vault · Concept · Current · Advanced · Analytical |
A table recording a relationship between two or more hubs, always many-to-many to absorb business-rule changes. | link_customer_account connects customer and account hubs, allowing joint accounts without redesign. |
| Satellite Data Vault · Concept · Current · Advanced · Analytical |
A table of descriptive attributes and their full change history, attached to one hub or link. | sat_customer_crm keeps every name and address change with load timestamps from the CRM. |
| Hash key and hash diff Data Vault 2.0 · Pattern · Current · Advanced · Analytical |
Hashed business keys used as join keys, plus a hash of attribute values used to detect changes cheaply. | If the new row's hash_diff matches the latest satellite row, nothing changed and no row is inserted. |
| Raw vault vs business vault Data Vault 2.0 · Concept · Current · Advanced · Analytical |
The raw vault stores source data unchanged; the business vault adds derived data and business rules. | Customer lifetime value is computed in the business vault, never written back into raw satellites. |
| PIT and bridge tables Data Vault 2.0 · Pattern · Current · Advanced · Analytical |
Helper tables that pre-compute snapshot pointers (PIT) and pre-joined hub-link paths (bridge) for faster queries. | A daily PIT table lets a mart pick each customer's satellite rows as of each month end in one join. |
| One Big Table (OBT) Analytics engineering practice · Pattern · Current · Intermediate · Analytical |
A single very wide, fully denormalised table per use case, relying on columnar compression instead of joins. | A product team's events_wide table has 180 columns, so analysts never write a join. |
| AutomateDV and datavault4dbt Datavault-UK; Scalefree (open source dbt packages) · Library · Current · Advanced · Analytical |
dbt packages that generate Data Vault hub, link and satellite loading SQL from metadata. | A team declares 40 hubs in YAML and the package generates all incremental loading models. |
3.11 Modelling for NoSQL: Access Patterns First¶
Relational design starts from the data and lets queries be anything later. NoSQL design usually works the other way round: list every question the application will ask, then shape documents or keys so each question is answered by one fast lookup. You decide what to embed together, what to reference, and how to name keys so related items sit side by side.
Three ways to design the same app
| Relational | Document (MongoDB) | Key-value (DynamoDB) | |
|---|---|---|---|
| Design starts from | Entities and normal forms | Access patterns | Access patterns |
| Related data | Joined at query time | Embedded or referenced | Pre-grouped by key |
| Duplication | Avoided | Accepted where useful | Common |
| New query type | Usually just SQL | May need new index | May need new GSI or remodel |
| Size limit per unit | Row ~ page-based | 16 MB document | 400 KB item |
NoSQL trades query flexibility for predictable, single-lookup performance.
Think of it like…
A relational design is a library catalogue that can answer any question with some walking; a NoSQL design is a takeaway menu where every popular meal is already boxed, so it is handed over instantly — but a new dish means repacking the kitchen.
Real-world example
The fictional TrailMate hiking app lists its access patterns: get a user profile, list a user's recent hikes, show a hike with its photos, list hikes on a trail. In MongoDB it embeds up to 20 photo summaries inside each hike document and references the user by ID. In DynamoDB, a single table uses PK = USER#42 with SK = HIKE#2026-09-14 so one Query returns a user's hikes in date order.
How it works¶
- Write down every access pattern with its filters, sort order, expected volume and latency target.
- Group data that is read together: embed child data that is bounded and owned by one parent; reference data that is shared or unbounded.
- Design keys: choose partition keys with many distinct, evenly used values and sort keys that support range queries.
- Add secondary indexes (GSIs, MongoDB indexes) for the remaining access patterns, overloading generic key names where useful.
- Plan how duplicated data is updated and how documents will be versioned as the schema evolves.
Who uses it
Backend and mobile developers on MongoDB, DynamoDB, Firestore or Cassandra, and solution architects designing serverless systems.
Key takeaway
If asked "how do you design a DynamoDB table?", start with "list the access patterns first" — it is the single most important point.
Going deeper
- MongoDB documents are limited to 16 MB and DynamoDB items to 400 KB, so unbounded arrays (every comment ever) must be referenced or bucketed rather than embedded.
- A DynamoDB partition supports roughly 3,000 read units and 1,000 write units per second, so a key such as the current date that concentrates traffic creates a hot partition and throttling.
- DynamoDB allows up to 20 global secondary indexes per table by default (eventually consistent, any key) and up to 5 local secondary indexes (same partition key, defined at table creation).
- Single-table design was popularised by Rick Houlihan's AWS re:Invent talks and Alex DeBrie's The DynamoDB Book (2020); AWS guidance also notes that separate tables are fine when entities are never fetched together.
- Cassandra modelling is query-first too: one table per query, partition keys sized to stay well under about 100 MB, and clustering columns that define on-disk sort order; Chebotko diagrams document this design.
- MongoDB's published schema design patterns — bucket, extended reference, subset, computed, outlier, attribute, polymorphic and schema versioning — are reusable answers to recurring document-modelling problems.
Common mistakes & misconceptions
- Porting a normalised schema table-for-table into MongoDB or DynamoDB: you lose joins and gain nothing; redesign around access patterns.
- Embedding unbounded lists: documents grow until they hit size limits and every update rewrites a huge document.
- Choosing a low-cardinality partition key such as country or status: a few partitions take all the traffic and throttle.
Try it: Query a single-table design with the AWS CLI (works against DynamoDB Local)¶
aws dynamodb query \
--endpoint-url http://localhost:8000 \
--table-name TrailMate \
--key-condition-expression "PK = :pk AND begins_with(SK, :sk)" \
--expression-attribute-values '{":pk":{"S":"USER#42"},":sk":{"S":"HIKE#2026"}}' \
--no-scan-index-forward # newest hikes first
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| Access-pattern-first design NoSQL modelling practice · Concept · Current · Intermediate · General |
Listing every query with its keys, filters and sort order before designing documents, tables or keys. | TrailMate's 11 access patterns fit in a spreadsheet that drives every key and index decision. |
| Embedding (nested documents) Document modelling (MongoDB, Firestore) · Pattern · Current · Beginner · Document |
Storing related child data inside the parent document so one read returns everything needed together. | An order document embeds its line items and shipping address, read in one round trip. |
| Referencing Document modelling · Pattern · Current · Beginner · Document |
Storing an ID that points to another document, used for shared, large or independently changing data. | Each review stores product_id rather than embedding the whole product, which millions of reviews share. |
| One-to-few / many / squillions MongoDB schema design guidance (2014) · Concept · Current · Intermediate · Document |
Rule of thumb: embed for a few, reference children from the parent for many, reference the parent from children for squillions. | Addresses embed (few), product parts are referenced (many), log lines point to their host (squillions). |
| Bucket pattern MongoDB schema design patterns · Pattern · Current · Intermediate · Document |
Grouping many small related items, such as readings, into one document per time window. | A sensor writes one document per hour holding 3,600 readings, instead of 3,600 separate documents. |
| Extended reference pattern MongoDB schema design patterns · Pattern · Current · Intermediate · Document |
Copying a few frequently needed fields of a referenced document alongside its ID to avoid lookups. | Orders store customer_id plus customer name and city, enough for the order list page. |
| Schema versioning pattern MongoDB schema design patterns · Pattern · Current · Intermediate · Document |
Each document carries a schema_version field so old and new shapes can coexist during gradual migration. | Version 2 user documents split name into first and last; the app reads both versions and upgrades on write. |
| Single-table design Amazon DynamoDB practice · Pattern · Current · Advanced · Key-value |
Storing several entity types in one DynamoDB table with generic keys so related items are fetched in one Query. | Users, hikes and photos share TrailMate's table; PK USER#42 returns the user and all hikes together. |
| Partition key and sort key Amazon DynamoDB · Concept · Current · Intermediate · Key-value |
The partition key spreads items across storage; the sort key orders items within a partition for range queries. | PK = STORE#12 and SK = ORDER#2026-10-01#9918 lets one Query fetch a store's orders by date. |
| Global secondary index (GSI) Amazon DynamoDB · Feature · Current · Intermediate · Key-value |
An alternate partition and sort key maintained asynchronously, enabling access patterns the base key cannot serve. | GSI1 with GSI1PK = TRAIL#ben-nevis lists every hike on a trail, newest first. |
| Key overloading Amazon DynamoDB practice · Pattern · Current · Advanced · Key-value |
Using generic attribute names (PK, SK, GSI1PK) whose values carry prefixes for different entity types. | SK values like PROFILE, HIKE#2026-09-14 and FOLLOWER#77 live in the same sort-key column. |
| Adjacency list pattern Amazon DynamoDB practice · Pattern · Current · Advanced · Key-value |
Modelling many-to-many graphs by storing edge items under each node's partition key, often with an inverted GSI. | PK USER#42, SK GROUP#9 means user 42 belongs to group 9; the inverted index lists group members. |
| Query-first table design Apache Cassandra practice · Pattern · Current · Advanced · Wide-column |
Creating one denormalised table per query, with partition and clustering keys matching that query exactly. | hikes_by_trail and hikes_by_user hold the same data, each partitioned for its own query. |
| Hot partition Distributed NoSQL databases · Concept · Current · Intermediate · General |
A partition key value receiving far more traffic than others, overloading one storage node and causing throttling. | Using PK = DATE#2026-10-08 puts every write for today on one partition; adding a random suffix spreads it. |
| Document and item size limits MongoDB, DynamoDB, Firestore documentation · Concept · Current · Intermediate · General |
Hard caps per stored unit — 16 MB MongoDB document, 400 KB DynamoDB item, about 1 MiB Firestore document. | A chat app storing all messages in one conversation document hits MongoDB's 16 MB limit after months. |
3.12 Schema Evolution and Migrations¶
A database schema keeps changing as an application grows: new tables, new columns, renamed fields. A migration is a small, versioned script that makes one change, stored in Git next to the code. Migration tools record which scripts have run in each environment, so development, test and production databases all reach the same version in the same order.
Think of it like…
Migrations are like numbered renovation instructions for a house: every builder follows step 1, then 2, then 3, and a logbook on the wall shows which steps this house has already had.
Real-world example
The fictional PayDay payroll startup needs to split full_name into first_name and last_name on a 30-million-row table without downtime. It ships an expand migration adding nullable columns, deploys code that writes both, backfills in 10,000-row batches overnight, switches reads to the new columns, and a week later runs a contract migration that drops full_name. Flyway records each step in flyway_schema_history.
How it works¶
- Write each change as a new, numbered migration file; never edit one that has already run in a shared environment.
- Review migrations like code and run them automatically in CI against a fresh database.
- Apply them in order with a migration tool, which records the version, checksum and time in a history table.
- For large or busy tables, use expand-and-contract and online schema change tools to avoid long locks.
- Roll forward with a new corrective migration rather than relying on untested down scripts in production.
Who uses it
Backend developers, DevOps and platform engineers, DBAs and anyone deploying an application backed by a database.
Key takeaway
Know the expand-and-contract (parallel change) pattern and that in PostgreSQL you should use CREATE INDEX CONCURRENTLY and a lock_timeout on busy tables.
Going deeper
- In PostgreSQL most ALTER TABLE forms take an ACCESS EXCLUSIVE lock; even a fast change can queue behind a long query and block all traffic, so set lock_timeout (for example 3s) and retry.
- PostgreSQL 11+ adds a column with a constant default without rewriting the table, and NOT NULL can be added safely via a CHECK ... NOT VALID constraint validated later; MySQL 8.0 supports ALGORITHM=INSTANT for many column additions.
- Flyway stores applied versions and checksums in flyway_schema_history, Liquibase uses DATABASECHANGELOG plus a DATABASECHANGELOGLOCK table, and Prisma uses _prisma_migrations, so tools detect edited or missing scripts.
- Liquibase 5.0 (September 2025) moved its Community edition from Apache 2.0 to the source-available Functional Source License, with each release reverting to Apache 2.0 after two years; Flyway Community remains open source under Redgate.
- Declarative tools such as Atlas compare a desired schema with the live database and generate the migration plan, while imperative tools such as Flyway run the scripts you write.
- MySQL online schema change tools copy the table in the background — gh-ost by reading the binary log, pt-online-schema-change with triggers — then swap it in with a brief lock.
Common mistakes & misconceptions
- Editing an already-applied migration: checksums break and environments silently diverge; always add a new migration instead.
- Renaming or dropping a column in one step: running application instances still use the old name and start failing mid-deploy.
- Backfilling millions of rows in one transaction: it locks rows, bloats the WAL and replication lag; batch it in small chunks.
Try it: Versioned migrations with Flyway and Alembic¶
# Flyway: files in db/migration are applied in version order
# V1__create_employee.sql
# V2__add_first_last_name.sql
# R__refresh_reporting_views.sql (repeatable, reruns when changed)
flyway -url=jdbc:postgresql://localhost/payday -user=dev info
flyway -url=jdbc:postgresql://localhost/payday -user=dev migrate
# Alembic (Python / SQLAlchemy)
alembic revision -m "add first and last name"
alembic upgrade head
alembic history --verbose
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| Database migration Software engineering practice · Concept · Current · Beginner · General |
A versioned, ordered script that changes the schema or data from one version to the next, stored in source control. | V7__add_invoice_due_date.sql adds a column and is applied to test and production by the pipeline. |
| Schema history table Migration tool feature · Concept · Current · Beginner · Relational |
A table the migration tool maintains listing which migrations ran, when, with what checksum and outcome. | flyway_schema_history shows production is at version 41 while staging is already at 43. |
| Flyway Redgate (open-source Community edition) · Tool · Current · Beginner · Relational |
Popular migration tool using plain SQL or Java migrations named by version, such as V1__init.sql, run in order. | A Spring Boot service runs Flyway at startup, applying any new migrations before accepting traffic. |
| Liquibase Liquibase Inc. (Community under FSL since 5.0) · Tool · Current · Intermediate · Relational |
Migration tool using changelogs of changesets in SQL, XML, YAML or JSON, with rollback and diff features. | A bank's changelog.yaml includes changesets tagged per release, with explicit rollback blocks reviewed by DBAs. |
| Alembic SQLAlchemy project (Mike Bayer) · Tool · Current · Intermediate · Relational |
Python migration tool for SQLAlchemy that can autogenerate migration scripts by comparing models to the database. | alembic revision --autogenerate creates a script after a developer adds a column to a SQLAlchemy model. |
| Prisma Migrate Prisma Data · Tool · Current · Intermediate · Relational |
Migration tool for the Prisma ORM that generates SQL migrations from changes in schema.prisma. | prisma migrate dev creates and applies a migration locally; prisma migrate deploy applies it in production. |
| Django migrations Django Software Foundation · Framework · Current · Beginner · Relational |
Django's built-in system that generates migration files from model changes and applies them with manage.py migrate. | python manage.py makemigrations creates 0012_order_due_date.py after editing the Order model. |
| Rails Active Record migrations Ruby on Rails · Framework · Current · Beginner · Relational |
Ruby DSL classes with timestamps that change the schema and update db/schema.rb. | bin/rails generate migration AddDueDateToInvoices due_date:date creates a timestamped migration file. |
| EF Core migrations Microsoft (.NET) · Framework · Current · Intermediate · Relational |
Entity Framework Core's migration system that scaffolds C# migration classes from model changes. | dotnet ef migrations add AddDueDate then dotnet ef database update applies it to SQL Server. |
| Atlas Ariga · Tool · Emerging · Intermediate · Relational |
Schema-as-code tool that compares a declared desired schema with a database and plans the migration automatically. | A team declares tables in HCL; atlas schema apply shows the ALTER statements before running them. |
| Sqitch Open-source project (David Wheeler) · Tool · Current · Advanced · Relational |
Database-native change management using deploy, revert and verify scripts with explicit dependencies, not version numbers. | A PostgreSQL team declares that the add_index change requires the create_table change in sqitch.plan. |
| Expand-and-contract (parallel change) Continuous delivery practice · Pattern · Current · Intermediate · General |
Make schema changes in backward-compatible steps — add, migrate, switch, remove — so old and new code coexist. | Renaming email to email_address took four deploys over a week with no downtime. |
| gh-ost GitHub (open source) · Tool · Current · Advanced · Relational |
Triggerless online schema migration tool for MySQL that copies the table and replays changes from the binary log. | GitHub-style ALTERs on a 500 GB MySQL table run for hours while the site stays fully online. |
| pt-online-schema-change Percona Toolkit · Tool · Current · Advanced · Relational |
Online ALTER tool for MySQL that copies the table in chunks and uses triggers to capture concurrent changes. | A DBA adds an index to a 200-million-row orders table without locking writes. |
| pgroll Xata (open source) · Tool · Emerging · Advanced · Relational |
Zero-downtime PostgreSQL migration tool that automates expand-and-contract, serving old and new schema versions simultaneously. | pgroll exposes the table through versioned views so old and new app versions both work during rollout. |
3.13 Naming Conventions and Design Anti-Patterns¶
Consistent names make a schema readable for years, and some designs that look clever early on become painful later. Anti-patterns such as Entity-Attribute-Value tables, polymorphic foreign keys or comma-separated lists stop the database from enforcing rules and make queries slow and complex. Each has a well-known better alternative.
Common anti-patterns and their fixes
| Why it hurts | Better design | |
|---|---|---|
| Entity-Attribute-Value | No types, no constraints, many self-joins | Typed columns, plus JSONB for rare extras |
| Polymorphic association | No real foreign key possible | Exclusive arc or shared supertype table |
| Comma-separated list | Cannot index, join or validate | Junction table |
| tag1, tag2, tag3 columns | Fixed limit, OR-heavy queries | Child table, one row per tag |
| One true lookup table | No FK per code type | One lookup table per domain |
| FLOAT for money | Rounding errors | NUMERIC or integer pence |
| Table per year | Unions and duplicated DDL | One table, partitioned by date |
Each fix lets the database enforce types, keys and constraints again.
Think of it like…
A schema is a shared kitchen: if everyone labels jars the same way and keeps one ingredient per jar, anyone can cook; if jars are labelled "misc" and hold three spices mixed together, every recipe becomes a puzzle.
Real-world example
The fictional CraftBase marketplace stored product details in an EAV table: (product_id, attribute, value) with everything as text. Finding red cotton shirts under £20 needed four self-joins and casts, taking 9 seconds. Moving common attributes into typed columns and rare ones into a JSONB column with a GIN index cut it to 40 ms, and CHECK constraints now stop prices like 'twenty'.
How it works¶
- Agree a naming standard: lowercase snake_case, consistent singular or plural tables, id or table_id keys, and readable constraint names.
- Avoid reserved words and quoted mixed-case identifiers so names work in every tool and dialect.
- Review designs against known anti-patterns: EAV, polymorphic associations, delimited lists, repeated columns, one-true-lookup tables.
- Replace them with typed columns, proper junction tables, exclusive-arc foreign keys or JSONB with validation.
- Enforce standards with linters and code review, and record conventions in the repository.
Who uses it
Backend developers, DBAs and data modellers doing design reviews; these patterns come up constantly in code reviews and interviews.
Key takeaway
Bill Karwin's SQL Antipatterns is the classic reference; for interviews, be able to explain why EAV and polymorphic associations break foreign keys and type checking.
Going deeper
- Bill Karwin's SQL Antipatterns (Pragmatic Bookshelf, 2010; revised as Volume 1 in 2022) names many of these, including Jaywalking (delimited lists), Multicolumn Attributes, Metadata Tribbles and Polymorphic Associations.
- Unquoted identifiers fold to lower case in PostgreSQL and to upper case in Oracle, so a table created as "OrderItems" in quotes must be quoted forever; lowercase snake_case avoids this entirely.
- Trees in SQL have four classic designs: adjacency list (parent_id, queried with recursive CTEs), path enumeration (PostgreSQL ltree), nested sets (fast reads, slow writes) and closure tables (all ancestor-descendant pairs).
- Soft deletes (deleted_at columns) keep data recoverable but every query, unique constraint and foreign key must account for them; partial unique indexes such as UNIQUE (email) WHERE deleted_at IS NULL help.
- Multi-tenant SaaS schemas choose between a shared schema with tenant_id on every row (often with row-level security), a schema per tenant, or a database per tenant, trading cost against isolation.
Common mistakes & misconceptions
- Choosing EAV for "flexibility": you lose types, constraints and performance; JSONB plus a few typed columns is usually the better flexible design.
- Mixing naming styles across tables: CustomerID, customer_id and custId in one schema guarantees bugs in joins and ORMs.
- Assuming a soft-delete flag is free: forgotten WHERE deleted_at IS NULL filters leak deleted data into reports and APIs.
Try it: Replace a polymorphic association with an exclusive arc (PostgreSQL)¶
CREATE TABLE comment (
comment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
photo_id bigint REFERENCES photo,
article_id bigint REFERENCES article,
body text NOT NULL,
-- exactly one parent must be set
CONSTRAINT comment_one_parent_ck CHECK (num_nonnulls(photo_id, article_id) = 1)
);
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| snake_case naming Common SQL convention · Concept · Current · Beginner · Relational |
Lowercase words joined by underscores for tables and columns, avoiding quoting and case-folding problems across databases. | order_line.unit_price works unquoted in PostgreSQL, MySQL, SQL Server and Oracle. |
| Singular vs plural table names Naming convention debate · Concept · Current · Beginner · Relational |
Teams pick singular (customer) or plural (customers) names; consistency matters far more than the choice itself. | Rails defaults to plural tables, while many data modellers prefer singular entity names like customer. |
| Key and constraint naming Naming convention · Concept · Current · Beginner · Relational |
Predictable names such as customer_id for keys and pk_, fk_, uq_, ck_ or ix_ prefixes or suffixes for constraints and indexes. | An error mentioning fk_order_customer immediately tells the developer which relationship failed. |
| Reserved words and quoted identifiers SQL standard and dialects · Concept · Current · Beginner · Relational |
Names like order, user or group clash with SQL keywords and need quoting, which differs by dialect. | A table named user must be written "user" in PostgreSQL and [user] in SQL Server; app_user avoids it. |
| Entity-Attribute-Value (EAV) Anti-pattern (Karwin, SQL Antipatterns) · Pattern · Legacy · Intermediate · Relational |
Storing data as (entity, attribute name, value) rows, losing types, constraints and simple queries. | Finding products where colour = red AND size = M needs two self-joins on product_attribute. |
| Polymorphic association Anti-pattern (Karwin, SQL Antipatterns) · Pattern · Current · Intermediate · Relational |
A pair of columns (parent_type, parent_id) pointing at different tables, which no foreign key can enforce. | comments.commentable_type = 'Photo' with commentable_id = 99 can point at a photo that no longer exists. |
| Jaywalking (delimited lists) Anti-pattern (Karwin, SQL Antipatterns) · Pattern · Current · Beginner · Relational |
Storing several IDs or values in one comma-separated string column. | tags = 'red,sale,cotton' cannot be indexed or joined; a product_tag table fixes it. |
| Multicolumn attributes Anti-pattern (Karwin, SQL Antipatterns) · Pattern · Current · Beginner · Relational |
Numbered columns like phone1, phone2, phone3 for a repeating attribute, with a fixed limit and awkward queries. | Searching any phone number needs WHERE phone1 = ? OR phone2 = ? OR phone3 = ?. |
| One true lookup table (OTLT) Anti-pattern · Pattern · Current · Intermediate · Relational |
A single generic table holding every code type, so foreign keys cannot restrict a column to the right kind of code. | order.status can accidentally reference a 'country' code because both live in lookup_code. |
| Metadata tribbles (table per period) Anti-pattern (Karwin, SQL Antipatterns) · Pattern · Current · Intermediate · Relational |
Splitting one entity into many tables by year, customer or region, multiplying DDL and unions. | sales_2024, sales_2025, sales_2026 should be one sales table partitioned by sale_date. |
| FLOAT for money Anti-pattern · Pattern · Current · Beginner · Relational |
Using binary floating point for currency, which cannot represent most decimal fractions exactly. | 0.1 + 0.2 in a FLOAT column gives 0.30000000000000004; NUMERIC(12,2) gives exactly 0.30. |
| Closure table Relational tree modelling · Pattern · Current · Advanced · Relational |
Storing every ancestor-descendant pair of a hierarchy in a separate table for fast subtree queries. | A category tree of 5,000 nodes gets category_path(ancestor, descendant, depth) to list all subcategories instantly. |
| Soft delete Application design pattern · Pattern · Current · Intermediate · Relational |
Marking rows as deleted with a flag or timestamp instead of removing them, keeping data recoverable. | Users with deleted_at set disappear from the app but can be restored within 30 days. |
| Multi-tenant schema patterns SaaS architecture · Pattern · Current · Intermediate · Relational |
Shared tables with tenant_id, schema per tenant, or database per tenant — trading cost, isolation and operational effort. | A SaaS with 8,000 small customers uses tenant_id plus row-level security; its biggest client gets a dedicated database. |
| SQL Antipatterns (book) Bill Karwin, Pragmatic Bookshelf · Resource · Current · Intermediate · Relational |
Classic book cataloguing common SQL design and query mistakes, each with the recommended solution. | A team uses its chapters as a checklist during schema design reviews. |
3.14 Choosing Data Types Well¶
Every column has a data type that decides what values it can hold, how much space it takes and how it sorts and compares. Good choices catch bad data early, save storage and keep calculations correct. Poor choices — text for dates, floats for money, 32-bit integers for fast-growing IDs — cause subtle bugs that are expensive to fix later.
Think of it like…
Data types are like containers in a kitchen: an egg box for eggs, a measuring jug for liquid; putting soup in an egg box technically works for a moment, then makes a mess.
Real-world example
The fictional FlyEasy travel site stored booking times as text like '03/04/2026 14:00'. Half the rows were US-format, half UK, and sorting was alphabetical. Converting to timestamptz (stored in UTC) fixed sorting and time-zone display; prices moved from float to numeric(10,2), ending a £0.01 mismatch in daily revenue reports.
How it works¶
- Pick the most specific type that fits the meaning: date for dates, boolean for yes/no, numeric for money.
- Size integer columns for growth: bigint for IDs and counters on anything that might pass 2 billion rows.
- Store moments in time as timestamp with time zone (UTC), and use date when time of day does not matter.
- Use text with CHECK constraints or lookup tables rather than oversized varchar limits or fragile enums.
- Choose the right character set and collation (UTF-8, case and accent rules) before loading data.
Who uses it
Everyone who writes CREATE TABLE: backend developers, DBAs, data engineers and analysts loading data into warehouses.
Key takeaway
Interview favourites: never use float for money, use timestamptz for events, use bigint for IDs, and in MySQL use utf8mb4 rather than utf8.
Going deeper
- PostgreSQL's timestamptz stores an 8-byte UTC instant and converts to the session's TimeZone for display; plain timestamp stores wall-clock time with no zone, which is ambiguous across daylight-saving changes.
- MySQL's TIMESTAMP is a 32-bit Unix time ending 2038-01-19 03:14:07 UTC (the Year 2038 problem), while DATETIME reaches 9999-12-31; MySQL's utf8 alias means 3-byte utf8mb3, which cannot store emoji — use utf8mb4, the default since 8.0.
- IEEE 754 doubles hold about 15-17 significant decimal digits but cannot represent 0.1 exactly; NUMERIC/DECIMAL stores exact decimal digits, and many payment systems store integer minor units (pence, cents).
- In PostgreSQL varchar(n) and text perform the same; the limit is only a constraint. SQL Server distinguishes varchar from Unicode nvarchar (UTF-16) and has supported UTF-8 collations since SQL Server 2019.
- Column order affects row size through alignment padding: PostgreSQL aligns 8-byte types on 8-byte boundaries, so placing bigint and timestamptz columns before smaller ones can save several bytes per row.
- SQLite uses flexible type affinity and accepts almost any value in any column unless you declare a STRICT table (available since 3.37.0, 2021); Oracle Database 23ai finally added a native SQL BOOLEAN type.
Common mistakes & misconceptions
- Storing dates as text: sorting, range filters and validation all break; use date or timestamptz and format only for display.
- Using 32-bit int for IDs on growing tables: hitting 2,147,483,647 forces an emergency column rewrite under pressure.
- Using timestamp without time zone for events from many regions: daylight-saving shifts and offsets corrupt ordering and reports.
Try it: See type behaviour and sizes in PostgreSQL¶
SELECT 0.1::float8 + 0.2::float8 AS float_sum, -- 0.30000000000000004
0.1::numeric + 0.2::numeric AS numeric_sum, -- 0.3
pg_column_size(1::int) AS int_bytes, -- 4
pg_column_size(1::bigint) AS bigint_bytes, -- 8
pg_column_size(now()) AS ts_bytes, -- 8
pg_column_size(gen_random_uuid()) AS uuid_bytes, -- 16
now() AT TIME ZONE 'Asia/Tokyo' AS tokyo_time;
Key databases, concepts, languages, tools & standards¶
| Name | What it does | Example |
|---|---|---|
| INTEGER and BIGINT SQL standard · Concept · Current · Beginner · Relational |
Whole-number types: 4-byte INTEGER up to about 2.1 billion, 8-byte BIGINT up to about 9.2 quintillion. | An events table receiving 50 million rows a day uses a bigint ID; int would overflow in about six weeks. |
| NUMERIC / DECIMAL SQL standard · Concept · Current · Beginner · Relational |
Exact decimal numbers with declared precision and scale, the right choice for money and quantities needing exact arithmetic. | price numeric(10,2) stores 12345678.99 exactly and sums invoices without rounding drift. |
| FLOAT / REAL / DOUBLE PRECISION SQL standard; IEEE 754 · Concept · Current · Beginner · Relational |
Approximate binary floating-point numbers, fast and compact for measurements but inexact for decimal fractions. | Sensor temperatures like 21.374 °C suit double precision; bank balances do not. |
| VARCHAR vs TEXT SQL standard and dialects · Concept · Current · Beginner · Relational |
Variable-length strings; varchar(n) adds a maximum length, while text in PostgreSQL is unlimited with the same performance. | A PostgreSQL team uses text with CHECK (length(code) = 3) instead of guessing varchar(255) everywhere. |
| CHAR(n) SQL standard · Concept · Current · Beginner · Relational |
Fixed-length strings padded with spaces; rarely useful except for truly fixed codes. | char(2) for ISO country codes like 'GB' is reasonable; char(50) for names wastes space and pads values. |
| Character set and collation Unicode; database dialects · Concept · Current · Intermediate · Relational |
Encoding decides which characters can be stored; collation decides sorting and whether comparisons ignore case and accents. | MySQL utf8mb4_0900_ai_ci makes 'José' equal 'jose' in searches; a binary collation would not. |
| utf8mb4 (MySQL) MySQL / MariaDB · Format · Current · Intermediate · Relational |
MySQL's full 4-byte UTF-8 encoding; the older utf8 alias (utf8mb3) cannot store emoji and many CJK characters. | Customer reviews containing 👍 fail to insert into a utf8mb3 column but work after converting to utf8mb4. |
| BOOLEAN SQL standard · Concept · Current · Beginner · Relational |
True/false (and NULL) values; supported natively in PostgreSQL and MySQL (as TINYINT(1)), and in Oracle since 23ai. | is_active boolean NOT NULL DEFAULT true is clearer than a char(1) holding 'Y' or 'N'. |
| DATE, TIME and TIMESTAMP SQL standard · Concept · Current · Beginner · Relational |
Calendar dates, times of day and combined date-times without zone information. | birth_date date suits a passport field, since time of day and zone are irrelevant. |
| TIMESTAMP WITH TIME ZONE SQL standard; PostgreSQL timestamptz · Concept · Current · Intermediate · Relational |
An absolute instant, stored normalised (UTC in PostgreSQL) and shown in each session's time zone. | A flight booked in Sydney and viewed in London shows the correct local time in both places. |
| INTERVAL SQL standard · Concept · Current · Intermediate · Relational |
A span of time such as 3 days or 2 hours 15 minutes, usable in date arithmetic. | WHERE created_at > now() - interval '7 days' finds last week's sign-ups. |
| UUID type PostgreSQL uuid, SQL Server uniqueidentifier, MySQL BINARY(16) · Concept · Current · Intermediate · Relational |
A native 16-byte identifier type that stores UUIDs compactly and compares them efficiently. | Converting a char(36) UUID column to uuid shrank a 1-billion-row index by roughly half. |
| JSON and JSONB SQL:2016 JSON; PostgreSQL, MySQL · Concept · Current · Intermediate · Relational |
Types for semi-structured data; PostgreSQL's JSONB stores a parsed binary form that can be indexed with GIN. | A product's optional specs live in a JSONB column queried with specs @> '{"material":"cotton"}'. |
| ENUM vs lookup table PostgreSQL/MySQL ENUM; relational design · Concept · Current · Intermediate · Relational |
ENUM types restrict a column to fixed labels; lookup tables do the same but are easier to extend and describe. | order_status as an ENUM needs ALTER TYPE to add 'returned'; a lookup table just needs an INSERT. |
| BLOB / BYTEA SQL standard; PostgreSQL bytea · Concept · Current · Intermediate · Relational |
Binary data types for raw bytes; large files are usually better kept in object storage with a URL in the database. | Thumbnails under 50 KB sit in bytea; 20 MB videos go to S3 with only their keys stored. |
| Range and array types PostgreSQL · Concept · Current · Advanced · Relational |
PostgreSQL types holding ranges (daterange, tstzrange) or arrays, with operators for overlap and containment. | A booking's stay daterange plus an exclusion constraint prevents two guests booking the same room on overlapping nights. |