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.| kb.databasedesignbook.com
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 on Database Design Book
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. Problem Many times I’ve seen people asking for help with fixing some complicated SQL queries. A common scenario is that people try to solve the problem by using JOIN with multiple tables, sometimes with subqueries.| kb.databasedesignbook.com
Author: Alexey Makhotkin (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 underl...| kb.databasedesignbook.com
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.| kb.databasedesignbook.com
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 requiremen...| kb.databasedesignbook.com
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 the book called “Database Design using Minimal Modeling”, scheduled to be released in Summe...| kb.databasedesignbook.com