Table of Contents

Purchase: Database Design Book.

1. Introduction

• 1.1 Who is this book for?
• 1.2 What level of understanding is this book aimed at?
• 1.3 What you would get from reading this book
• 1.4 Who I am

2. Building a logical model

• 2.1 System requirements as input
• 2.2 Who do we write the logical model for?
  • 2.2.1 For yourself
  • 2.2.2 For software developers
  • 2.2.3 For the project group
• 2.3 Can I skip the logical model?
• 2.4 Can I use an ERD instead?
• 2.5 Elements of a logical model
• 2.6 The process
• 2.7 Anchors: introduction
  • 2.7.1 Example: posts
  • 2.7.2 A list of anchors
  • 2.7.3 Example: invoices
  • 2.7.4 Anchor IDs
• 2.8 Attributes: introduction
  • 2.8.1 Attributes: definition
  • 2.8.2 Attributes and anchors
  • 2.8.3 Human-readable questions
  • 2.8.4 Example values
  • 2.8.5 Data types and types of data
  • 2.8.6 What is not an attribute?
  • 2.8.7 How to confirm that all the attributes have been listed?
• 2.9 Links: introduction
  • 2.9.1 Links: definition
  • 2.9.2 Links: pair of anchors
  • 2.9.3 Links: cardinality
  • 2.9.4 Cardinality is a business concern
  • 2.9.5 Links: sentences
  • 2.9.6 False links and unique pairs of IDs
• 2.10 More on anchors
  • 2.10.1 Unique attributes
  • 2.10.2 Optional unique attributes
  • 2.10.3 External IDs
  • 2.10.4 External ID enumeration
  • 2.10.5 Several unique IDs
  • 2.10.6 Implementing physical IDs
• 2.11 Handling time in the logical model

3. “Hello world” use case: podcast catalog

• 3.1 Business requirements
• 3.2 Anchors
• 3.3 Attributes
• 3.4 Links
• 3.5 Cross-checking the requirements
• 3.6 A diagram
• 3.7 Are we there yet?
• 3.8 Evolving the system

4. Building a physical schema

• 4.1 Many table design strategies are possible

5. Table-per-anchor table design strategy

• 5.1 Action plan
• 5.2 Anchors: choosing table names
• 5.3 Attributes: choosing column names
• 5.4 Attributes: choose column data types
  • 5.4.1 Recommended physical types for SQL databases: summary
  • 5.4.2 Strings
  • 5.4.3 Integer numbers
  • 5.4.4 Monetary amounts
  • 5.4.5 Numeric values
  • 5.4.6 Yes/no values
  • 5.4.7 Either/or/or values
  • 5.4.8 Dates
  • 5.4.9 Date with time in UTC timezone
  • 5.4.10 Date with time in a specific timezone
  • 5.4.11 Timezone names
  • 5.4.12 Binary blobs
• 5.5 Links
  • 5.5.1 One-to-many (1:M) links
  • 5.5.2 An ID cannot be an attribute value
  • 5.5.3 Many-to-many (M:N) links
• 5.6 Podcast catalog: a complete physical schema
  • 5.6.1 CREATE TABLE statements
  • 5.6.2 Audio file and cover images
• 5.7 Physical ID design
  • 5.7.1 Case study: a tiny CMS
  • 5.7.2 The maximum number of items
  • 5.7.3 Reaching the maximum number of items
  • 5.7.4 Space taken by IDs
  • 5.7.5 Disk space is time
  • 5.7.6 Storage density
  • 5.7.7 UUIDs as anchor IDs
  • 5.7.8 Countries, currencies, languages: well-known anchors
  • 5.7.9 Countries, currencies, and languages in your business
• 5.8 Handling time in the physical model

6. Other table design strategies

• 6.1 Table design concerns
• 6.2 Is there a recommended table strategy?
• 6.3 Table-per-anchor, revisited
• 6.4 Side tables
  • 6.4.1 Naming and composition of side tables
  • 6.4.2 A stopgap table
• 6.5 JSON columns

7. Dealing with absent data

• 7.1 Use case: user’s bio
• 7.2 Sentinel values: NULL
• 7.3 Other sentinel values
• 7.4 Sentinel values exist only on a physical level
• 7.5 Explicit reasons for missing data
• 7.6 Summary

8. Secondary data

• 8.1 Cached column example
• 8.2 There is no free lunch
• 8.3 Cached column is not an attribute
• 8.4 Discovering secondary data

9. Evolving your database

• 9.1 Elementary database migrations
• 9.2 Table rewrite
• 9.3 Adding an attribute
  • 9.3.1 Step 1: Update logical model
  • 9.3.2 Step 2. Run database migration
  • 9.3.3 Step 3. Update code
• 9.4 Dealing with table rewrite

10. Movie tickets: repeated sales pattern

• 10.1 Business requirements
• 10.2 Per-department modeling
• 10.3 Movies department
• 10.4 Maintenance department
• 10.5 Movie schedule department
• 10.6 Tickets department
• 10.7 A diagram
• 10.8 Conclusion

11. Books and washing machines: polymorphic data pattern

• 11.1 Business requirements
• 11.2 Per-department modeling
• 11.3 Key insight: generic anchor vs specific anchors
• 11.4 Multiplexing on item type
• 11.5 Tangled links
• 11.6 A diagram
• 11.7 Table-per-anchor approach
• 11.8 Polymorphic table design strategy
  • 11.8.1 JSON-based columns
  • 11.8.2 Physical schema
  • 11.8.3 Storing links in JSON
  • 11.8.4 Table design concerns, revisited
  • 11.8.5 Documenting physical storage

12. Practicalities

• 12.1 Document-based catalog
• 12.2 Spreadsheet-based catalog
• 12.3 How much to write
• 12.4 Lightweight designs

---

“Database Design Book” (2025)

A common problem in business-oriented database design is how to store the history of data as it changes. Tracking the changes of attribute values is needed more often, and is widely explained, see for example my previous post “Historized attributes: systematic table design”.

In this post we’ll discuss a slightly more complex scenario: historized relationships. First we discuss the M:N case. 1:N is very similar logically, but requires a somewhat different physical approach.

A common problem in business-oriented database design is how to store the history of data as it changes. Tracking changes of attribute values is common, and is widely explained, see for example my previous post “Historized attributes: systematic table design”.

Let’s discuss a somewhat more complex scenario: historized relationships. An example of a historized relationship is somebody assigned to a project for a period of time, then assigned away, and reassigned again later. In some use cases we want to know the entire history: for example, for billing purposes.

Author: Alexey Makhotkin squadette@gmail.com, (~3900 words)

One of the goals of this publication is to deconstruct the traditional ways of teaching basic topics in relational databases. Previously we discussed the fourth normal form (4NF): “Historically, 4NF explanations are needlessly confusing”.

Let’s discuss the ultimate beast: fifth normal form (5NF). Often it’s presented even more confusingly than 4NF is, and we can show that this presentation is unnecessary, and the confusion is completely artificial.

Author: Alexey Makhotkin squadette@gmail.com, (~8800 words)

There are many SQL JOINs guides and tutorials, but this one takes a very different approach. Specifically:

• LEFT JOIN is presented first, INNER JOIN second;

• strict discipline of using ID equality comparison in ON condition;

• we distinguish between N:1, 1:N and M:N cases of JOINs, with N:1 strictly preferred;

• we avoid misleading wording and imagery;

• we show a detailed explanation of overcounting in GROUP BY queries;

Author: Alexey Makhotkin squadette@gmail.com.

In the previous post we looked at the logical ERD diagram and tried to investigate if it is a good system specification tool.

We can also contrast ERD diagram and the tabular format proposed by the Minimal Modeling approach.

State park database: a Minimal Modeling approach (pt. III-b)

Here is my attempt to reproduce the above ERD diagram in a tabular format presented in the “Database Design Book”. Some parts are unclear to me and marked with “(?)”; unfortunately, the original system is also hypothetical, so there are no stakeholders who could tell us what’s the right way. ChatGPT output was not used here, so any mistakes are mine.

Author: Alexey Makhotkin squadette@gmail.com.

In Part 1 we’ve learned how to design logical ERD diagrams based on the logical model. We used the Minimal Modeling notation for the logical model, and the ERD diagram was derived from that. So, the logical model was a specification, and the ERD diagram was just an illustration.

Let’s investigate what happens if we try to use the ERD diagram directly as a specification. What sort of information we could extract from it, and what information tends to be missing or incomplete. Also, we’ll talk about collaboration: what if you need to make changes and share them with other people.

Author: Alexey Makhotkin squadette@gmail.com.

In the first part we’ve designed a logical ERD diagram based on the structured logical model. We built the structured logical model from the free-text business requirements.

What if we need to draw a physical ERD diagram for the same task? It turns out that we’ve already done maybe 80% of the work, and we can reuse the structured logical model verbatim. We’ll just use a different graphical notation.

Author: Alexey Makhotkin squadette@gmail.com.

I started writing a long post on how to design correct ERD diagrams based on the approach from the “Database Design Book”, but the text got a bit unwieldy. So I’m going to regroup and focus on one part: many-to-many relationships (“M:N links” in book terms).

Suppose that you need to build an ERD diagram based on some sort of real-world or teaching task. How do you make sure that your ERD diagram is correct?

Author: Alexey Makhotkin squadette@gmail.com, ~5400 words.

This is the first public revision of this text. Early readers have shared encouraging feedback, but I’m sure there’s still room for improvement. I’m releasing it now to gather broader input from a wider audience.

Update (2025-06-08): I wrote a prequel to this text: “Multi-join queries design: investigation”. https://minimalmodeling.substack.com/p/multi-join-queries-design-investigation, another 3400 words.

Update (2026-01-25): Here is another prequel: “A modern guide to SQL JOINs” (~8800 words). This one builds the foundation to systematically build queries based on JOINs. https://kb.databasedesignbook.com/posts/sql-joins/.