WORK IN PROGRESS

Database Design Book

Planned release date: April 2025

The goal of this book is to help you get from a vague idea of what you need to implement (e.g.: “I need to build a website to manage schedule and instructor appointments for our gym”), to the full and complete definition of database tables. Estimated book length: 150-200 pages.

To achieve the goal, first we show how to build a structured list of anchors, attributes and links. To understand the business requirements, we spell out what each element does, using carefully designed formalized sentences. After that, we can build physical model based on the logical model, in a straightforward way.

Subscribe to receive draft chapters and book updates

I'm writing this book in the open. I regularly solicit beta reading feedback on the book. See the table of contents below.

    We respect your privacy. Unsubscribe at any time.

    My name is Alexey Makhotkin, I’ve been working with databases for more than 25 years in various roles: software engineer, database administrator, team lead, head of software engineering. I've built dozens of schemas along the years.

    ​
    A few years ago I started the “Minimal Modeling” substack: https://minimalmodeling.substack.com/, to summarize what I’ve learned along the way. This book is another step in that direction.

    Table of contents

    • Introduction
      • Who is this book for?
      • At what level of understanding is this book aimed?
      • What you would get from reading this book;
    • Building a logical model
      • System requirements as input;
      • Can I skip a logical model?
      • Who do we write logical model for?
      • Can I use a diagram instead?
      • Elements of logical model;
      • The process
      • Anchors
        • Example: posts;
        • List of anchors;
        • Example: invoices;
        • Anchor IDs
      • Attributes
        • Attributes: definition;
        • Attributes and anchors;
        • Human-readable questions;
        • Example values;
        • Data types and types of data
        • What is not an attribute?
        • How to confirm if we got all the attributes?
      • Links
        • Links: definition;
        • Links: pairs of anchors;
        • Links: cardinality;
        • Cardinality is a business concern;
        • Links: Sentences
        • False links and unique pairs of IDs;
      • More on anchors;
        • Unique attributes;
        • Optional unique attributes;
        • External IDs;
        • ID Enumeration and industrial espionage;
        • Several unique IDs;
      • Handling time in the logical model;
    • "Hello world" use case: a podcast catalog;
    • Use case: Google Drive;
      • Requirements;
      • Anchors;
      • Attributes;
      • Non-attributes;
      • Links;
      • Graphical schema;
    • Building a physical schema
      • Many table design strategies are possible;
    • Table-per-anchor table design strategy
      • Action plan
      • Anchors: choose table names;
      • Attributes: choose table names;
      • Attributes: choose column data types;
        • Cheatsheet: recommended data types;
        • Strings;
        • Integer numbers;
        • Monetary amounts;
        • Numeric values;
        • Yes/no values;
        • Either/or/or values;
        • Dates;
        • Date with time, UTC timezone;
        • Date with time in a specific timezone;
        • Binary blobs;
      • Links: one-to-many;
      • Links: many-to-many;
      • Physical IDs;
        • Cast study: a tiny CMS;
        • Maximum number of items;
        • Reaching the maximum;
        • Space taken by IDs;
        • Disk space is time;
        • Storage density;
        • UUIDs as anchor IDs;
        • Countries, currencies, languages;
        • Composite IDs;
        • IDs should not change;
      • Handling time in the physical model
    • Other table design strategies;
    • Database design for Google Calendar: a tutorial (free chapter): https://kb.databasedesignbook.com/posts/google-calendar/​
      • Problem description;
      • Basic all-day events;
      • Time-based events;
      • Repeated all-day events;
      • Rendering the calendar page;
      • Complete logical model;
      • Creating SQL tables;
      • The tables;
    • Physical modeling: examples
      • Example: e-commerce system;
      • Example: project management web application;
      • More examples;
    • Secondary data
      • Cached column example;
      • There is no free lunch;
      • Cached column is not an attribute;
      • Discovering secondary data;
      • Documenting secondary data;
    • Evolving your database
      • Elementary database migrations;
      • Table rewrite;
      • Adding an attribute
      • Adding an anchor;
      • Dealing with table rewrite;
      • Other elementary database migrations;
      • Managing project risk with logical modeling;
    • Common design patterns
    • Structuring tables differently
      • Why do real-world databases get so messy?
      • Game of Tables;
      • Per-attribute tables;
      • Entity-Attribute-Value (EAV);
      • JSON;
    • Conclusion
      • Cheat sheet;
      • Modeling Templates
      • Tools

    (Unfinished chapters are italized.)

    Interested? Subscribe using the form above, and you'll receive an email when the book sales begin.