Database Schema

Storing data with no rules is easy going in and falls apart coming out, because the same fact arrives in a different shape every time.

Take a signup date. One row holds 2026-03-01, another holds 3/1, and a third is empty. That happens as soon as three different screens write to the same table and three different people built them. Nobody notices for a while. It surfaces three months later, when someone asks how many people signed up in the first quarter: the dates can't be compared, the question goes unanswered, and somebody has to repair 2 million rows by hand.

A schema is there to block that at the moment of writing. Declare that the signup date column takes dates and nothing else, and a value that breaks the rule is refused before it is stored. The database becomes the gatekeeper instead of human care, so data arrives in the same shape no matter which screen it came through. A rule that lives only in a document breaks the first time someone new doesn't read the document; a rule in the schema holds even then.

Four things get written down.

1. Tables — how many tables there are and what each one holds. A table stores data in rows and columns, and for a collaborative document product that means a members table, a documents table, a teams table.
2. Columns and types — which fields each table has and what kind of value each accepts. A name is text, a signup date is a date, a view count is an integer. Text columns also get a length, which is what keeps a novel out of a title field. Picking the type is a decision, not a formality: store a price as text and you can no longer add prices together without converting every row first.
3. Constraints — the rules attached to a column. Whether it may be left empty, whether the same value can appear twice, what to fill in when nothing is supplied. Mark the email column as unique and registering twice with one address is refused by the database itself.
4. Relationships — how tables connect. A primary key is the column that identifies one row uniquely, and a foreign key is a column pointing at a row in another table. Point the author column of the documents table at the members table's primary key and a document by a nonexistent member cannot be created.

Design decisions ride on top of that. Normalization splits tables so the same fact is never written in two places, while denormalization allows duplication on purpose for speed. Whether to repeat a team name on every document row or store it once and point at it by number is exactly that choice. Indexes, the structures that make reads fast, are usually managed alongside the schema as well. Either way the blueprint looks the same — read the tables, columns and rules and you can see what the team decided to prioritize.

Backend engineers usually write it. Before building a feature they settle what data gets stored and in what shape, then commit that decision as a file in the code repository. Drawing the screens first and fitting data in afterwards tends to freeze columns that are either missing or unused, which is why the order runs this way.

The readers are a wider group.

1. Other engineers — you need to know the columns to write a query. SQL is the standard language for telling a relational database what you want, and without column names and types there is no sentence to write. Teams using an object-relational mapper (ORM) often keep the declarations in code, where they serve as the schema.
2. Data analysts — pulling any metric starts with checking what actually sits in each column. Not knowing that the status column has five possible values is how an active-user count comes out wrong.
3. Product managers — before proposing a feature, they check whether today's data supports it. If last-login time isn't being recorded, a dormant-account reminder can only ship after the recording starts, which usually means waiting weeks for enough history to accumulate.

Changing a schema means a migration, the work of moving a structure. Unlike deploying code, it has to answer what happens to data already stored: add a column and you owe an answer for what fills it in the existing 2 million rows.

"Let's look at the schema before we talk about this feature."

Settle the shape the data gets stored in before discussing screens or logic.

"That value isn't in the schema. We'd need a new column."

There's nowhere to put it today, so this needs a migration.

"The schema already allows one member to belong to several teams."

The relationship in the blueprint permits it, even if no screen exposes it yet.

When Pagedeck decided to take enterprise customers, the first meeting was about the schema. Until then a member belonged to exactly one team, while an enterprise user moves between several departments. Theo proposed dropping the team column from the members table and adding a separate table that links members to teams.

Mia's question was about the existing 120,000 accounts. Whether to pause the service while moving them, or to keep the old column and the new table side by side and migrate gradually, was the decision in that room. That is how a schema meeting turns into a scheduling meeting: most of the cost of changing a structure comes from the data already in it.

The common assumption is that a schema is an internal document for engineers. The file lives in the repository and reading it takes some notation, so the assumption is easy to make. But metric definitions effectively hang off it. How an active user is counted depends on which values the status column holds, and a metric agreed without knowing that column produces a different answer once someone queries it. When product and analytics never look at the schema, the same word keeps two meanings for a long time.

The opposite mistake is trying to cover every future case up front because changes feel expensive. Create twenty columns for things you might need and most of them stay empty, and once the person who knows what they were for leaves, nobody dares delete them. Storing only what is needed now and adding the rest through migrations is easier to live with.