WORK IN PROGRESS

Database Design Book

Planned release date: Christmas 2024

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
      • Our approach;
      • Benefits of logical model;
      • Finding the list of anchors
        • Anchor IDs;
      • Finding attributes
        • Data types and types of data
      • Defining links
        • Sentences
        • Cardinality
      • More on IDs and unique attributes;
        • Unique attributes;
        • Optional unique attributes;
        • External IDs;
        • ID Enumeration and industrial espionage;
        • Several unique IDs;
      • Why do we use formalized sentences?
      • Graphical schemas;
      • Is my logical model correct?
    • 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;
        • Binary blobs;
      • Links: one-to-many;
      • Links: many-to-many;
      • Physical IDs;
        • 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;
      • Timestamps;
      • Is my table schema correct?
      • Can I skip the logical model step?
      • Why don't we talk about normal forms?
    • 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;
      • Example: WordPress;
      • More examples;
    • Secondary data
      • Speed and convenience;
      • Derived columns;
      • Aggregated tables;
      • Flat tables;
      • A price to pay for speed and convenience;
    • 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;
    • Advanced topics
      • "Events" table;
      • Polymorphic data: books and washing machines;
      • Template repetitions: movie tickets, plane tickets, course assignments;
      • Historized attributes: keeping track of changing prices;
    • Structuring tables differently
      • Why do real-world databases get so messy?
      • Game of Tables;
      • Per-attribute tables;
      • Entity-Attribute-Value (EAV);
      • JSON;
    • Conclusion
      • Cheatsheets;
      • Modeling Templates
      • Tools

    (Unfinished chapters are italized.)

    Interested? Subscribe using the form above. I'm going to send new draft chapters several times per month. Also, I'll send regular requests for the beta readers to try out book drafts.