1 Why Good Database Design?

Learning Objectives

After studying this chapter, you will be able to:

explain what goes wrong when data is stored in one big table without a design;
recognize and name the three kinds of anomalies;
list the characteristics of a good relational design;
describe the two design methods in this book, ERD and normalization, in your own words;
argue which method best fits a given design situation.

1.1 Data without a design

Almost every organization starts out keeping track of its data in a spreadsheet. That makes sense: an Excel worksheet is quick to set up, everyone can work with it, and as long as the amount of data stays limited, things usually go fine. Sooner or later, however, almost every organization runs into the limits of this approach.

Suppose a small online store selling coffee and coffee accessories keeps all of its orders in a single worksheet. Each row describes one product in one order (Table 1.1).

Table 1.1: Orders of the online store in a single table

OrderID	OrderDate	CustomerID	CustomerName	City	ProductID	Description	Price	Quantity
1001	2026-03-02	C01	Johnson	Boston	P10	Coffee beans 1 kg	14.95	2
1001	2026-03-02	C01	Johnson	Boston	P12	Paper filters	3.49	1
1002	2026-03-03	C02	Miller	Denver	P10	Coffee beans 1 kg	14.95	1
1003	2026-03-05	C01	Johnson	Cambridge	P15	Milk frother	24.95	1

At first glance nothing seems wrong: all the information is there. Look more closely, though, and you will see several problems.

Repetition (redundancy). The name and city of customer C01 appear three times in the table. The description and price of product P10 appear twice. With thousands of orders, the same data is stored thousands of times.
Contradiction (inconsistency). According to the first two rows, customer C01 lives in Boston, but according to the last row, in Cambridge. Did the customer move, or was it a typo? The table can’t tell you.
Unclear facts. What is the price of coffee beans? If the price goes up next month, the table will soon contain two prices. Which one applies?

These problems are not caused by careless users, but by the structure of the table: customer data and order data do not belong in the same row. They are two different kinds of things that happen to have been crammed into one table. That is exactly what database design solves: separating different kinds of data into separate, linked tables.

1.1.1 Anomalies: what goes wrong when you make changes

The consequences of a poor structure only really show when you start adding, changing, or deleting data. These problems are called anomalies.

Update anomaly: Customer Johnson moves. You now have to find every row for Johnson and change the city everywhere. Miss one, and the database is inconsistent, as has already happened in Table 1.1.
Insert anomaly: The store adds a new product to its range: a hand grinder (P20). As long as nobody has ordered this product, there is no row in which you can record it. The same goes for a new customer who hasn’t bought anything yet.
Delete anomaly: Customer Miller cancels order 1002, and that row is deleted. With it, all information about customer Miller disappears from the database, even though the customer still exists.

Sound familiar?

Anyone who has ever maintained a large Excel workbook in which the same data appears in several places knows these problems from experience. Lookup functions such as XLOOKUP and PivotTables can solve a lot, but they don’t remove the underlying problem: the same fact is stored in more than one place.

1.1.2 The consequences for an organization

A poor design is not a technical detail. It has a direct impact on business operations.

Unreliable information. Reports on revenue per customer or per region are wrong if the same customer appears in the database with different cities. Management decisions are then based on incorrect numbers.
Higher costs. Finding and fixing errors takes time. The longer a flawed design is in use, the more data there is and the more expensive it becomes to improve the design afterwards.
Hard to extend. If the store later also wants to record suppliers, inventory, or returns, that is nearly impossible in one big table without starting over.
Privacy risks. When personal data is repeated in many places, it is difficult to correct or delete it completely, even though privacy laws¹ require exactly that.

A good design up front is therefore much cheaper than repairing a poor design later.

1.2 Characteristics of a good design

So what is a good design? For a relational database, the type of database this book focuses on, the following characteristics apply.

Each table describes one subject. Customers, products, and orders each get their own table.
Each fact is recorded once. A customer’s city is stored in exactly one place. Change it there, and it is changed everywhere.
Each row is uniquely identifiable. Each table has a primary key (PK), such as a customer number or product number.
Tables are connected through keys. An order refers to a customer by including the customer number as a foreign key (FK). This preserves the connection without copying data.
The database itself guards the connections. An order cannot refer to a customer that does not exist. This is called referential integrity.

If we design the online store according to these rules, we end up with four tables that are linked to each other (Figure 1.1). How you arrive at such a design is exactly what this book is about.

erDiagram
    Customer ||--o{ Order : places
    Order ||--|{ OrderLine : contains
    Product ||--o{ OrderLine : "appears in"
    Customer {
        string CustomerID PK
        string Name
        string City
    }
    Order {
        int OrderID PK
        date OrderDate
        string CustomerID FK
    }
    OrderLine {
        int OrderID PK, FK
        string ProductID PK, FK
        int Quantity
    }
    Product {
        string ProductID PK
        string Description
        decimal Price
    }

Figure 1.1: The design of the online store as an entity relationship diagram

You don’t need to fully understand this diagram yet. Focus on the result: Johnson’s city is now stored in only one place, a new product can be added without anyone having ordered it, and deleting an order leaves the customer untouched. All the anomalies from the previous section are gone.

1.3 From the real world to a database

Designing a database usually happens in three steps, from abstract to concrete.

Conceptual model: Which things exist in the real world that we want to record information about, and how are they related? For example: a customer places orders, an order contains products. This model is independent of any software.
Logical model: The conceptual model translated into tables, columns, primary keys, and foreign keys. This model is still independent of any specific database program.
Physical model: The logical model worked out in a concrete database system, such as Microsoft Access or SQLite, with data types, indexes, and other technical choices.

This book focuses on the first two steps: the conceptual and logical design. That is the thinking that comes before any database, regardless of which program you use afterwards.

1.4 Two methods

There are two common methods for arriving at a good logical design. This book covers both.

1.4.1 Method 1: designing with ERDs

With this method, you start from the real world. You ask yourself questions such as: what does the organization want to keep data about? What properties do those things have? How are they related to each other? You draw the answer in an entity relationship diagram (ERD), like the one in Figure 1.1. You then translate the diagram into tables using a fixed set of rules.

This approach is also called top-down: you start with the big picture and work toward the details. The method was introduced by Peter Chen in 1976 and has been the most widely used way to design databases ever since.

1.4.2 Method 2: normalization

With normalization, you start from the data itself, for example an existing worksheet like Table 1.1, a form, or an invoice. You break that data down step by step into a set of smaller tables. At each step you test the tables against a fixed rule, a normal form. A table that satisfies the third normal form is free of the anomalies you saw above.

This approach is also called bottom-up: you start with the individual data items and build up the structure from there. The method comes from Edgar F. Codd, the founder of the relational model, and was developed in the 1970s.

1.5 Why two methods?

If both methods lead to the same result, why learn both? There are three reasons.

They have different starting points. In practice, a design assignment doesn’t always start the same way. Sometimes you need to design a new system for a process that doesn’t exist yet; then all you have is a description of the real world, and an ERD is the obvious choice. Sometimes there is already an Excel file, a form, or a stack of invoices that needs to be turned into a database; then normalization is the natural approach.

They check each other. An ERD is a drawing based on insight and experience. It is quite possible to overlook something. Normalization gives you an objective test: do the tables that come out of your ERD satisfy the third normal form? Conversely, an ERD helps you present and discuss the tables that come out of normalization in a clear way.

Together they give more understanding. ERDs teach you to think in terms of connections: what things are there, and how do they relate to each other? Normalization teaches you to understand precisely why a design is right or wrong. If you master both, you can not only create a design but also defend it.

1.6 Which method should you choose?

Table 1.2 lists the main differences.

Table 1.2: Comparison of the two design methods

Feature	ERD	Normalization
Starting point	Description of the real world	Existing data (worksheet, form, invoice)
Direction	Top-down	Bottom-up
Character	Visual, intuitive	Step by step, systematic
Strength	Overview and communication with users	Objective test for structural errors
Pitfall	Forgotten or misjudged relationships	Abstract; the big picture can get lost
Suited for	New systems, larger designs	Converting existing lists and documents

You can use the following rules of thumb to choose.

Are you designing something new based on a description or a conversation with the client? Start with an ERD.
Do you already have data in a spreadsheet, on a form, or in a report? Start with normalization.
Not sure, or is the design important? Create an ERD first, then check the tables against the third normal form.

Advice

Most students find the ERD method the easiest to start with, because you can see right away what you are doing. That is why this book covers ERDs first. After that, learn normalization too: it is the tool you use to check whether your design is really correct.

1.7 How this book is organized

The book consists of the following parts.

Chapter 2: Designing with ERDs. You learn to recognize entities, attributes, and relationships, draw them in an ERD, and convert the diagram into tables. The chapter ends with exercises.
Chapter 3: Normalization. You learn to convert an existing set of data step by step into the first, second, and third normal forms. This chapter also ends with exercises.
Chapter 4: Assignments. A series of design assignments in which you choose which method to use. If you like, you can work out an assignment with both methods and compare the results.

The online store from this chapter returns in every chapter as a running example.

1.8 Key concepts

Anomaly: An unwanted effect when adding, changing, or deleting data, caused by a flawed table structure.
Redundancy: Storing the same fact more than once.
Primary key (PK): A field (or combination of fields) in a table that uniquely identifies each row in the table.
Foreign key (FK): A field in a table that refers to the primary key in another table, thereby creating a relationship between the two tables.
Referential integrity: The rule that a foreign key may only refer to a row that actually exists.
Entity relationship diagram (ERD): A drawing of the entities, their attributes, and the relationships between them.
Normalization: A step-by-step method for testing and improving a table structure using normal forms.

Such as the General Data Protection Regulation (GDPR) in the European Union and the California Consumer Privacy Act (CCPA).↩︎