A common problem in business-oriented database design is how to store the history of data as it changes. Tracking the changes of attribute values is needed more often, and is widely explained, see for example my previous post “Historized attributes: systematic table design”.

In this post we’ll discuss a slightly more complex scenario: historized relationships. First we discuss the M:N case. 1:N is very similar logically, but requires a somewhat different physical approach.

A common problem in business-oriented database design is how to store the history of data as it changes. Tracking changes of attribute values is common, and is widely explained, see for example my previous post “Historized attributes: systematic table design”.

Let’s discuss a somewhat more complex scenario: historized relationships. An example of a historized relationship is somebody assigned to a project for a period of time, then assigned away, and reassigned again later. In some use cases we want to know the entire history: for example, for billing purposes.

Author: Alexey Makhotkin squadette@gmail.com, (~3900 words)

One of the goals of this publication is to deconstruct the traditional ways of teaching basic topics in relational databases. Previously we discussed the fourth normal form (4NF): “Historically, 4NF explanations are needlessly confusing”.

Let’s discuss the ultimate beast: fifth normal form (5NF). Often it’s presented even more confusingly than 4NF is, and we can show that this presentation is unnecessary, and the confusion is completely artificial.

Author: Alexey Makhotkin squadette@gmail.com, (~8800 words)

There are many SQL JOINs guides and tutorials, but this one takes a very different approach. Specifically:

• LEFT JOIN is presented first, INNER JOIN second;

• strict discipline of using ID equality comparison in ON condition;

• we distinguish between N:1, 1:N and M:N cases of JOINs, with N:1 strictly preferred;

• we avoid misleading wording and imagery;

• we show a detailed explanation of overcounting in GROUP BY queries;

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”. Some parts are unclear to me and marked with “(?)”; unfortunately, the original system is also hypothetical, so there are no stakeholders who could tell us what’s the right way. ChatGPT output was not used here, so any mistakes are mine.

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. Also, we’ll talk about collaboration: what if you need to make changes and share them with other people.

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.

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?

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.

Update (2026-01-25): Here is another prequel: “A modern guide to SQL JOINs” (~8800 words). This one builds the foundation to systematically build queries based on JOINs. https://kb.databasedesignbook.com/posts/sql-joins/.

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.com/.