Database Schema Design Guide: From Requirements to a Clean ERD
A good database schema is invisible: it just works, for years, as the application grows. A bad one leaks into every query and migration. This guide is about getting it right early.
Database schema design is the act of deciding what tables exist, what columns they hold, and how they relate - and it is one of the highest-leverage decisions in a software project. A well-designed schema fades into the background: queries are natural, integrity is enforced by the database, and adding a feature rarely means restructuring what is already there. A poorly designed one taxes every day of development, forcing awkward joins, duplicate data, and migrations that touch half the tables. The difference is almost always made early, before much code exists.
This guide covers schema design end to end: turning requirements into entities, choosing primary and foreign keys, deciding relationships and their cardinality, normalizing to remove redundancy, and knowing when to denormalize on purpose. Throughout, an entity relationship diagram is the working document, and you can build yours in Atlas Diagram Studio at /diagrams using the ERD tool at /diagram-tools/erd-tool. Designing the schema visually, before you write a migration, is far cheaper than discovering the flaws in production.
Turn requirements into entities and relationships
Schema design starts with understanding what the system must remember and how the pieces relate. Read or gather the requirements and pull out the nouns - the things - and the verbs that connect them. A sentence like a customer places orders, and each order contains products, hands you three entities and two relationships almost verbatim. Capture these as boxes and lines first, at a conceptual level, without worrying yet about column types or keys; the shape of the model matters more than its details at this stage.
As the entities take shape, name the relationships and their cardinality explicitly, because ambiguity here becomes bugs later. Is a product in exactly one category or several? Can an order exist without a customer, for a guest checkout? Each answer changes the schema. Working this out on a diagram in the editor at /diagrams lets you and your stakeholders see the same picture and catch the mismatches in understanding that plague text specifications. The conceptual model you settle on becomes the skeleton everything else hangs from.
Choose keys and enforce integrity
Keys are the backbone of a relational schema. Every table needs a primary key that uniquely identifies each row, and the safe default is a surrogate key - an auto-incrementing integer or a UUID - that the system controls and that never has to change. Relationships are then made with foreign keys: the child table stores the parent's primary key, and a foreign key constraint tells the database to reject any child that points at a parent that does not exist. That constraint is not bureaucracy; it is the database guaranteeing your data cannot drift into an impossible state.
Beyond keys, let the schema enforce as many rules as it can. A NOT NULL constraint captures that an order must have a customer; a UNIQUE constraint captures that two users cannot share an email; a CHECK constraint captures that a quantity must be positive. Every rule you push into the schema is a rule your application code no longer has to remember to enforce, and one that stays true no matter which service writes to the table. A schema that enforces its own integrity is dramatically more robust than one that trusts every caller to behave.
Normalize to remove redundancy
Normalization is the process of organizing columns so that each fact is stored exactly once. The symptom of a poorly normalized schema is duplication: a customer's address copied onto every order, a category name repeated on every product. Duplication is not just wasteful; it is a correctness hazard, because the copies drift out of sync and there is no longer a single source of truth. Normalization moves each repeated fact into its own table, referenced by a foreign key, so there is one authoritative copy.
- First normal form (1NF): every column holds a single atomic value - no comma-separated lists, no repeating groups of columns.
- Second normal form (2NF): every non-key column depends on the whole primary key, not just part of a composite key.
- Third normal form (3NF): non-key columns depend only on the key, not on other non-key columns - no transitive dependencies.
- A repeated group of values, like phone1, phone2, phone3, signals a missing child table you should extract.
- A value copied across many rows, like a category name on every product, signals a lookup table referenced by a foreign key.
- Aim for third normal form as the default, since it removes the redundancy that causes update anomalies without over-fragmenting the schema.
Know when to denormalize
Normalization is the right default, but it is not a religion. A fully normalized schema can require many joins to answer a common query, and at scale those joins can be too slow for a hot path. Denormalization deliberately reintroduces some redundancy - a cached order total on the order row, a duplicated author name on a comment - to make reads faster. The key word is deliberately: you accept the cost of keeping copies in sync in exchange for a specific, measured performance win, rather than duplicating by accident.
The discipline is to normalize first and denormalize only where evidence demands it. Start with a clean third-normal-form design, measure real query performance, and denormalize the specific paths that prove too slow, documenting why. Reflect these choices in your ERD so future maintainers understand that a duplicated column is intentional, not a mistake. For the mechanics of turning a schema into a diagram and keeping it current, see the guide on generating diagrams from code at /guides/how-to-generate-diagrams-from-code, and explore the type-specific tools under /diagram-tools for the different views your documentation needs.