WORK IN PROGRESS

Database Design Book

Planned release date: spring 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

    • Quick introduction and example: Library use-case
      • Overview of the entire process;
      • Skipping ahead: the end result;
      • Creating logical model;
      • Building physical tables;
      • A graphical schema;
    • Building the logical model
      • Finding the list of anchors
      • Finding attributes
        • Data types and types of data
      • Defining links
        • Sentences
        • Cardinality
      • Why do we use formalized sentences?
      • Graphical schemas;
      • Is my logical model correct?
    • Logical modeling: examples
      • Example: Google Drive;
        • Requirements;
        • Anchors;
        • Attributes;
        • Non-attributes;
        • Links;
        • Graphical schema;
      • Example: e-commerce system;
      • Example: project management web application;
      • More examples;
    • Creating tables: physical model
      • SQL syntax;
      • 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;
      • Timestamps;
      • Is my table schema correct?
      • Can I skip logical model step?
      • Why don't we talk about normal forms?
    • Physical modeling: examples
      • Use-case: Google Drive, pt. II;
      • Example: e-commerce system;
      • Example: project management web application;
      • More examples;
    • Secondary data
      • Speed and convenience;
      • Derived columns;
      • Aggregated tables;
      • Flat tables;
      • A price to pay for speed and convenience;
    • Schema evolution
      • Evolving logical model
      • ALTER TABLE: evolving physical model;
      • Managing project risk with logical modeling;
    • Advanced topics
      • "Events" table;
      • Polymorphic data: books and washing machines;
      • Template repetitions: movie tickets, hotel nights, 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.