Recent content in Case studies, SQL advice, theory explained on Database Design Book
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…
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…
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…
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…
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…
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…
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…
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…
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:…
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…
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…
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…
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…