All articles
165 articles · updated weekly See our Tools
All articles
Tutorials

How to model tables without building a database that's impossible to maintain

Normalization without dogma, one column per fact, real foreign keys and honest types: how to design a schema that survives time and avoids the costly anti-patterns.

How to model tables without building a database that's impossible to maintain
COVER · Tutorials

Almost every hard-to-maintain database started with a schema that looked clever. Someone wanted to save a table and stuffed three phone numbers into a contatos column separated by commas. Someone stored price as FLOAT because "it's a number". Someone decided birth_date could be VARCHAR because the format varied. Six months later nobody can run a report without an improvised SPLIT, and every deploy is a prayer. The problem is rarely a lack of advanced knowledge — it's the refusal to model the truth of the domain before reaching for a shortcut.

This piece is about designing tables that survive time: normalization without dogma, honest types, integrity in the database, and the anti-patterns that look like savings and charge interest.

Normalization is a starting point, not a religion

Third normal form (3NF) solves most of your problems: every column depends on the key, the whole key, and nothing but the key. It's not academic theory — it's the defense against duplicated data drifting out of sync. If the customer name is copied across a thousand order rows, the day they marry and change their surname you have a thousand conflicting truths.

Start at 3NF by default. Model entities as they exist in the world: a customer is one table, an order is another, and the relationship between them is a foreign key. Don't denormalize "for performance" before you have a measured performance problem. Speculative optimization is the most expensive form of complexity: you pay the maintenance cost every day and may never reap the gain. Model for truth first; optimize later, with real data and an EXPLAIN in hand.

A column is one fact

The most violated and the cheapest rule to follow: every column holds exactly one atomic fact. The moment you're thinking of putting "11999999999,1133334444" into a column, or an improvised JSON with a list you'll filter later, stop. That's a table in disguise.

-- Wrong: the column hides a 1:N relationship
CREATE TABLE customer (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   TEXT NOT NULL,
  phones TEXT  -- "11999999999,1133334444" — impossible to index, validate or query
);

-- Right: the relationship becomes a table
CREATE TABLE phone (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customer (id) ON DELETE CASCADE,
  number      TEXT   NOT NULL,
  kind        TEXT   NOT NULL DEFAULT 'mobile'
);

JSON has its place — genuinely schema-less attributes, integration payloads, settings you never query by field. But if you're going to WHERE, JOIN or ORDER BY a piece of that JSON, it wanted to be a column or a table. A CSV field never has a place.

Real keys and real foreign keys

Referential integrity is the database's responsibility, not the application's. "But we validate it in the code" is not enough: you have migrations, ad-hoc scripts, two services writing to the same table, and the intern running an UPDATE in the production console at eleven at night. The foreign key is the only defense that never sleeps.

Declare the foreign key with the right action: ON DELETE CASCADE when the child doesn't exist without the parent (an order item without an order is garbage), ON DELETE RESTRICT when deleting the parent should be blocked, ON DELETE SET NULL when the relationship is optional. Every primary key must exist and be stable — prefer a surrogate key (IDENTITY or UUID) over a natural key that can change. A national ID looks immutable until the day it was typed wrong.

The right types, always

The data type is the first validation layer and you get it for free. Getting it wrong here contaminates everything downstream.

  • Money is never FLOAT. Floating point doesn't represent 0.10 exactly; your sum of cents will drift. Use NUMERIC(12,2) (or integer cents).
  • A date is DATE/TIMESTAMPTZ, not VARCHAR. A string doesn't sort by time, doesn't add intervals, and accepts "2026-02-31". With TIMESTAMPTZ you also settle time zones once and for all.
  • A boolean is BOOLEAN, not CHAR(1) with 'Y'/'N' or an integer 0/1.
  • A short, stable enumeration becomes a native enum or a lookup table with a foreign key — never free text that accepts 'active', 'Active' and 'actve'.

When reviewing a long CREATE TABLE full of types and constraints, formatting the SQL aligns the columns and makes type mistakes jump out before they reach production.

NULL with intent

NULL means "unknown" or "not applicable" — not "empty", and certainly not "zero". This distinction is semantic and has consequences: NULL propagates through comparisons (NULL = NULL is false), is ignored by aggregations, and breaks naive assumptions in code.

Decide column by column. If cancelled_at is NULL, the order wasn't cancelled — NULL here carries information and is correct. But if quantity can be NULL, ask what that really means; you probably want NOT NULL DEFAULT 0. Every nullable column is one more branch that every JOIN and every report has to handle. Make NOT NULL the default and justify each exception.

Consistent naming and indexes where search happens

Pick a convention and don't negotiate: snake_case, tables singular (customer) or plural (customers) — it doesn't matter which, as long as it's one across the whole schema. Foreign keys as <entity>_id (customer_id). No cust_cd, customerId and customer_code coexisting. Naming inconsistency is cognitive friction in every query anyone writes.

Indexes come after the honest schema, but not long after. Every foreign key column you use in a JOIN should be indexed — Postgres does not create that index for you, contrary to what many people assume. Also index the columns that appear in WHERE and ORDER BY of real queries. Don't index everything: an index speeds reads and penalizes writes, so let pg_stat and execution plans guide you. If you enjoy the details of how each engine handles indexes and types, it's worth reading PostgreSQL vs MySQL before deciding.

CHECK constraints as a safety net

When the type isn't enough to express the rule, the CHECK steps in. It's the domain invariant written where it can't be bypassed.

CREATE TABLE order_item (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id  BIGINT  NOT NULL REFERENCES "order" (id) ON DELETE CASCADE,
  quantity  INTEGER NOT NULL CHECK (quantity > 0),
  price     NUMERIC(12,2) NOT NULL CHECK (price >= 0),
  discount  NUMERIC(12,2) NOT NULL DEFAULT 0
            CHECK (discount >= 0 AND discount <= price)
);

Negative quantity, negative price, discount greater than price — none of these states should exist, and the CHECK guarantees they won't, no matter where the data comes from. It's cheap to declare and priceless when it catches the bug the application forgot to validate.

The anti-patterns that charge interest

Three recurring traps:

Generic EAV (entity-attribute-value): an attributes (entity_id, key, value) table that promises infinite flexibility and delivers unreadable queries, lost types (everything becomes text) and zero integrity. Almost always you wanted real columns, or a JSON field in a corner, not a meta-schema reinventing the database inside the database.

The god table: a data table with sixty columns, half of them NULL on any row, mixing customer, order and payment. It's the 3NF that never happened. Break it up by entity.

Denormalizing too early "for performance": you copy the product name into the order line "to avoid a JOIN", and now you have two places to update and one of them will fall behind. Denormalization is a conscious decision, made after measuring, with an explicit synchronization path — not a reflex.

Frequently asked questions

Do I always need to reach 3NF?

3NF is a good starting point, not a sacred goal. It eliminates most of the redundancy that causes inconsistent data, which is why it works as a default. Denormalizing beyond it is a conscious decision, made after measuring a real bottleneck — not the starting point.

Should I use a natural key or a surrogate key?

Prefer a surrogate key (IDENTITY or UUID) in most cases. Natural keys like a national ID or email look stable until the day they change or were entered wrong, and then the change propagates to every foreign key pointing at them. A surrogate key gives you an identifier that never has to change.

Can I store JSON in a column instead of creating tables?

You can, for genuinely unstructured data or data you never query into (a log payload, loose settings). The mistake is using JSON to dodge modeling a clear relationship: if you need to filter, join, or validate those fields, they want to be real columns and tables, with types and foreign keys.

When should I create an index?

On every foreign key column used in a JOIN (Postgres doesn't create that index for you) and on the columns that appear in WHERE and ORDER BY of real queries. Don't index everything as a precaution: an index speeds up reads and penalizes writes. Let execution plans and statistics guide what actually needs an index.

What to take from here

An honest schema is easier to maintain than a clever one. Model entities as they exist in the domain, let each column hold one fact, and make the database defend its own integrity with foreign keys, the right types and CHECK constraints. Normalize by default and denormalize only when a measured number — not a hunch — says it's worth it. The day the schema becomes boring and predictable is the day it stops waking you up in the middle of the night.

RD
Author
Rafael Duarte
Backend developer with a background in fintech and B2B SaaS — worked on teams that scaled APIs from zero to millions of requests. Carries enough production scars to hold strong opinions on tools, patterns and architecture decisions. Not an academic: he read the UUID RFC when he had to choose between v4 and v7 for a write-heavy table.
View profile