Database Design Book

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)

Learn how to get from business requirements to a database schema

If this post was useful, you may find this book useful too.

Table of contents and sample chapters

Book length: 145 pages, ~32.000 words. Available in both PDF and in EPUB format.

Purchase for €32