Database Design: Normalisasi, Relasi 1:1, 1:N, N:M

| SQL | sql, tutorial, design, normalisasi

Database Design: Normalisasi, Relasi 1:1, 1:N, N:M

Query cepat dimulai dari skema yang benar. Desain database yang buruk = data ganda, update ribet, dan anomali klasik. Artikel ini membahas relasi dan normalisasi — dua pilar desain relasional.

1. Relasi Antar Tabel

1:1 — Satu ke Satu

Satu baris di tabel A berpasangan dengan maksimal satu baris di tabel B.

Contoh:

  • users ↔ users_detail (foto, pengaturan jarang diakses)
  • karyawan ↔ karyawan_gaji (kolom sensitif dipisah keamanan)
CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    email    VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE users_profile (
    user_id     INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    bio         TEXT,
    foto_url    VARCHAR(255),
    tema        VARCHAR(20) DEFAULT 'light'
);

user_id sebagai PRIMARY KEY (sekaligus FK) memaksa tepat satu profil per user.

1:N — Satu ke Banyak

Paling umum: satu kategori → banyak produk; satu user → banyak order.

CREATE TABLE kategori (
    id   INTEGER PRIMARY KEY,
    nama VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE produk (
    id          INTEGER PRIMARY KEY,
    nama        VARCHAR(100) NOT NULL,
    kategori_id INTEGER REFERENCES kategori(id)
);

FK ada di sisi “banyak” (produk.kategori_id).

N:M — Banyak ke Banyak

Satu mahasiswa ↔ banyak course; satu course ↔ banyak mahasiswa → butuh tabel join.

CREATE TABLE mahasiswa (
    id   INTEGER PRIMARY KEY,
    nama VARCHAR(80) NOT NULL
);

CREATE TABLE course (
    id     INTEGER PRIMARY KEY,
    judul  VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    mahasiswa_id INTEGER REFERENCES mahasiswa(id) ON DELETE CASCADE,
    course_id    INTEGER REFERENCES course(id) ON DELETE CASCADE,
    nilai        DECIMAL(5,2),
    PRIMARY KEY (mahasiswa_id, course_id)
);
mahasiswa ──< enrollments >── course

Tabel join sering membawa atribut relasi (nilai, qty, peran, tanggal gabung) — sesuatu yang tidak punya tempat di salah satu ujung saja.

-- Contoh atribut relasi di order (N:M order ↔ produk)
CREATE TABLE order_items (
    order_id   INTEGER REFERENCES orders(id),
    produk_id  INTEGER REFERENCES produk(id),
    qty        INTEGER NOT NULL,
    harga_satuan DECIMAL(12,2) NOT NULL,   -- snapshot harga saat order
    PRIMARY KEY (order_id, produk_id)
);

harga_satuan di-snapshot — kalau harga produk berubah nanti, riwayat order tetap benar.


2. Anomali Tanpa Desain Baik

Tabel gabungan raksasa pesanan:

id pelanggan produk qty kategori_pelanggan
1 Andi Kopi 2 Gold
2 Andi Teh 1 Gold
3 Budi Kopi 1 Silver

Masalah:

  1. Update anomaly — ubah kategori Andi → harus ubah banyak baris; satu terlewat = data tidak konsisten
  2. Insert anomaly — pelanggan baru belum belanja → tidak bisa masuk tabel
  3. Delete anomaly — hapus order terakhir Budi → data Budi ikut hilang

Normalisasi memecah masalah ini.


3. Normalisasi

1NF — First Normal Form

Setiap sel = satu nilai atomik; tidak ada list di satu kolom.

-- ❌ 1NF violation
| nama  | no_hp                  |
| Andi  | 0812..., 0813...       |

-- ✅ 1NF: satu nomor per baris / tabel terpisah
| nama | no_hp     |
| Andi | 0812...   |

Aturan:

  • Kolom tidak boleh berisi array/komma-separated
  • Tidak ada group/repeating column (hp1, hp2, hp3 → tabel anak)

2NF — Second Normal Form

1NF + setiap kolom non-key bergantung penuh pada SELURUH primary key (bukan sebagian).

-- Composite PK: (order_id, produk_id)
| order_id | produk_id | qty | nama_pelanggan |   ← nama_pelanggan hanya
                                                bergantung order_id
                                                → 2NF violation

qty bergantung pada kombinasi (order, produk) ✅
nama_pelanggan hanya bergantung order_id ❌ → pindah ke tabel orders

2NF relevan saat PK-nya komposit. PK tunggal otomatis memenuhi 2NF.

3NF — Third Normal Form

2NF + tidak ada kolom non-key yang bergantung ke kolom non-key lain (transitive dependency).

| product_id | kategori_id | kategori_nama |
-- kategori_nama bergantung kategori_id, bukan product_id secara langsung
-- → pindah ke tabel kategori
produk(id, nama, kategori_id)
kategori(id, nama)

BCNF (Boyce-Codd)

Setiap determinan adalah kandidat key. Sedikit lebih ketat dari 3NF — untuk skema aneh di mana satu kolom non-key menentukan key. Untuk 99% skema bisnis, 3NF sudah cukup.


4. Contoh Sebelum vs Sesudah (3NF)

❌ Sebelum

CREATE TABLE pesanan (
    id              INTEGER PRIMARY KEY,
    nama_pelanggan  VARCHAR(80),
    email_pelanggan VARCHAR(255),
    kota_pelanggan  VARCHAR(50),
    nama_produk     VARCHAR(100),
    kategori_produk VARCHAR(50),
    qty             INTEGER,
    harga           DECIMAL(12,2)
);

✅ Sesudah 3NF

CREATE TABLE pelanggan (
    id    INTEGER PRIMARY KEY,
    nama  VARCHAR(80) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    kota  VARCHAR(50)
);

CREATE TABLE kategori (
    id   INTEGER PRIMARY KEY,
    nama VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE produk (
    id          INTEGER PRIMARY KEY,
    nama        VARCHAR(100) NOT NULL,
    kategori_id INTEGER REFERENCES kategori(id),
    harga       DECIMAL(12,2) NOT NULL
);

CREATE TABLE pesanan (
    id           INTEGER PRIMARY KEY,
    pelanggan_id INTEGER NOT NULL REFERENCES pelanggan(id),
    order_date   DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE order_items (
    order_id  INTEGER REFERENCES pesanan(id),
    produk_id INTEGER REFERENCES produk(id),
    qty       INTEGER NOT NULL CHECK (qty > 0),
    PRIMARY KEY (order_id, produk_id)
);

Update nama pelanggan → satu baris di satu tabel.


5. Denormalisasi — Kapan Melanggar Aturan?

Normalisasi penuh = banyak JOIN. Untuk read-heavy / laporan, kadang data sengaja digabung lagi (denormalisasi) demi kecepatan:

Strategi Contoh
Tabel aggregate produk.terjual_total dihitung periodik
Materialized view Snapshot laporan harian
Duplikasi terkontrol Simpan kota di orders untuk historis (bukan join users)
Caching / Redis Di luar database

Aturan main:

  1. Desain dulu benar (normal) untuk source of truth
  2. Optimasi read dengan turunan (index, view, materialized, cache)
  3. Bila menyalin data, tentukan siapa yang update dan kapan — duplikasi liar = anomali kembali

6. Checklist Desain Skema

  1. ✅ Setiap tabel punya primary key jelas
  2. ✅ Relasi diekspresikan FK (+ ON DELETE disadari)
  3. ✅ Tidak ada data ganda yang bisa dipindah ke tabel sendiri
  4. ✅ Kolom atomik (1NF), dependensi penuh (2NF), tanpa transitive (3NF)
  5. ✅ N:M punya tabel join + atribut relasi bila perlu
  6. ✅ Snapshot harga/tanggal di fakta transaksi (jangan selalu join ke master)
  7. ✅ Konsistensi penamaan (snake_case, *_id)
  8. ✅ Constraints dari artikel Constraints diterapkan

Singkat: kapan bentuk relasi?

Pola Gunakan
1:1 Pisah kolom sensitif / jarang diakses
1:N FK di sisi banyak
N:M Tabel join (+ atribut relasi)

7. Recap

Topik Poin
1:1 PK = FK di tabel kedua
1:N FK di sisi banyak
N:M Tabel join; PK komposit
Atribut relasi qty, nilai, peran → di tabel join
1NF Nilai atomik, tanpa list
2NF 1NF + tanpa partial dependency
3NF 2NF + tanpa transitive dependency
Anomali Update / insert / delete
Denormalisasi Opsi read-speed setelah desain benar

Penutup

Normalisasi bukan ritual akademis — ia mencegah data ganda yang bikin laporan salah dan update berantakan. Kuasai 1NF–3NF dan pola 1:1 / 1:N / N:M, lalu denormalisasi dengan sadar hanya saat kebutuhan performa menuntut.

Di artikel selanjutnya, kita lihat sisi proedural SQL: Stored Procedure, Function & Trigger — kode yang hidup di dalam database.


Bagikan via Email Bagikan via Facebook Bagikan via Twitter/X Bagikan via WhatsApp