Most databases start life as a spreadsheet. Someone in operations has been tracking tenancy contracts in Excel for years, it works, and now it's your job to "just put it in the system".

So you create one table with one column per spreadsheet column. It works on day one. By month three, a tenant has two different phone numbers depending on which row you look at, a building's address is spelled four ways, and deleting an old contract somehow deleted the only record of a unit.

Database normalization is the cure, and it's far less academic than the textbook definitions make it sound. Let's normalize that spreadsheet step by step, using a property management example, then talk about when to break the rules on purpose.

The starting point: one big table

Here's our imported spreadsheet, simplified:

Already you can see the three classic problems, called anomalies:

• Update anomaly: the tenant's phone and the building's address are stored many times. Change one row, forget another, and the data now disagrees with itself.
• Insert anomaly: you can't record a new, empty unit, because a row needs a contract.
• Delete anomaly: delete the last contract in a building and the building's address disappears with it.

Normalization is just a set of steps that removes these, by making every fact live in exactly one place.

First normal form: one value per cell, no repeating columns

1NF says each cell holds a single value, and you don't have repeating groups of columns.

Our table breaks it twice. tenant_phones holds a list, so you can't easily search for a number or add a third one. And paid_jan, paid_feb… is a repeating group: what happens in year two? Add paid_jan_2?

The fix is to turn lists into rows in their own tables:

• A tenant_phones table, one row per phone number.
• A payments table, one row per payment, with a contract_id, a period, an amount and a paid date.

The test: if you ever find yourself splitting a cell by commas in code, or adding numbered columns, 1NF is asking for a new table.

Second normal form: depend on the whole key

2NF only matters for tables with a composite primary key (a key made of more than one column). It says every other column must depend on the whole key, not just part of it.

Say one contract can cover several units, like a shop plus a storage room. We create a contract_units table with the key (contract_id, unit_id):

monthly_rent is fine: the rent for this unit on this contract depends on both columns. But unit_floor depends only on unit_id. A unit is on the same floor no matter which contract it's in. That's a partial dependency, and it brings back the update anomaly.

The fix: unit_floor moves to a units table, where it depends on the unit's own key.

Third normal form: nothing but the key

3NF says a non-key column shouldn't depend on another non-key column. These are called transitive dependencies.

In our units table we have building and building_address. The address doesn't depend on the unit; it depends on the building, which depends on the unit. So the building gets its own table, and units keep only a building_id.

The same thinking pulls the tenant's details out of the contract. A tenant's name and email are facts about the tenant, not about each contract they sign.

There's a famous summary of the first three forms: every column should depend on the key, the whole key, and nothing but the key.

The normalized schema

Here's where we end up, as simplified Laravel migrations:

Schema::create('buildings', function (Blueprint $t) {
    $t->id();
    $t->string('name');
    $t->string('address');
});

Schema::create('units', function (Blueprint $t) {
    $t->id();
    $t->foreignId('building_id')->constrained();
    $t->string('code')->unique();
    $t->string('floor');
});

Schema::create('contracts', function (Blueprint $t) {
    $t->id();
    $t->string('number')->unique();
    $t->foreignId('tenant_id')->constrained();
    $t->date('starts_on');
    $t->date('ends_on');
});

Plus tenants, tenant_phones, contract_units (with the rent per unit) and payments. More tables, yes. But now the address is fixed in one place, an empty unit can exist, and deleting a contract can't take a building with it.

Getting data back together is a join away, and Eloquent relationships make that pleasant: $contract->units->first()->building->address, with eager loading to avoid N+1 queries.

When to denormalize on purpose

Normalization optimises for correct writes. Sometimes reads matter more, and it's fine to bend the rules deliberately:

• Historical snapshots, as in the callout above: invoices, signed documents, audit logs.
• Counters and totals that would be expensive to compute on every page load, like the outstanding balance on a contract. Keep one source of truth and update the copy in the same transaction (or recalculate it in a job).
• Reporting tables that flatten many joins into one wide table for dashboards, rebuilt on a schedule.
• Search indexes and caches, which are denormalized copies by design.

The rule that keeps this safe: every copy has an owner. You always know which table is the truth, and how and when the copy is refreshed. Denormalization without an owner is just the original spreadsheet with extra steps.

The short version

• 1NF: one value per cell; lists become rows in their own table.
• 2NF: with a composite key, every column depends on the whole key.
• 3NF: columns don't depend on other non-key columns.
• Normalize by default. Denormalize for a measured reason, with a clear source of truth.

What's the strangest "spreadsheet turned database table" you've had to clean up? I suspect everyone has met a notes column holding three different kinds of data.