Database Design Principles

You cannot query a database you cannot picture. Most people who get stuck writing queries are not stuck on syntax; they are stuck because they have no mental image of what they are reaching into.

So this page builds the picture first. It assumes no computer science background. If you can read a spreadsheet, you have everything you need to start, and by the end you should be able to look at an unfamiliar database and imagine its shape before you write a line.

Its companion, Reading a Schema, is the practical follow-on: walking into a real database, querying it, and checking the answer is true.

Start here: a table is a spreadsheet#

That is not a simplification you have to unlearn later. It is genuinely the model.

Three words, and they are the whole vocabulary:

A row (or record) is one real thing: one patient, one order, one appointment.
A column (or field) is one attribute, holding the same kind of value in every row.
A table (or collection) is all the things of one type, stacked up.

When someone says “we have 40,000 consultations”, they mean a table called consultations has 40,000 rows. When you write a query, you are describing which rows you want and which columns you want to see. That is it. Everything else is refinement.

The one idea that makes it a database: rows point at each other#

A spreadsheet holds one grid. A database holds many, and they know how they relate.

Say each appointment needs to know its patient. You could paste the patient’s name into every appointment, but then one person changing their name means hunting down every row. So instead the appointment stores the patient’s id, and the id does the pointing.

That arrow is the entire concept of a join: the appointments table only knows 3c91, so to report a human-readable name you follow the id back to the patients table and pick up A. Rahman.

This is also why real queries feel like work. Ids are what the database connects on; names are what people want to read. Getting from one to the other is most of what a query does.

The three relationship shapes#

Once you see ids pointing at rows, every structure in any database is one of three shapes. Learn to spot which one you are looking at and the query almost writes itself.

Shape	Plain reading	Example	How you can tell
One to one	Each A has exactly one B, and the reverse	A user and their login profile	The id appears at most once on each side
One to many	Each A has many Bs; each B belongs to one A	A patient and their appointments	The id repeats on the “many” side
Many to many	Each A has many Bs and each B has many As	Orders and products	Neither side can hold it; a third table does the join

Many-to-many is the one that trips people up. An order contains many products, and a product appears in many orders, so neither table can store the other’s id. The fix is a third table where one row is one pairing: this order, this product, this quantity. If you ever meet a table whose name is two other table names stuck together, that is what it is.

If you have mapped a process before, this is the same skill aimed at nouns instead of steps. Most confusing schemas are a many-to-many that someone tried to force into one-to-many.

The other shape: documents#

Everything so far was a grid. MongoDB is not a grid. It stores documents: nested, self-contained, shaped more like a form than a row.

Here is the same appointment in both worlds.

A document can hold nested objects and lists inside itself, which a spreadsheet cell cannot. That is the real difference, and it drives everything else.

	Relational (PostgreSQL, MySQL)	Document (MongoDB)
One thing is	A row in a table	A document in a collection
Shape	Flat grid, fixed columns	Nested, fields vary per document
Structure enforced by	The database, strictly	The application code, by habit
Combining	`JOIN`	Often already nested, or `$lookup`
Language	SQL	The Mongo query language

The sentence to hold onto: in a relational database the schema is a promise the database keeps. In a document database it is a habit the application has. A Postgres column exists in every row because the database refuses otherwise. A MongoDB field exists because whichever version of the code wrote that document happened to include it. Code changes, so documents disagree.

That single difference causes most wrong answers people get from MongoDB, and it comes back at the end of this page.

The central tradeoff: store once, or duplicate#

Look again at the two pictures above. The relational side stores the patient’s name once. The document side copied it inside the appointment. That is the fundamental tradeoff, and nearly every design decision is a version of it.

Normalize means store each fact exactly once, and point at it with ids. Change a name in one place and every appointment is instantly correct. The cost is that answering a question means reassembling pieces from several tables.

Denormalize means duplicate the fact where it gets read. Reads are one hop and fast. The cost is that copies drift: change the name and old appointments still carry the old one.

Neither is right in general. Normalizing favours correctness and writing; denormalizing favours speed and reading.

For anyone querying, denormalized copies are the trap. If the name sits on the appointment, you are reading the name as it was when that appointment was written, not the name today. Sometimes that is exactly right, which is why an order deliberately freezes the price paid; if it re-read today’s price your revenue reports would rewrite history every time someone edited a product. Often it is wrong, and it never announces itself. You get a plausible answer, not an error.

Always know which one you are reading.

Embed or reference#

In a document database that tradeoff becomes one concrete choice: put related data inside the document, or store an id and look it up.

Rules of thumb that hold up:

Embed when the child belongs to one parent, is read whenever the parent is read, and is bounded. An address on a user. The line items on an order.
Reference when the data is shared between parents, queried on its own, or unbounded. A patient referenced by their appointments.
The question that decides it: does this list have a ceiling? Embedding something that grows forever is the most common document-model mistake. A document has a size limit, so an unbounded list eventually starts failing writes.

What an index actually is#

An index is the same thing as the index at the back of a book. Without one, finding every mention of a word means reading all 400 pages. With one, you flip to a sorted list and jump straight to the pages.

A database index is a pre-sorted lookup kept beside the table. Without it, “find the appointments for this doctor” reads every row and checks each one. With it, the database jumps straight there.

Three things worth knowing even if you never create one:

Indexes speed up reads and slow down writes. Every write updates every index, so nobody indexes everything.
An index only helps if it matches how you filtered. An index on doctor_id does nothing for a filter on created_at.
A slow query is usually a missing index, not too much data. If a query hangs, that is a question for an engineer, not a reason to abandon the question.

The schema is never finished#

Products change, so the shape of stored data changes with them. This is normal. It is also the single biggest source of confidently wrong answers, and it hits document databases hardest, because nothing forces old documents to match new ones.

Fields appear over time. A field added last year does not exist on older records. Filter on it and you silently exclude everything older, and get a smaller, wrong, entirely plausible number.
Meanings drift. A status value gets reused for a slightly different case. The name never changes.
Old shapes survive. In one collection you can have documents written by three generations of code, sitting side by side, each shaped differently.
Deletes are often not deletes. Many systems set a deleted flag instead of removing the row. Miss it and you count things that no longer exist.
Test and internal records are in there too. Staff accounts and load tests are real rows and will happily inflate your totals.

The habit this earns: before you trust a filter, find out when that field started existing. Reading a Schema has the mechanics for checking.

The posture#

A schema is a frozen argument about how the business worked, written by people who did not know what you would need to ask. It will be partly out of date, partly wrong, and mostly undocumented. That is the normal condition, not a problem with your database or your understanding.

It just means a true answer comes from understanding the shape, not from trusting the field names.