Part Five of the Series: Building the Right Software Systems
When a new project begins, the database is often one of the first things we think about. We open a design tool and start creating tables:
Users
Products
Orders
Payments
Then we add relationships, keys, and indexes, and start writing code. Everything looks organized. But after some time, problems begin to appear:
A query becomes slow.
A simple data change becomes complicated.
Some information is duplicated in multiple places.
It becomes difficult to know which table represents the actual source of truth.
In many cases, the problem isn't the database itself. The problem is that data design was never treated as part of the engineering design from the beginning.
Data Is Not a Technical Detail
Data is the system's memory. Code can be changed. An API can be redesigned. Even a large part of the architecture can be replaced. But the data accumulated over years of operation is not something that can be easily ignored. Therefore, when we design a database, we are not simply deciding where to store information. We are also deciding:
What is considered a fact in the system?
What are the relationships between entities?
What rules must never be violated?
What can change?
And what must be preserved?
These are both Business and Engineering decisions.
Start with the Meaning of the Data
Let's take a simple example. We have an e-commerce system and want to store an order. It might seem straightforward:
Order - id - user_id - product_id - price - quantity
But what does `price` mean? Is it the product's current price? Or is it the price the customer paid when the order was created? The question seems small, but it is extremely important. If the product's price changes tomorrow, should the price stored in an old order change? Of course not.
So we aren't storing data simply because we need columns. We are storing facts about events that happened in the real world. Thinking this way changes how we design databases.
The Database Should Protect Business Rules
There is a difference between: "The code prevents something from going wrong" and: "The entire system does not allow it to happen". Suppose we have an Orders table, and every order in the system must be associated with an existing user. It is better for that relationship to be clearly defined and protected at the database level as well. Don't rely only on the assumption that every developer will remember the rule. Large systems contain:
APIs
Background Jobs
Scripts
Admin Tools
Integrations
One day, one of these components may perform an operation that wasn't expected. Whenever the database can help protect data integrity, it should.
Don't Duplicate the Source of Truth
One of the fundamental principles of data design is: Every important piece of information should have a clear source of truth. Imagine that a customer's name exists in three different tables. The customer changes their name. Which copy should be updated? If two are updated and the third is forgotten, the system now contains contradictory information. Data duplication isn't always wrong. Sometimes it is intentional for performance, historical, or system-specific reasons. But we should know: Why are we duplicating this data? Not: "We needed it here, so we copied it."
But Don't Make Normalization a Religion
This brings us to the other extreme. After learning about Database Normalization, a developer may try to apply it literally to everything. Every piece of information ends up in its own table, and every operation requires dozens of JOINs. Good design isn't a competition to see how many tables we can create. The goal is to find the right balance between:
Data integrity
Design clarity
Ease of development
Performance
System requirements
Sometimes duplicating certain data is the correct engineering decision. The important thing is that the decision is intentional.
Indexes Are Not Magic
When a query becomes slow, one common response is: "Add an index". But indexes aren't free. They help with reads, but they require storage and add overhead when data is inserted or updated. So we shouldn't add an index to every column. We need to understand how the data is actually being used. If the system constantly executes: Find orders by customer_id. Then an appropriate index may make perfect sense. But the real question is: What queries actually matter? This brings us back to a principle we discussed in the previous article: Measure before you optimize.
Data Grows Differently from Code
At the beginning, we might have:
10,000 records
Everything is fast
Then:
1 million
10 million
100 million
Suddenly, decisions that seemed unimportant at the beginning start affecting the system. A simple query becomes expensive. A report takes minutes. A deletion operation starts affecting performance. A table that was once small becomes one of the largest parts of the system. That's why we need to think about the lifecycle of data. Not just: "How do we store it?". But also: "How will it grow?" And: "Will we need it forever?"
Not Everything Needs to Live Forever
One common mistake is storing everything forever without a plan. But data has a lifecycle. There may be data that:
We need continuously.
We need for a specific period.
We need for legal or auditing purposes.
We need only for analytics.
Or we no longer need at all.
Each type of data may require a different strategy. This becomes increasingly important as the system grows.
A Migration Is Not Just a File
When we change the database, we're not simply changing a table. We're changing the data structure that the system depends on. That's why Database Migrations should be treated as a fundamental part of project engineering. Changes should be:
Traceable
Reviewable
Clear
Consistently applicable across different environments
Most importantly, understanding the database structure should never depend on the memory of a single developer.
The Database Should Not Become a Bottleneck
In the previous article, we discussed Scalability. The database is often one of the most important bottlenecks in a growing system. But before we think about:
Read Replicas
Sharding
Partitioning
Caching
we should ask simpler questions:
Is the query designed well?
Is there an appropriate index?
Are we reading more data than we need?
Are we executing the same query thousands of times?
Is the data structured correctly?
Does the operation really need to be synchronous?
Many major performance problems begin with a very small problem in query design.
Don't Let the ORM Hide the Database from You
ORM tools are excellent. They make code easier to write and can significantly speed up development. But their convenience can hide what is actually happening inside the database. A few lines of code can result in dozens or hundreds of queries. That's why engineers need to understand what is happening behind the ORM. It isn't enough to know how to write: user.orders You should also know: How many queries will this execute? And: Is this operation still appropriate when we have one million users? Tools help us, but they shouldn't prevent us from understanding the system we're building.
The Data and the Domain Should Speak the Same Language
If the system deals with a concept called `Subscription`, that concept should be clear throughout the design. We don't want it to be called `subscription` in one place, `membership` somewhere else, and `plan_user` in another, while everyone is actually talking about the same thing. This isn't just a naming problem. It's a problem of understanding. When different parts of the system use the same language as the business and its users, communication becomes easier and the system design becomes clearer.
Think About History, Not Just the Current State
There is an important question when designing data: Do we only need to know what is happening now, or do we also need to know what happened in the past?
For example, if a customer's address changes, do we only care about their current address? What about old orders? Should an old order show the customer's address at the time the order was placed? Sometimes we need to preserve a snapshot of data at a specific point in time. Sometimes we need a complete record of changes. This leads to concepts such as:
Auditing
History
Event Logs
Temporal Data
We don't need to use these techniques in every project. But when the nature of the system requires them, the question should be raised early.
Data Is Part of the Architecture
Ultimately, Database Design cannot be separated from Architecture. If we design the system without thinking about data, we'll eventually find ourselves trying to force the database to support a design it was never prepared for. But if we understand:
Domain
Business Rules
Data Lifecycle
Access Patterns
Expected Growth
then database design becomes much clearer.
Principles to Remember
When designing a database, ask:
What facts does the system represent?
What is the true source of each piece of information?
What rules should the database protect?
How will the data grow?
How will it be used?
What needs an index?
Is any data duplicated? Why?
Do we need to preserve a history of changes?
Does the team understand the data structure without relying on one person?
Do we understand what the ORM is doing behind the scenes?
Conclusion
A database is not simply a place where we store data so the application can read it. It is part of the system itself. Every decision we make about it today can affect the project for years. So the question shouldn't be: "How do we create the tables?" It should be: "How do we represent the truth that the system needs to remember, and how do we preserve that truth as the project grows?". When data is designed well, many parts of the system become easier. When data design is weak, the problems appear everywhere:
In APIs
In performance
In reports
In Business Logic
And in everyday development
In the next article, we'll move to the layer that connects the system to the outside world: APIs. How do we design an API that the team can rely on? And what makes a simple API today turn into a problem years later?
A database is much more than a collection of tables used to store information—it is a fundamental part of the system's architecture. In this article, we explore how to think about data as a representation of business truth, how to design for integrity, performance, growth, and history, and why database decisions made today can shape a system for years to come.
Scalability doesn't mean building the biggest system possible from day one, nor does it mean ignoring the future until problems appear. This article explores how to think about scalability pragmatically—when to keep things simple, when to introduce more advanced architecture, and how to design systems that can evolve when real growth arrives.
A well-structured software system isn't defined by its folders or layers, but by the clarity of its components and their responsibilities. In this article, we explore practical principles for dividing a system into maintainable, loosely coupled components that make software easier to understand, extend, and evolve over time.
Every great software system starts with a clear understanding of the problem, not the technology. In this article, we explore why problem analysis should come before solution design, how asking the right questions leads to better engineering decisions, and why understanding the business is the foundation of building software that lasts.
