Getting the data model right: where normalization should stop

The most expensive thing to change later in a database is the data model. You can add an index to a slow query in minutes and fix a bad setting with one line. But what a table represents, and where each piece of information lives, reaches into every query in the application and every row in the database. Changing it means moving code and data together while the system is running.

So this post is about normalization. I won't repeat the definitions of the normal forms; every database book has them. Instead I'll show what keeping a fact in one place has bought me in my own systems, when I copy data on purpose, and how I pay for the copy.

The examples come from three systems: this blog; KillReport, a real-time killmail tracking and analytics platform for the EVE Online community; and Biocidal, a multi-tenant, GIS-based field management system that municipalities use for pest and vector control.

#What normalization actually buys you

If I had to sum up normalization in one sentence: every fact lives in exactly one place. A customer's address sits in the customers table; orders point to the customer by id. When the address changes, one row is updated and every order sees the new address.

You see the value when a fact doesn't live in one place. Here is an example from the blog you are reading. Post tags are not in a separate table; they sit on the post itself as an array:

model Post {
  id   String   @id @default(uuid())
  // ...
  tags String[]
}

In PostgreSQL this is a text[] column. Querying it is easy:

SELECT title FROM posts WHERE tags @> ARRAY['Node.js'];

But the tag itself isn't defined anywhere. Each post carries its own list of tags as free text. If I write Node.js on one post and node.js on another, the database has no idea they are the same thing:

SELECT tag, count(*) FROM posts, unnest(tags) AS tag GROUP BY tag;

    tag     | count
------------+-------
 node.js    |     1
 Node.js    |     1
 PostgreSQL |     1

On this blog I solve that in the application. The tag suggestions in the admin panel treat different spellings of the same tag as one and put the most used spelling first. In other words, I give by hand, in code, the guarantee that normalization would give in the database.

The normalized version would look like this:

CREATE TABLE tags (
  id   serial PRIMARY KEY,
  name text NOT NULL
);
CREATE UNIQUE INDEX tags_name_key ON tags (lower(name));

CREATE TABLE post_tags (
  post_id int REFERENCES posts ON DELETE CASCADE,
  tag_id  int REFERENCES tags  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

With this schema, the database objects the moment you try to add node.js:

ERROR:  duplicate key value violates unique constraint "tags_name_key"
DETAIL:  Key (lower(name))=(node.js) already exists.

Renaming a tag becomes a one-line UPDATE. And when you want to give a tag a description, a page URL or a logo, you have somewhere to put it.

So why does this blog still use an array? Because here the cost is small. There is one author, few posts, and tags have no data of their own. The array is less work than a join table and the code that manages it. The day tags get their own pages, descriptions or several authors, the math changes. I don't treat normalization as a rule but as this kind of calculation: I normalize when the cost of inconsistency is bigger than the cost of the extra table.

#Copying on purpose

Denormalization means keeping the same fact in a second place. It is usually done to speed up reads: instead of computing the result every time, you write it down. I do this too, but for every copy I ask myself two questions:

1. Which one is the source of truth? When the copy breaks, what do I rebuild it from?
2. Who updates the copy, and where? The answer has to be a single place.

#A counter with a source of truth

The view count on this blog is a good example. Each post has a number in its views column. But that number isn't the real information. The real information is in the post_views table:

CREATE TABLE post_views (
  post_id    text REFERENCES posts ON DELETE CASCADE,
  ip_hash    text,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (post_id, ip_hash)
);

When I record a view, I first insert a row into this table, and only if the row was actually inserted do I increment the counter. Both happen in the same transaction. Because the primary key is (post_id, ip_hash), when the same IP opens the same post a second time the insert does nothing and the counter doesn't move. In Prisma it looks like this:

return db.$transaction(async (tx) => {
  const { count } = await tx.postView.createMany({
    data: [{ postId: post.id, ipHash: hashIp(ip, secret) }],
    skipDuplicates: true,
  });
  if (count === 0) return post.views;
  const updated = await tx.post.update({
    where: { id: post.id },
    data: { views: { increment: 1 } },
    select: { views: true },
  });
  return updated.views;
});

If the counter ever breaks, I know what to do, because the source of truth is still there:

UPDATE posts p
SET views = (SELECT count(*) FROM post_views v WHERE v.post_id = p.id);

That is what makes the copy acceptable: it can be rebuilt from the source at any time.

#The real risk of a copy: more write paths

In KillReport the same idea runs at a much larger scale. When a killmail is saved, the daily counters behind the leaderboards are incremented in the same transaction as the record itself:

INSERT INTO character_kill_stats (kill_date, character_id, kill_count)
VALUES ($1::date, $2, 1)
ON CONFLICT (kill_date, character_id)
DO UPDATE SET kill_count = character_kill_stats.kill_count + 1;

The primary key is (kill_date, character_id). The first record of the day creates the row and the rest increment it. No scheduled job needed.

The SQL was fine. The problem was that the code writing killmails didn't live in one place. Over time seven different files had come to write killmails, and they had drifted apart. Two of them never updated the leaderboard counters. When I noticed, 3,847 killmails weren't counted in any leaderboard.

I rebuilt the counters from the source records, but the real fix was in the code, not the database: a single function that writes killmails. That function writes the killmail, the victim, the attackers and the counters in one transaction. Nothing else touches the killmails table directly.

The lesson I took from it: the risk of denormalization is usually not in the SQL but in how many paths write the same data. If you keep a copy, put the path that updates it in one place too.

One consequence of using the same transaction is worth remembering. In PostgreSQL, if a statement inside a transaction fails, the rest of the transaction is aborted:

ERROR:  current transaction is aborted, commands ignored until end of transaction block

So you can't catch the counter's error in application code and say "never mind, carry on". You chose the same transaction precisely because you wanted that guarantee: either both the killmail and the counter are written, or neither is. If you want to let one part fail, you need a SAVEPOINT for that part. But then you have given up the guarantee that the counter stays consistent with the source record.

#Copying derived data means copying the derivation too

KillReport has a wide read table for filtering: killmail_filters. For each killmail, everything that can be filtered on, the victim's and attackers' ids, the region, the value, sits in a single row. Attacker ids are in int[] arrays with a GIN index.

This table started out as a materialized view. The view's definition found the region and constellation by joining other tables. To insert new killmails one by one instead of refreshing the whole view each time, I turned it into a regular table. While writing the insert code, I forgot to carry that join over.

Once the view definition was gone, so was the only place that said where the region came from. New rows arrived with an empty region and constellation. By the time I noticed, months later, those two fields were NULL in 37,781 of 44,493 rows.

When you derive data and copy it, you also copy the logic that derives it. If that logic lives in two places in the code, they will drift.

Since then, for every column I copy, I answer one question explicitly: can this value change? The repair migration in KillReport has both answers side by side:

• A ship type's group never changes. Once this copy is written correctly it stays correct forever; no syncing needed.
• A killmail's ISK value (the game's currency) changes with market prices. A separate worker keeps this copy up to date.

The first is a cheap copy. The second needs a process that owns it. A column copied without making this distinction is a column nobody knows has gone stale.

#Not a copy, but a record of that moment

Not every repeated fact is denormalization. In KillReport, an attacker's corporation id is stored inside the killmail exactly as ESI (the game's official API) sent it at that moment. If the pilot changes corporation the next day, the record doesn't change, and it shouldn't. What is stored isn't "the pilot's corporation" but "the pilot's corporation in that fight".

The e-commerce equivalent is the price written on an order line. The product's price changes later, but the order total doesn't. Replace that column with a join to the products table and you rewrite history.

Biocidal has many columns like this, and each one carries a note saying why it is copied. In subscription events, the plan and company name are copied at the time of the event, so the history stays readable even if the plan is renamed or deleted later. In events where vehicles enter and leave an area, the area's name is copied, so the log keeps its meaning even if the area is deleted. In the notification queue, the recipient's address is copied, so "what did we send to this address last month?" can still be answered after the staff member is deleted. Some records deliberately have no foreign key to the company at all: after a company's data has been exported, the archive record must survive even if the company is deleted.

What they have in common: none of these columns holds a "current value" and none of them is synced. They record when something happened, to whom, under which name. Normalizing them would mean losing information.

Both meanings can live side by side in the same system. What matters is knowing which one each column carries. In KillReport, whether a killmail belongs to a faction is decided by the faction id on the killmail at that moment. The faction counters, on the other hand, use the current faction of characters and corporations. Without a comment in the code explaining this, anyone seeing different numbers on two screens would assume it was a bug.

#Constraints: telling the database the rule

#A type is a constraint too

A column's type is the first rule you give the database. Get it wrong and it produces the sneakiest bugs. In Biocidal, the mobile number on requests lived in a bigint column for a long time. It sounds reasonable: the number is made of digits. But a phone number isn't a number; you never add or compare phone numbers.

Try writing a number that starts with + into that column:

UPDATE request SET mobile = '+905321234567' WHERE id = 1;

You get no error. When PostgreSQL converts the text to a number it silently drops the + and stores 905321234567. There is no way left to store the country code, and supporting international numbers with this column is impossible.

Worse, the same number was written in more than one way. Users' phone numbers lived in another table as free text: 5321234567 in some records, +905321234567 in others. The application believed it prevented the same number from being registered twice. But that uniqueness was never defined in the database, and two different spellings passed the application's check as separate records.

The fix took four steps. First I changed the column type without losing data:

ALTER TABLE request ALTER COLUMN mobile TYPE text USING mobile::text;

Then I converted every number to a single format, E.164 (+905321234567). Values that didn't fit the rule I set to NULL. 238 or a thirteen-digit number isn't a phone number; forcing them into the format would have produced numbers that don't exist. I chose saying "I don't know" over turning bad data into wrong data. The migration printed how many records it cleaned as it ran. Finally, I told the database the rule the application had assumed all along:

CREATE UNIQUE INDEX user_company_id_phone_key ON "user" (company_id, phone);

If a rule lives only in the application, it is gone the moment data comes in through a path that doesn't follow it. Without both a normalized format and a unique constraint in the database, a "this number is already registered" check can't be trusted.

#Composite foreign keys

Another benefit of normalization is being able to state rules in the database. In KillReport every moon belongs to a planet, and every planet to a solar system. For speed, the moon also stores solar_system_id. But a foreign key on planet_id alone doesn't guarantee that the moon's system matches the planet's system. The answer is a composite foreign key:

CREATE TABLE planets (
  id              int PRIMARY KEY,
  solar_system_id int NOT NULL,
  UNIQUE (id, solar_system_id)
);

CREATE TABLE moons (
  id              int PRIMARY KEY,
  planet_id       int NOT NULL,
  solar_system_id int NOT NULL,
  FOREIGN KEY (planet_id, solar_system_id)
    REFERENCES planets (id, solar_system_id) ON DELETE CASCADE
);

Now a moon in the wrong system can't be inserted:

ERROR:  insert or update on table "moons" violates foreign key constraint "moons_planet_id_solar_system_id_fkey"
DETAIL:  Key (planet_id, solar_system_id)=(1, 200) is not present in table "planets".

If you can tie a copied column back to its source like this, the database protects the copy's consistency instead of your application code. That is denormalization at its safest.

#Why changing a schema later is expensive

Everything above has one thing in common: it was all discovered while the system was running. A schema change made later takes far more than the same change on an empty database. These are the three situations I run into most.

#A rename can turn into drop and recreate

Say you want to rename a table. How does your ORM read the difference in the schema? Prisma's migrate diff expresses a renamed table as a DROP TABLE followed by a CREATE TABLE. On an empty development database that doesn't matter. In production it deletes every row in that table.

When I renamed the table holding hourly system data in KillReport, I wrote the migration by hand:

ALTER TABLE system_kills RENAME TO system_activity;

That keeps the data in place, but it doesn't finish the job. When PostgreSQL renames a table, it doesn't touch the names of the objects attached to it:

                    Table "public.system_activity"
 Column |  Type   | Nullable |                 Default
--------+---------+----------+------------------------------------------
 id     | integer | not null | nextval('system_kills_id_seq'::regclass)
Indexes:
    "system_kills_pkey" PRIMARY KEY, btree (id)

The sequence, the primary key and the indexes still carry the old name. The ORM sees them as differences on the next comparison, so they have to be renamed one by one as well.

#A new column's default is a claim

I added a new column to the same table for jump data. My first instinct was DEFAULT 0. But the table already held the hourly records the server had collected until then, and I didn't know the jump data for those hours. DEFAULT 0 would have written "there were no jumps in that hour" on every old hour, and the seven-day chart would have shown a flat line that never happened.

ALTER TABLE system_activity ADD COLUMN ship_jumps integer;

I added the column with no default and allowed NULL. NULL means "I don't know"; 0 means "I know, and it's zero". For the old rows the right answer was the first.

If the column really must be required and the table is large, PostgreSQL has a known way to do it without locking the table for long. Add the column as nullable and backfill the old rows first. Then add the constraint as not yet validated and validate it in a separate step:

ALTER TABLE posts ADD COLUMN slug text;
-- backfill old rows (in batches on a large table)
UPDATE posts SET slug = 'p-' || id WHERE slug IS NULL;

ALTER TABLE posts ADD CONSTRAINT posts_slug_not_null
  CHECK (slug IS NOT NULL) NOT VALID;
ALTER TABLE posts VALIDATE CONSTRAINT posts_slug_not_null;

ALTER TABLE posts ALTER COLUMN slug SET NOT NULL;
ALTER TABLE posts DROP CONSTRAINT posts_slug_not_null;

NOT VALID applies the constraint only to newly written rows and doesn't scan the table. VALIDATE does the scan but doesn't block writes. In the last step, SET NOT NULL sees the validated constraint and doesn't scan the table a second time.

#A column added later changes the rules too

My biggest example of changing a schema after the fact comes from Biocidal. For the first six years, the data model had no concept of an "organization". When I decided to host several organizations in the same database, I had to add an organization id (company_id) to twelve tables.

Adding the column was the easy part. I added it as nullable, assigned every existing row to a default organization, then made it required. The hard part was that the meaning of "unique" changed. In a single-organization system, a vehicle's licence plate was unique across the whole table. In a multi-organization system, two different companies had to be able to register the same plate, but the same company must not register it twice:

DROP INDEX vehicle_plate_key;
CREATE UNIQUE INDEX vehicle_plate_company_id_key ON vehicle (plate, company_id);

You have to think this through for every unique constraint, one by one. On my first attempt I split too much by organization. The next day I rolled part of it back: I removed the organization id from reference tables that every organization shares, such as biocidal products. I also decided that an email address should stay unique across the whole system, not per organization. None of these decisions were technical; each one answered the question "who does this data belong to?"

The lesson: when you change an assumption in the data model, you have to rethink every constraint that rests on it.

#Tables the ORM doesn't know about

In KillReport, five tables deliberately live outside the Prisma schema: killmail_filters, three counter tables and a log table. They are managed with hand-written SQL because they use things Prisma can't express.

The problem: Prisma sees these tables in the database but can't find them in its schema. It treats that as drift, and prisma migrate dev offers to drop them at the first opportunity. At one point that offer amounted to 99,724 rows. Worse, prisma migrate status says "Database schema is up to date!" at the same time, so you can't see the danger by looking at the status command.

I took two measures. First, generate every migration with prisma migrate diff, remove the DROP TABLE lines by hand, and check that no DROP is left in the file before applying it. Second, when I saw a written rule wasn't enough, I used a hook to block the AI coding assistant I work with on this project from running migrate dev, migrate reset and db push with data loss accepted. A written rule that can be broken will be broken one day, especially when you're not always the one typing the commands.

The same happens in Biocidal, with indexes instead of tables. PostGIS geometry columns are defined as Unsupported(...) in the Prisma schema, and Prisma can't see the GiST indexes on them. migrate dev produces lines that drop those indexes every time; the notes at the top of the migration files are a reminder to strip them out each time.

If you work with an ORM and there is even one object in the database it doesn't know about, check today how your migration tool sees it.

#The questions I ask before adding a column

If I had to boil all of this down to a checklist:

• Where does this fact really live? If it's already in another table, why am I keeping it here again?
• If it's a copy, who updates it? If the answer isn't a single function or a single transaction, it will drift over time.
• Can this value change? If not, the copy is cheap. If it can, something has to keep it current.
• A copy, or a record of that moment? The price on an order line isn't a copy. Mixing the two up rewrites history.
• Does the type fit what the data means? A phone number, postcode or ID number is made of digits but isn't a number.
• Can the database enforce this rule? If I can state a rule with a foreign key, unique or check constraint, I don't leave it to application code.
• What is the right value for old rows? If I don't know, it's NULL, not a made-up default.

#What's next

Even with the right data model, queries can be slow. Next I'll write about indexes: why B-tree is enough most of the time, when GIN is needed for arrays and JSON, where GiST fits for spatial queries, and when partial indexes, which index only the rows that match a condition, pay off.