Database Design: From Data to Application Architecture

Applications depend on data, and poor data design creates problems that get harder to fix as the application grows. Changing a schema with no rows in it is a decision; changing one with years of production data in it is a project.

That is why database design belongs near the start of application architecture rather than at implementation time.

Where does database design start?

Not with tables. Before creating anything, establish what information exists, who creates it, who updates it, who reads it, and how the things it describes relate to one another.

Those questions are about the business, and answering them usually reveals that two teams mean different things by the same word — which is far cheaper to discover now than after both meanings are in the same column.

What is an entity?

An entity is something meaningful in the domain. An application managing customers might have Organisation, Customer, Contact, Order and Payment.

The test is whether the thing has an independent identity and a life of its own. An order exists whether or not anyone is currently looking at it. A shopping basket mid-session may not need to.

How do relationships shape the model?

Entities relate: an organisation has customers, a customer has orders, an order has payments. Working out the cardinality — one to one, one to many, many to many — determines the shape of the schema, and getting it wrong is the mistake that is most expensive to reverse.

The question worth asking early is whether a relationship that looks like one to many will stay that way. A customer belonging to one organisation is a clean model right up to the day a customer belongs to two.

What does a relational database give you?

Relational databases organise data into tables and the relationships between them, queried with SQL. The concepts that matter in design:

• Primary keys — how a row is identified.
• Foreign keys — how a relationship is enforced rather than hoped for.
• Constraints — the rules the database will not let you break.
• Indexes — the structures that make specific queries fast.
• Transactions — the guarantee that a group of changes applies completely or not at all.

Why does integrity belong in the database?

A system should make invalid states impossible rather than merely unlikely: required information missing, duplicate records, relationships pointing at nothing, two concurrent updates leaving a half-applied result.

Application validation and database constraints do different jobs. Application validation gives a person a good error message. Database constraints are what hold when a second service, a migration script or a well-meaning manual fix bypasses the application entirely. Relying only on the first is how bad data gets in.

What about performance?

As data grows, query performance stops being theoretical. Indexes make specific queries fast, but indexing everything is not a strategy: every index costs storage and slows writes.

Design for the access patterns you actually have. The useful question is not "is this table indexed" but "what queries does this application run most often, and what do they touch". That is knowable from the workflows you mapped at the start.

How does it fit the application?

In a typical system a request travels from the user to the frontend, to the interface layer, into business logic, and finally to the database — and back.

Each layer has a responsibility, and the common failure is letting them blur: business rules written into queries, or database structure leaking into the user interface. Either makes the other impossible to change alone.

What should an engineer consider?

• Migrations — how the schema changes without losing data or downtime.
• Backups, and whether a restore has ever been tested. An untested backup is a belief.
• Retention — what you keep, for how long, and what obligations that creates.
• Access — who and what can read each table.
• Concurrency — what happens when two people act at once.
• Growth — which table becomes the large one, and what that breaks first.

The practical conclusion

A database is not storage bolted to the side of an application. It is the information architecture the application is built on, and it outlives most of the code above it.

---

Related reading: custom software versus off-the-shelf, and software architecture.