Chapter 3: The Importance of Database Design — Why Build It Carefully From the Start

What you'll learn in this chapter

• List three qualitative reasons database design is hard to fix later
• Explain, with a concrete example, what design-time decisions end up affecting down the road
• Understand why data-design skills remain valuable even in the AI era

Prerequisites

You should understand Chapter 2: The Relationship Between a System and Its Database.

Database design is a building's "foundation"

When you build a house, you do the foundation work first. If the foundation is tilted, no matter how impressive the building you put on top, it won't be stable. And if you try to fix the foundation after the building is finished, you first have to tear down everything above it.

Database design corresponds to this "foundation." No matter how clean your application code is, if the table design is broken, the whole system becomes unstable. And fixing the design after the system is already in operation is close to rebuilding the foundation without ever stopping the live service running on top of it.

The qualitative reasons design mistakes are hard to fix later

It's often said that "fixing a design later is hard," but what exactly makes it hard? Let's organize this into three walls a junior engineer is likely to run into on the job.

1. You can't stop a live service

If an e-commerce site or an SNS announced, "we're about to change the table structure, so we'll be down for three days," users would leave. On a system running in production, you need to carry out schema changes and data migration while keeping downtime to a minimum, which adds care and cost.

During development	In production
You can DROP a table and rebuild it	You have to change things while preserving existing data
Just a few dozen rows of test data	Millions to hundreds of millions of rows of production data
"Let's pause it for a bit" is acceptable	Even one minute of downtime means financial loss or an SLA violation

2. The cost of data migration

When you change a design, you need to write logic to convert existing data into the new shape. Consider, for example, a change like "the last and first name used to be in a single column; I want to split them into separate columns."

• You need to write logic that reads each record and mechanically splits it into last and first name
• It's easy if names are separated by a half-width space, like "Taro Yamada," but you have to decide how to handle exceptions like "YamadaTaro" (no space) or foreign names ("Jean-Luc Picard")
• Before running the migration script on production data, you need a rehearsal with a comparable volume of data
• You need to decide how to handle new data that comes in while the migration is running (lock it? write it twice?)

This kind of migration work can be avoided entirely if the design is done properly from the start.

3. The impact spreads wide

A database is often referenced by multiple systems and features, so changing a table's structure spreads its impact just as widely.

• Renaming a column means rewriting every SQL statement that uses it
• Deleting a column can break past reporting/aggregation queries
• Changing a data type means changing every corresponding type definition on the app side too (TypeScript types, Java classes, etc.)

At design time, the impact is confined to your own code, but after the system is live, it ripples out to the whole team and multiple systems.

A note on quantifying the cost of a fix

You'll often see claims like "fixing something at design time costs 100 times less than fixing it in production." This traces back to research by Barry Boehm (1981) in software engineering, but Boehm himself revised this in 2001, saying it settles closer to a 5x factor for small systems, and more recent research (Menzies et al., 2017) criticizes the claim, saying no clear exponential increase has been demonstrated. The precise number can't be guaranteed, but the general direction — that fixing something later costs more — is something most people would agree with empirically.

What design-time decisions end up affecting later

Let's look at concrete examples of how small choices made at design time end up mattering years down the road.

Decision at design time	Impact that shows up years later
Making the primary key BIGINT instead of INTEGER	Whether you can handle billions of rows, or run out at around 2.1 billion
Making an email column 255 characters instead of 100	Whether users with long email addresses fail to register
TIMESTAMP WITH TIME ZONE vs. WITHOUT for date-times	Whether expanding overseas means recalculating every row due to a time-zone problem
Physical delete vs. logical delete	Whether you can honor a request to "restore a customer I accidentally deleted"
Whether a column has a NOT NULL constraint	Whether invalid data can get in, and the cleanup cost if it does

These are all decisions you could make "in 30 seconds at design time," but if you try to change them after the system is in production, all three of the qualitative reasons above stand in your way.

Data-design skills remain valuable in the AI era

You might wonder, "now that it's the age of AI and machine learning, do we still need database-design knowledge?" In fact, the opposite is true — it matters more precisely because of the AI era.

1. Data is AI's fuel

Machine learning models learn from large volumes of good-quality data. "Good quality" means structured, free of contradictions, and with clearly organized relationships — exactly what database design has always aimed for.

2. Design still matters even on a managed cloud database

Managed services like AWS RDS or Google Cloud SQL take operational work off your plate (backups, replication, OS patching), but they don't take table design off your plate. If anything, on cloud pay-as-you-go pricing, "creating a bunch of unnecessary indexes" or "writing inefficient queries" translates directly into cost, so the quality of your design becomes visible in dollar terms.

3. A common language for every engineer

Frontend, backend, data analysis, infrastructure — whichever specialty you're in, there's always a moment where you need to "read and discuss the shape of the data." Being able to read a table design is basic fitness for communicating within a team.

What good database design enables

Reading through this guide's 19 chapters, glossary, and design template collection will help you build this foundation.

Exercises

Exercise 1: Think of a situation where a design is hard to fix

Suppose you're running an SNS in production and asked to split the existing "username" column so that it later adds a separate "nickname" column too. List three problems you'd expect to run into.

Sample answer

Some problems you might run into:

1. Initial value for existing users: existing users won't have a nickname column, so you need to decide on an initial value (copy username as-is / leave it empty / have the user fill it in later)
2. Rewriting existing SQL: anywhere username is displayed, you'll need to change the logic to prefer nickname and fall back to username if it's not set
3. Past links and notification messages: existing features like "@username notifications" need to be redesigned for how they should behave once nicknames are introduced

A common mistake: estimating that "it's just adding a column, so it's easy" and diving in. Technically, "adding" a new column is simple (ALTER TABLE ADD COLUMN), but the real amount of work is in aligning all the logic that uses it afterward.

Alternative approach: if you'd anticipated at design time that "we might add a nickname in the future," you could have created a general-purpose column like display_name from the start. But over-engineering a design for changes that may never happen raises maintenance cost in its own right. "How far to prepare for future change" is a trade-off.

Exercise 2: Design-time decisions

Suppose you're designing a posts table for a new SNS. Which of the following changes do you think would be hardest to make later? Think it through along with your reasoning.

1. Increasing the maximum post length from 280 to 1000 characters
2. Adding a "category" (something like a tag) to posts
3. Changing the primary key type from INTEGER to BIGINT (to handle more than the ~2.1 billion limit)
4. Changing post deletion from physical delete to logical delete

Sample answer

Option 3 (changing the primary key type) is likely the hardest. Here's why:

• The primary key is likely referenced as a foreign key from other tables (comments.post_id, likes.post_id, etc.), so every table that references it also needs its type changed to match
• A primary key is included in many indexes, so the change requires rebuilding indexes, which takes time with a large volume of production data
• Every type on the application side that handles the primary key (TypeScript's number, Python's int, etc.) also needs to change

How hard the other options are:

• 1 (max post length): often just a simple ALTER TABLE ALTER COLUMN. Relatively light
• 2 (adding a category): light if it's just a new column, but a medium amount of work if it needs to be many-to-many, which requires a junction table (see Chapter 14, Many-to-Many)
• 4 (switching to logical delete): a medium amount of work — add a deleted_at column and rewrite the delete and read queries

This is exactly why: it's common best practice to make the primary key BIGINT from the very start. Whether INTEGER (up to about 2.1 billion) will be enough is hard to predict ahead of time, whereas BIGINT (about 9.2 quintillion) realistically never runs out. The storage overhead is negligible.

Summary

Here's a recap of what this chapter covered.

• Database design corresponds to a building's "foundation" — fixing it after the system is in operation is qualitatively hard
• Three reasons it's hard to fix later: "you can't stop a live service," "data-migration cost," and "the breadth of impact"
• Small decisions at design time (primary key type / character length / time zone / delete strategy) have an outsized effect on future operational load
• Data-design skills remain just as important in the AI era — if anything, they become visible as cloud cost

The next chapter looks at a database's basic capabilities (CRUD + ACID) through actual SQL.

Next chapter

Chapter 4: CRUD and ACID