Posts
A modern guide to SQL JOINs
Author: Alexey Makhotkin squadette@gmail.com. There are many SQL JOINs guides and tutorials, but this one takes a different approach. We try to avoid misleading wording and imagery, and we structure the material in a different way. The goal of this article is to clarify your mental model. (~6600 words) Table of contents Prerequisites Starting with LEFT JOIN Use canonical syntax Use only ID equality in ON condition “Learning foreign language” metaphor Example dataset: employees/payments LEFT JOIN: N:1 case N:1, 1:N, and M:N cases SQL may be too permissive LEFT JOIN: 1:N case Why you should only use ID equality Self-join Fully general LEFT JOIN algorithm Things to remember, pt.
[read more](…
Posts
A modern guide to SQL JOINs
Author: Alexey Makhotkin squadette@gmail.com. There are many SQL JOINs guides and tutorials, but this one takes a different approach. We try to avoid misleading wording and imagery, and we structure the material in a different way. The goal of this article is to clarify your mental model. (~6600 words) Table of contents Prerequisites Starting with LEFT JOIN Use canonical syntax Use only ID equality in ON condition “Learning foreign language” metaphor Example dataset: employees/payments LEFT JOIN: N:1 case N:1, 1:N, and M:N cases SQL may be too permissive LEFT JOIN: 1:N case Why you should only use ID equality Self-join Fully general LEFT JOIN algorithm Things to remember, pt.
Posts
State park database: a Minimal Modeling approach (pt. III-b)
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”.
Posts
ERD diagrams as specification tool (pt. III)
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.
Posts
ERD diagrams, pt. II: physical diagrams
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.
Posts
ERD diagrams, pt. I: many-to-many relationships
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?
Posts
Systematic design of multi-join GROUP BY queries
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. Problem Many times I’ve seen people asking for help with fixing some complicated SQL queries.
Posts
Foreign Keys
Author: Alexey Makhotkin <squadette@gmail.com> (Word count: 2900). Foreign keys are one topic that you cannot ignore if you want to talk about database design. In this informational two-pager I’d like to point out the following aspects of foreign key technology: foreign keys are only a partial solution to the problem of database consistency; in classic relational databases, eliminating foreign keys may be an easy performance win; in many real-world scenarios foreign keys could not be enforced even if the underlying database supports them; This text is a complementary chapter of “Database Design Book” https://databasedesignbook.
Posts
Historized attributes: systematic table design
Author: Alexey Makhotkin squadette@gmail.com. (Word count: 3200). A common problem in business-oriented database design: keeping the history of values of a certain data attribute. For example, we may want to track the price of various goods, as they change with time. Many other tasks could be reduced to this problem: for example, when people change their address in the government database, we may want to keep track of previous addresses.
Posts
Many yes/no attributes: table design study
Author: Alexey Makhotkin squadette@gmail.com. I wanted to demonstrate the relationship between the logical model and a physical model. We’re going to design a commonly seen use case: many yes/no attributes of a single anchor (in our case, Restaurant). Then we’ll discuss how the physical tables would be designed. We’ll see that sometimes physical design strategy changes as the system becomes more mature. At the same time, logical design elements never change if the business requirement is still relevant.
Posts
Database Design for Google Calendar: a tutorial
Author: Alexey Makhotkin squadette@gmail.com. Introduction In this database design tutorial (~9000 words) I’m going to show how to design the database tables for a real-world project of substantial complexity. We’ll design a clone of Google Calendar. We will model as much as possible of the functionality that is directly related to the calendar. This series illustrates an approach explained in my “Database Design Book”. Here is the website of the book: (<https://databasedesignbook.