Almost every truly painful schema I have had to live with started the same way. Someone opened a migration file and started typing CREATE TABLE before anyone had agreed on what the data actually was. The tables came first and the understanding came later, which is exactly backwards. A schema is the last artifact in modeling, not the first. By the time I write SQL, the hard thinking is already done.
This is the methodology I use to get from a fuzzy feature request to a schema I am willing to sign my name to. It is not heavy. It does not need special tooling. It mostly needs you to slow down for an afternoon so you can move fast for the next two years. For the design principles that govern the final schema, pair this with my post on normalization and when to denormalize.
Step one, gather the nouns and the rules
I start by reading or listening to how the people who actually do the work describe it. Not the engineers. The people in the domain. I am hunting for two things. The nouns, which become candidate entities, and the rules, which become constraints and relationships.
When a logistics coordinator says “a shipment can have many parcels but every parcel belongs to exactly one shipment,” they have just handed me a one-to-many relationship and a NOT NULL foreign key, for free, in plain language. The domain experts are doing the modeling already. My job is to write it down faithfully and notice when their sentences contradict each other.
I keep a running glossary as I go. The single most underrated modeling tool is an agreed definition of each term. When two people use the word “account” to mean two different things, you will not find out until production, unless you forced the definition early.
Step two, find the entities and their identity
From the nouns, I pull out the real entities. The test I apply is identity. Does this thing have an existence of its own that I need to refer to over time? A customer does. An order does. A line on an order does. The color “blue” usually does not, it is an attribute, until the day the business needs a color catalog with its own rules, at which point it earns entity status.
For every entity I ask one question immediately. What makes a row unique? Sometimes there is a natural key, like an ISO country code. More often there is not, and I add a surrogate key, a generated identity column with no business meaning. I lean toward surrogate keys for most entities because natural keys have a nasty habit of changing, and a primary key that changes is a primary key that ruins your week.
Step three, map the relationships
Now I connect the entities, and I am precise about cardinality because cardinality is where the schema is decided.
- One to many is the common case. The “many” side carries a foreign key pointing back to the “one” side. An order has many items, so order_items carries the order_id.
- Many to many always becomes a junction table. There is no other honest way to represent it. Students and courses meet in an enrollments table that carries both foreign keys.
- One to one is rare and deserves suspicion. Usually it means you either have one entity that you split for no reason, or you have an optional extension that genuinely belongs in its own table. I make myself justify every one-to-one.
For each relationship I also pin down the participation rules. Is the foreign key mandatory or optional? What should happen on delete? These are not afterthoughts. ON DELETE behavior is a real business decision dressed up as a technical one, and the business should get a vote.
Step four, attributes and the grain question
With entities and relationships in place, I attach attributes, and for each one I ask what it really is. Is it atomic, or is it secretly several facts crammed together? A “name” field that everyone wants to search by first and last name is two columns pretending to be one. An address is almost always several.
The most important question at this stage is grain. What does one row in this table represent, exactly, in one sentence? If I cannot say it cleanly, the table is confused and the queries will be too. “One row per order” is a clear grain. “One row per order, except sometimes per shipment” is a future incident report.
Step five, write the schema and let the database help
Only now do I write SQL, and at this point it almost writes itself, because the thinking is finished. Here is the kind of thing that falls out of the steps above for a simple course enrollment domain.
-- One row per student
CREATE TABLE students (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email CITEXT NOT NULL UNIQUE,
full_name TEXT NOT NULL,
enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE
);
-- One row per course offering
CREATE TABLE courses (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code TEXT NOT NULL UNIQUE, -- natural key, stable per catalog
title TEXT NOT NULL,
capacity INT NOT NULL CHECK (capacity > 0)
);
-- The junction table: one row per student per course
CREATE TABLE enrollments (
student_id BIGINT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
course_id BIGINT NOT NULL REFERENCES courses(id) ON DELETE RESTRICT,
grade TEXT CHECK (grade IN ('A','B','C','D','F') OR grade IS NULL),
PRIMARY KEY (student_id, course_id)
);Look at how much of the model is now enforced by the database rather than left to hope. The many-to-many becomes a composite primary key, which also prevents a student from enrolling twice in the same course without any application code. The two different ON DELETE choices encode a real rule: deleting a student removes their enrollments, but you cannot delete a course that still has students in it.
Step six, validate against the queries you will run
A model is only as good as the questions it can answer. Before I call it done, I take the five or ten queries the application will actually run most often and I write them against the schema on paper. If a common question requires a tortured five-table join or a subquery nested three deep, the model is fighting the workload and I go back a step.
This is also where access patterns start to inform indexing, though I keep that as a separate concern. Get the model honest first, then make it fast. I walk through the performance side in detail in my database indexing deep dive.
Step seven, plan for change
No model survives contact with a roadmap unchanged, so I design for evolution from the start. I avoid columns that mean different things in different rows. I prefer adding a nullable column or a new table over overloading an existing one. I keep a migration discipline where every schema change is a versioned, reviewable file, never a manual edit to a live database.
The mindset that has served me best is this. Modeling is the act of writing down what is true about the world, carefully enough that the database can enforce it. The SQL is just the transcript. Do the thinking first, write the schema last, and validate it against the real questions, and you end up with tables that feel boring in the best possible way. Boring schemas do not page you.