Database Normalization: 1NF, 2NF, 3NF (MySQL Examples)

One of the biggest challenges in designing a database is avoiding data anomalies: unnecessary data duplication, difficulty updating, and inconsistency. The solution is Database Normalization — the process of organizing tables and columns so that the dependencies between data are logical and efficient. This article walks you through the normalization process, from an unnormalized form all the way to 3NF (Third Normal Form).

Why Is Normalization Important?

An unnormalized database is prone to three types of anomalies:

• Insert Anomaly: You can't insert certain data without also inserting other irrelevant data.
• Update Anomaly: Changing a single fact requires updating many rows, which is prone to inconsistency.
• Delete Anomaly: Deleting one record accidentally removes other information.

Initial Table (Unnormalized / 0NF)

Imagine an orders table that stores all information in a single table:

-- UNNORMALIZED table (an example of the problem)
-- Notice the product columns storing multiple values at once
CREATE TABLE pesanan_buruk (
    id_pesanan     INT,
    tanggal        DATE,
    nama_pelanggan VARCHAR(100),
    alamat         VARCHAR(200),
    kota           VARCHAR(50),
    produk_dibeli  VARCHAR(500),  -- "Laptop,Mouse,Keyboard" -- multiple values!
    harga_produk   VARCHAR(200),  -- "9500000,350000,750000" -- multiple values!
    total_bayar    DECIMAL(10,2)
);

The table above has many problems: the produk_dibeli and harga_produk columns contain multiple values at once, the customer data is repeated in every order, and there is no clear Primary Key.

1NF (First Normal Form) – Eliminate Multiple Values

1NF rule: Each column must store an atomic value (one value per cell), and each row must be unique.

-- After 1NF: one row per product within an order
CREATE TABLE pesanan_1nf (
    id_pesanan     INT NOT NULL,
    id_item        INT NOT NULL,  -- distinguishes the rows
    tanggal        DATE,
    nama_pelanggan VARCHAR(100),
    alamat         VARCHAR(200),
    kota           VARCHAR(50),
    nama_produk    VARCHAR(200),
    harga_produk   DECIMAL(10,2),
    jumlah         INT,
    PRIMARY KEY (id_pesanan, id_item)
) ENGINE=InnoDB;

INSERT INTO pesanan_1nf VALUES
(1, 1, '2024-01-10', 'Andi', 'Jl. Merdeka 10', 'Jakarta', 'Laptop ASUS', 9500000, 1),
(1, 2, '2024-01-10', 'Andi', 'Jl. Merdeka 10', 'Jakarta', 'Mouse Logitech', 350000, 2),
(2, 1, '2024-01-11', 'Budi', 'Jl. Sudirman 5', 'Bandung', 'Keyboard', 750000, 1);

This table is now in 1NF, but there is still a problem: the customer data (name, address, city) is repeated in every row of the same order.

2NF (Second Normal Form) – Eliminate Partial Dependency

2NF rule: The table is already in 1NF, and every non-key column must depend fully on the entire Primary Key (not just part of it). This applies when the Primary Key consists of more than one column (a composite key).

In the pesanan_1nf table, columns like nama_pelanggan, alamat, and tanggal depend only on id_pesanan, not on the combination of (id_pesanan, id_item). This is a partial dependency that must be removed.

-- After 2NF: separate the order header from the item details
CREATE TABLE pesanan_header (
    id_pesanan     INT AUTO_INCREMENT PRIMARY KEY,
    tanggal        DATE NOT NULL,
    nama_pelanggan VARCHAR(100) NOT NULL,
    alamat         VARCHAR(200),
    kota           VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE pesanan_detail (
    id_detail   INT AUTO_INCREMENT PRIMARY KEY,
    id_pesanan  INT NOT NULL,
    nama_produk VARCHAR(200) NOT NULL,
    harga       DECIMAL(10,2) NOT NULL,
    jumlah      INT NOT NULL DEFAULT 1,
    FOREIGN KEY (id_pesanan) REFERENCES pesanan_header(id_pesanan)
) ENGINE=InnoDB;

3NF (Third Normal Form) – Eliminate Transitive Dependency

3NF rule: The table is already in 2NF, and no non-key column depends on another non-key column (transitive dependency). In the pesanan_header table, the kota column might depend on kode_pos (rather than directly on id_pesanan). Likewise, customer data should be separated out so it isn't repeated in every order.

-- After 3NF: fully normalized tables

-- Customer table (customer master data)
CREATE TABLE pelanggan (
    id_pelanggan INT AUTO_INCREMENT PRIMARY KEY,
    nama         VARCHAR(100) NOT NULL,
    email        VARCHAR(150) UNIQUE NOT NULL,
    telepon      VARCHAR(15)
) ENGINE=InnoDB;

-- Address table (separated from the customer for flexibility)
CREATE TABLE alamat_pelanggan (
    id_alamat    INT AUTO_INCREMENT PRIMARY KEY,
    id_pelanggan INT NOT NULL,
    jalan        VARCHAR(200),
    kota         VARCHAR(50),
    provinsi     VARCHAR(50),
    kode_pos     CHAR(5),
    FOREIGN KEY (id_pelanggan) REFERENCES pelanggan(id_pelanggan)
) ENGINE=InnoDB;

-- Product table (product master data)
CREATE TABLE produk (
    id_produk INT AUTO_INCREMENT PRIMARY KEY,
    nama      VARCHAR(200) NOT NULL,
    kategori  VARCHAR(50),
    harga     DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

-- Order table
CREATE TABLE pesanan (
    id_pesanan   INT AUTO_INCREMENT PRIMARY KEY,
    id_pelanggan INT NOT NULL,
    id_alamat    INT,
    tanggal      DATE NOT NULL,
    status       ENUM('Pending','Diproses','Dikirim','Selesai') DEFAULT 'Pending',
    FOREIGN KEY (id_pelanggan) REFERENCES pelanggan(id_pelanggan),
    FOREIGN KEY (id_alamat) REFERENCES alamat_pelanggan(id_alamat)
) ENGINE=InnoDB;

-- Order Detail table
CREATE TABLE detail_pesanan (
    id_detail  INT AUTO_INCREMENT PRIMARY KEY,
    id_pesanan INT NOT NULL,
    id_produk  INT NOT NULL,
    jumlah     INT NOT NULL DEFAULT 1,
    harga_saat_beli DECIMAL(10,2) NOT NULL,  -- price snapshot
    FOREIGN KEY (id_pesanan) REFERENCES pesanan(id_pesanan),
    FOREIGN KEY (id_produk) REFERENCES produk(id_produk)
) ENGINE=InnoDB;

Summary of Normalization Rules

• 1NF: Atomic values, unique rows, a Primary Key exists.
• 2NF: 1NF + every non-key column depends fully on the Primary Key.
• 3NF: 2NF + no transitive dependency (a non-key column depending on another non-key column).

When Do You Not Need Full Normalization?

Although normalization is very important, there are times when denormalization is done deliberately for faster queries — especially in reporting systems (data warehouses). For example, storing total_harga directly in the order table even though it could be calculated from the details. This is a trade-off between data integrity and read speed.

Conclusion

Database normalization is an essential process for designing a data structure that is clean, efficient, and free of anomalies. Start by identifying all the functional dependencies between columns, then apply 1NF to eliminate multiple values, 2NF to eliminate partial dependencies, and 3NF to eliminate transitive dependencies. A well-normalized database will be far easier to maintain, more consistent, and easier to extend as your application grows.