Database normalization: from 1NF to 3NF

Why normalization is important

Repeated data across multiple rows makes changes risky: one change can be missed in another row and the data becomes inconsistent. Normalization is the process of structuring the table so that each fact is only stored in one place.

First normal form (1NF)

A table satisfies 1NF if each column stores a single value instead of a list merged into a single text.

-- Melanggar 1NF: satu kolom menyimpan banyak nilai
CREATE TABLE pesanan_buruk (
    id INT PRIMARY KEY,
    produk VARCHAR(255) -- contoh isi: 'Buku, Pensil, Pulpen'
);

-- Mengikuti 1NF: satu baris untuk satu produk
CREATE TABLE pesanan_item (
    id INT PRIMARY KEY,
    pesanan_id INT NOT NULL,
    produk VARCHAR(100) NOT NULL
);

Second normal form (2NF)

2NF applies to tables with joint keys. Each non-key column should depend on the entire key, not just a portion.

-- pesanan_id + produk_id adalah kunci gabungan
-- nama_produk hanya bergantung pada produk_id, bukan pesanan_id
-- sehingga sebaiknya dipindah ke tabel produk tersendiri
CREATE TABLE produk (
    produk_id INT PRIMARY KEY,
    nama_produk VARCHAR(100) NOT NULL
);

Third normal form (3NF)

3NF eliminates dependence between non-key columns. For example, the post_code specifying the city_name should be stored in the city table, not repeated in each customer row.

Normalization is not an absolute rule

Some reports actually require slightly denormalized data to make the query faster to read. Use normalization as a starting point, then loosen it on the parts that really need high reading performance.

practice;

Take a table in your app that has a column with a comma-separated list, then redesign it into a separate table following 1NF.