Database Design & Normalisasi

📋 Daftar Isi

Pengenalan Database Design
ER Diagram (Entity-Relationship)
Normalisasi: 1NF (First Normal Form)
Normalisasi: 2NF (Second Normal Form)
Normalisasi: 3NF (Third Normal Form)
Tipe Relationships
Index Design Strategy
Denormalization: Kapan & Mengapa
Schema Design Patterns
Quiz Pemahaman

1. Pengenalan Database Design

Database design adalah proses merancang struktur database yang efisien, konsisten, dan scalable untuk menyimpan data aplikasi. Desain database yang baik akan menghemat waktu pengembangan, mencegah inkonsistensi data, dan memastikan performa query tetap cepat seiring bertambahnya data.

Database design yang buruk adalah sumber bug paling mahal dalam pengembangan software. Sebuah studi menunjukkan bahwa 60-80% masalah performa database berakar pada desain awal yang tidak tepat — bukan pada query atau hardware. Oleh karena itu, memahami prinsip database design adalah keterampilan wajib bagi setiap developer.

Tahapan Database Design

Tahap	Deskripsi	Output
1. Requirements Analysis	Identifikasi data apa yang perlu disimpan dan bagaimana data akan diakses	Dokumen kebutuhan data
2. Conceptual Design	Buat model abstrak dari entitas dan relasi (tanpa memikirkan implementasi)	ER Diagram
3. Logical Design	Konversi model konseptual ke schema relasional dengan normalisasi	Schema tabel, primary/foreign keys
4. Physical Design	Implementasi fisik — pilih RDBMS, tentukan indexes, partisi, dll.	DDL SQL, indexes, storage engine
5. Review & Iterasi	Validasi desain, uji performa, dan iterasi jika diperlukan	Optimasi & dokumentasi final

💡 Tips Desain

Sebelum membuat tabel, selalu tanyakan: "Bagaimana data ini akan di-query?" Desain database yang optimal sangat bergantung pada pola akses data — bukan hanya struktur datanya. Misalnya, data yang sering di-join sebaiknya tidak terlalu ter-normalisasi.

2. ER Diagram (Entity-Relationship)

ER Diagram (Entity-Relationship Diagram) adalah visualisasi yang menunjukkan entitas (objek/data), atribut (properti), dan relasi (hubungan) antar entitas dalam sistem. ER Diagram adalah langkah pertama dalam database design sebelum membuat tabel SQL.

Komponen ER Diagram

Komponen	Simbol	Penjelasan	Contoh
Entity	□ Kotak	Objek/entitas yang menyimpan data	Customer, Product, Order
Atribut	○ Oval	Properti dari entitas	nama, email, harga
Relationship	◇ Belah ketupat	Hubungan antar entitas	"memesan", "memiliki"
Primary Key	Atribut bergaris bawah	Identifier unik	customer_id, order_id
Cardinality	Angka di garis	Jumlah relasi (1:1, 1:N, M:N)	1 customer memiliki N orders

Contoh ER Diagram: E-Commerce

Diagram: ER Diagram Sistem E-Commerce

┌─────────────────────────────────────────────────────────────────────┐
│                    ER DIAGRAM — E-COMMERCE                          │
│                                                                     │
│   ┌─────────────┐       ┌─────────────┐       ┌─────────────┐     │
│   │  CUSTOMER   │       │   ORDER     │       │   PRODUCT   │     │
│   ├─────────────┤       ├─────────────┤       ├─────────────┤     │
│   │ *customer_id│──┐    │ *order_id   │    ┌──│ *product_id │     │
│   │  nama       │  │    │  customer_id│    │  │  nama_produk│     │
│   │  email      │  │    │  tanggal    │    │  │  harga      │     │
│   │  telepon    │  │    │  total      │    │  │  stok       │     │
│   │  alamat     │  │    │  status     │    │  │  kategori_id│     │
│   └─────────────┘  │    └──────┬──────┘    │  └─────────────┘     │
│                    │           │           │                       │
│                    │  1    ┌───┴───┐    N  │                       │
│                    └──────►│memesan│◄──────┘                       │
│                            └───────┘                               │
│                                 │                                  │
│                                 │ N                                │
│                                 ▼                                  │
│                          ┌─────────────┐                           │
│                          │ ORDER_ITEM  │                           │
│                          ├─────────────┤                           │
│                          │ *order_item_id                          │
│                          │  order_id   │                           │
│                          │  product_id │                           │
│                          │  jumlah     │                           │
│                          │  subtotal   │                           │
│                          └─────────────┘                           │
│                                                                     │
│   ┌─────────────┐                                                   │
│   │  CATEGORY   │                                                   │
│   ├─────────────┤                                                   │
│   │ *category_id│──────────── 1:N ──────────► PRODUCT              │
│   │  nama_kategori                                                  │
│   │  deskripsi  │                                                   │
│   └─────────────┘                                                   │
└─────────────────────────────────────────────────────────────────────┘

Contoh Implementasi SQL

SQL — DDL

-- Tabel Category
CREATE TABLE categories (
    category_id   INT PRIMARY KEY AUTO_INCREMENT,
    nama_kategori VARCHAR(100) NOT NULL UNIQUE,
    deskripsi     TEXT,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel Customer
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    nama        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) NOT NULL UNIQUE,
    telepon     VARCHAR(20),
    alamat      TEXT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel Product
CREATE TABLE products (
    product_id  INT PRIMARY KEY AUTO_INCREMENT,
    nama_produk VARCHAR(200) NOT NULL,
    harga       DECIMAL(12, 2) NOT NULL,
    stok        INT NOT NULL DEFAULT 0,
    category_id INT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Tabel Order
CREATE TABLE orders (
    order_id    INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    tanggal     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total       DECIMAL(15, 2) NOT NULL DEFAULT 0,
    status      ENUM('pending','diproses','dikirim','selesai','batal')
                DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

-- Tabel Order Item (junction / detail)
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id      INT NOT NULL,
    product_id    INT NOT NULL,
    jumlah        INT NOT NULL DEFAULT 1,
    subtotal      DECIMAL(12, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE RESTRICT
);

-- Index untuk query yang sering digunakan
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_tanggal ON orders(tanggal);
CREATE INDEX idx_products_category ON products(category_id);

3. Normalisasi: 1NF (First Normal Form)

Normalisasi adalah proses mengorganisasi data dalam database untuk mengurangi redundansi (duplikasi) dan meningkatkan integritas data. Tujuan utama normalisasi adalah memastikan setiap fakta hanya disimpan di satu tempat, sehingga tidak ada inkonsistensi saat data diperbarui.

⚠️ Masalah Tanpa Normalisasi

Jika customer "Budi" memesan 5 produk dan kita menyimpan semua data dalam satu tabel, setiap kali Budi pindah alamat, kita harus meng-update 5 baris sekaligus. Jika terlewat 1 baris, data menjadi tidak konsisten — ini disebut update anomaly.

Aturan 1NF

Sebuah tabel memenuhi First Normal Form (1NF) jika:

Setiap kolom berisi hanya satu nilai atomik (tidak ada array, list, atau nilai berganda)
Setiap kolom berisi tipe data yang sama di seluruh baris
Setiap baris adalah unik (ada primary key)
Tidak ada grup berulang (repeating groups)

Contoh: Pelanggaran 1NF

SQL — Tabel TIDAK 1NF

-- ❌ TABEL INI TIDAK MEMENUHI 1NF
-- Masalah: kolom "hobi" berisi banyak nilai dalam satu sel

SELECT * FROM customers_bad;

+----+----------+----------------------------+
| id | nama     | hobi                        |
+----+----------+----------------------------+
| 1  | Budi     | coding, gaming, memasak     |  ← Banyak nilai!
| 2  | Ani      | membaca, olahraga           |  ← Banyak nilai!
+----+----------+----------------------------+

-- Masalah tambahan:
-- - Sulit query: "cari semua customer yang hobi coding"
-- - Sulit update salah satu hobi saja
-- - Tidak bisa sorting/filter per hobi
-- - Jumlah hobi berbeda per baris

Perbaikan ke 1NF

SQL — Tabel 1NF

-- ✅ TABEL INI MEMENUHI 1NF
-- Pisahkan data ke tabel terpisah

-- Tabel customers (data utama)
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    nama        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) NOT NULL UNIQUE
);

-- Tabel hobbies (satu hobi per baris)
CREATE TABLE customer_hobbies (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    hobi        VARCHAR(50) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Sekarang mudah query!
SELECT c.nama, ch.hobi
FROM customers c
JOIN customer_hobbies ch ON c.customer_id = ch.customer_id
WHERE ch.hobi = 'coding';

-- Mudah update, delete, atau tambah hobi
INSERT INTO customer_hobbies (customer_id, hobi) VALUES (1, 'berenang');

4. Normalisasi: 2NF (Second Normal Form)

Sebuah tabel memenuhi Second Normal Form (2NF) jika:

Sudah memenuhi 1NF
Tidak ada partial dependency — setiap kolom non-key bergantung pada seluruh primary key, bukan hanya sebagian

📖 Apa itu Partial Dependency?

Partial dependency terjadi ketika tabel memiliki composite primary key (gabungan 2+ kolom) dan ada kolom yang hanya bergantung pada salah satu kolom primary key saja. Masalah ini hanya relevan untuk tabel dengan composite key.

Contoh: Pelanggaran 2NF

SQL — Tabel TIDAK 2NF

-- ❌ TABEL INI TIDAK MEMENUHI 2NF
-- Composite PK: (order_id, product_id)

CREATE TABLE order_items_bad (
    order_id    INT,
    product_id  INT,
    jumlah      INT,
    subtotal    DECIMAL(12,2),
    -- Kolom berikut PARTIAL DEPENDENCY:
    nama_produk VARCHAR(200),    -- Hanya bergantung product_id
    harga       DECIMAL(12,2),   -- Hanya bergantung product_id
    nama_customer VARCHAR(100),  -- Hanya bergantung order_id
    PRIMARY KEY (order_id, product_id)
);

-- Masalah:
-- 1. nama_produk & harga bergantung HANYA pada product_id
--    (bukan pada kombinasi order_id + product_id)
-- 2. nama_customer bergantung HANYA pada order_id
-- 3. Jika harga produk berubah, harus update BANYAK baris
-- 4. Redundansi: "Laptop ASUS" tercatat di setiap order_item

Perbaikan ke 2NF

SQL — Tabel 2NF

-- ✅ PISAHKAN ke tabel yang lebih spesifik

-- Tabel orders (data order)
CREATE TABLE orders (
    order_id    INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    tanggal     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Tabel products (data produk)
CREATE TABLE products (
    product_id  INT PRIMARY KEY AUTO_INCREMENT,
    nama_produk VARCHAR(200) NOT NULL,
    harga       DECIMAL(12, 2) NOT NULL,
    stok        INT DEFAULT 0
);

-- Tabel order_items — HANYA data yang bergantung pada
-- kombinasi (order_id, product_id)
CREATE TABLE order_items (
    order_id    INT,
    product_id  INT,
    jumlah      INT NOT NULL,
    subtotal    DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Sekarang harga produk hanya disimpan SATU kali di tabel products
-- Update harga → otomatis konsisten di semua order_item

5. Normalisasi: 3NF (Third Normal Form)

Sebuah tabel memenuhi Third Normal Form (3NF) jika:

Sudah memenuhi 2NF
Tidak ada transitive dependency — kolom non-key tidak bergantung pada kolom non-key lainnya

📖 Apa itu Transitive Dependency?

Transitive dependency terjadi ketika kolom A menentukan kolom B, dan kolom B menentukan kolom C — sehingga secara tidak langsung (transitif) A menentukan C. Kolom C harus dipindah ke tabel terpisah.

Contoh: Pelanggaran 3NF

SQL — Tabel TIDAK 3NF

-- ❌ TABEL INI TIDAK MEMENUHI 3NF
-- Memenuhi 1NF dan 2NF, tapi ada transitive dependency

CREATE TABLE employees_bad (
    employee_id INT PRIMARY KEY,
    nama        VARCHAR(100),
    email       VARCHAR(150),
    -- Transitive dependency:
    dept_id     INT,
    dept_nama   VARCHAR(100),    ← Bergantung pada dept_id
    dept_lokasi VARCHAR(100)     ← Bergantung pada dept_id
);

-- Transitive chain:
-- employee_id → dept_id → dept_nama, dept_lokasi
-- Karena dept_nama & dept_lokasi bergantung pada dept_id
-- (bukan langsung pada employee_id), ini melanggar 3NF.

-- Masalah:
-- 1. dept_nama & dept_lokasi diulang untuk setiap employee di dept tsb
-- 2. Jika dept pindah lokasi, harus update BANYAK baris
-- 3. Risiko inkonsistensi jika terlewat update salah satu baris

Perbaikan ke 3NF

SQL — Tabel 3NF

-- ✅ PISAHKAN data department ke tabel terpisah

-- Tabel departments
CREATE TABLE departments (
    dept_id   INT PRIMARY KEY AUTO_INCREMENT,
    nama_dept VARCHAR(100) NOT NULL,
    lokasi    VARCHAR(100)
);

-- Tabel employees — hanya punya foreign key ke departments
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    nama        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) NOT NULL UNIQUE,
    dept_id     INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL
);

-- Sekarang:
-- 1. Data dept hanya disimpan SATU kali di tabel departments
-- 2. Pindah kantor → update 1 baris saja
-- 3. Konsisten dan efisien

-- Query dengan JOIN
SELECT e.nama, e.email, d.nama_dept, d.lokasi
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

Ringkasan Normal Forms

Diagram: Ringkasan Normalisasi

┌──────────────────────────────────────────────────────────────────┐
│              RINGKASAN NORMAL FORMS                              │
│                                                                  │
│  ┌─────────────────────────────────────────────────────────┐    │
│  │ 1NF: Data Atomik                                        │    │
│  │  • Setiap kolom berisi SATU nilai                       │    │
│  │  • Tidak ada array/list dalam satu sel                   │    │
│  │  • Semua baris unik (ada primary key)                    │    │
│  └──────────────────────┬──────────────────────────────────┘    │
│                         ▼                                        │
│  ┌─────────────────────────────────────────────────────────┐    │
│  │ 2NF: Hapus Partial Dependency                           │    │
│  │  • Sudah 1NF                                             │    │
│  │  • Non-key columns bergantung pada SELURUH PK            │    │
│  │  • Hanya relevan untuk tabel dengan composite key        │    │
│  └──────────────────────┬──────────────────────────────────┘    │
│                         ▼                                        │
│  ┌─────────────────────────────────────────────────────────┐    │
│  │ 3NF: Hapus Transitive Dependency                        │    │
│  │  • Sudah 2NF                                             │    │
│  │  • Non-key columns tidak bergantung pada non-key lain    │    │
│  │  • A → B → C, maka C harus dipisah ke tabel lain        │    │
│  └─────────────────────────────────────────────────────────┘    │
│                                                                  │
│  Catatan: Ada BCNF, 4NF, 5NF, dll., tapi 3NF sudah cukup      │
│  untuk sebagian besar aplikasi nyata.                            │
└──────────────────────────────────────────────────────────────────┘

6. Tipe Relationships

Dalam database relasional, ada tiga tipe hubungan (relationships) utama antar tabel. Memahami hubungan ini sangat penting untuk merancang schema yang benar.

One-to-One (1:1)

Satu record di tabel A berkorespondensi dengan tepat satu record di tabel B.

SQL — One-to-One

-- Contoh: Setiap user punya tepat satu profile
CREATE TABLE users (
    user_id    INT PRIMARY KEY AUTO_INCREMENT,
    username   VARCHAR(50) NOT NULL UNIQUE,
    email      VARCHAR(150) NOT NULL UNIQUE,
    password   VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_profiles (
    profile_id   INT PRIMARY KEY AUTO_INCREMENT,
    user_id      INT NOT NULL UNIQUE,  -- UNIQUE = 1:1
    bio          TEXT,
    foto_url     VARCHAR(500),
    tanggal_lahir DATE,
    no_ktp       VARCHAR(20) UNIQUE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE
);

-- Query 1:1
SELECT u.username, u.email, p.bio, p.foto_url
FROM users u
JOIN user_profiles p ON u.user_id = p.user_id;

One-to-Many (1:N)

Satu record di tabel A bisa memiliki banyak record di tabel B, tapi setiap record di tabel B hanya milik satu record di tabel A. Ini adalah relasi paling umum.

SQL — One-to-Many

-- Contoh: Satu author bisa menulis banyak post
CREATE TABLE authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    nama      VARCHAR(100) NOT NULL,
    bio       TEXT
);

CREATE TABLE posts (
    post_id    INT PRIMARY KEY AUTO_INCREMENT,
    author_id  INT NOT NULL,        -- FK di tabel "many"
    title      VARCHAR(300) NOT NULL,
    content    TEXT,
    status     ENUM('draft','published') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
        ON DELETE CASCADE
);

-- Tambahkan index untuk performa JOIN
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_status ON posts(status, created_at);

-- Query 1:N
SELECT a.nama, COUNT(p.post_id) AS total_posts
FROM authors a
LEFT JOIN posts p ON a.author_id = p.author_id
GROUP BY a.author_id, a.nama
ORDER BY total_posts DESC;

Many-to-Many (M:N)

Satu record di tabel A bisa memiliki banyak record di tabel B, dan sebaliknya. Memerlukan junction table (tabel penghubung).

SQL — Many-to-Many

-- Contoh: Satu mahasiswa bisa ambil banyak mata kuliah,
-- dan satu mata kuliah diambil oleh banyak mahasiswa

CREATE TABLE mahasiswa (
    mhs_id  INT PRIMARY KEY AUTO_INCREMENT,
    nim     VARCHAR(20) NOT NULL UNIQUE,
    nama    VARCHAR(100) NOT NULL
);

CREATE TABLE mata_kuliah (
    mk_id    INT PRIMARY KEY AUTO_INCREMENT,
    kode_mk  VARCHAR(10) NOT NULL UNIQUE,
    nama_mk  VARCHAR(200) NOT NULL,
    sks      INT NOT NULL
);

-- Junction table — menghubungkan M:N
CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    mhs_id        INT NOT NULL,
    mk_id         INT NOT NULL,
    semester      VARCHAR(10) NOT NULL,
    nilai         CHAR(2),
    enrolled_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (mhs_id, mk_id, semester),  -- Satu mhs ambil 1 mk per sem
    FOREIGN KEY (mhs_id) REFERENCES mahasiswa(mhs_id),
    FOREIGN KEY (mk_id) REFERENCES mata_kuliah(mk_id)
);

CREATE INDEX idx_enroll_mhs ON enrollments(mhs_id);
CREATE INDEX idx_enroll_mk ON enrollments(mk_id);

-- Query: Lihat mata kuliah yang diambil mahasiswa
SELECT m.nim, m.nama, mk.nama_mk, mk.sks, e.nilai
FROM enrollments e
JOIN mahasiswa m ON e.mhs_id = m.mhs_id
JOIN mata_kuliah mk ON e.mk_id = mk.mk_id
WHERE e.semester = '2026-1';

7. Index Design Strategy

Index bukan sekadar "tambah index di semua kolom" — butuh strategi. Index yang berlebihan justru memperlambat operasi INSERT/UPDATE karena setiap perubahan data juga harus meng-update index.

Kapan Membuat Index

Situasi	Jenis Index	Contoh
Kolom di WHERE clause	Single / B-tree	`WHERE email = '...'`
Kolom di JOIN	Foreign key index	`ON orders.customer_id`
Kolom di ORDER BY	Single / Composite	`ORDER BY created_at DESC`
Kombinasi filter	Composite index	`WHERE status='active' AND kota='JKT'`
Pencarian teks	Full-text index	`WHERE MATCH(title) AGAINST('...')`
Kolom unik	Unique index	`email, username, nim`

Composite Index: Aturan Leftmost Prefix

SQL — Composite Index

-- Composite index pada (kota, status, created_at)
CREATE INDEX idx_orders_composite
ON orders(kota, status, created_at);

-- ✅ Query ini MEMANFAATKAN index (leftmost prefix)
SELECT * FROM orders WHERE kota = 'Jakarta';
SELECT * FROM orders WHERE kota = 'Jakarta' AND status = 'selesai';
SELECT * FROM orders WHERE kota = 'Jakarta' AND status = 'selesai'
  AND created_at > '2026-01-01';

-- ❌ Query ini TIDAK memanfaatkan index (skip kota)
SELECT * FROM orders WHERE status = 'selesai';
SELECT * FROM orders WHERE status = 'selesai' AND created_at > '2026-01-01';

-- ❌ Query ini HANYA memanfaatkan sebagian index
SELECT * FROM orders WHERE kota = 'Jakarta' AND created_at > '2026-01-01';
-- Hanya "kota" yang di-index, created_at di-scan manual

-- Tips: Susun kolom index dari yang paling selektif ke paling umum
-- Kolom yang sering dipakai di filter → paling kiri

Monitoring Index dengan EXPLAIN

SQL — EXPLAIN

-- MySQL: Analisis query dengan EXPLAIN
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'selesai';

-- Perhatikan kolom:
-- type: ALL = full scan (buruk), ref/range = pakai index (bagus)
-- key: index yang digunakan
-- rows: estimasi baris yang di-scan
-- Extra: Using index, Using filesort, Using temporary

-- PostgreSQL: EXPLAIN ANALYZE (lebih detail)
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42 AND status = 'selesai';

-- Perintah berguna untuk cek index
SHOW INDEX FROM orders;           -- MySQL
\di+ orders_*                     -- PostgreSQL

8. Denormalization: Kapan & Mengapa

Denormalization adalah proses sengaja menambahkan redundansi data ke dalam database untuk meningkatkan performa query. Ini adalah kebalikan dari normalisasi dan dilakukan setelah desain ter-normalisasi sudah stabil.

⚠️ Kapan Melakukan Denormalisasi

Denormalisasi adalah optimasi yang dilakukan karena alasan performa yang terbukti dengan benchmark, bukan "karena bisa". Lakukan denormalisasi hanya setelah: (1) desain sudah ter-normalisasi, (2) ada bukti query terlalu lambat, (3) index dan optimasi query tidak cukup.

Teknik Denormalization

Teknik	Deskripsi	Contoh
Materialized View	Simpan hasil query kompleks sebagai tabel "cached"	Tabel ringkasan penjualan per bulan
Redundant Columns	Tambah kolom yang sudah ada di tabel lain	Tambah `customer_name` di tabel orders
Derived Columns	Simpan hasil perhitungan	Simpan `total_items` di tabel orders
Flattened Tables	Gabung data dari beberapa tabel jadi satu	Search index yang menggabungkan data dari banyak tabel
Cache Tables	Salin data yang sering diakses ke tabel cepat	Tabel `popular_products` untuk halaman utama

Contoh: Denormalization untuk Performa

SQL — Denormalization

-- Skenario: Query "tampilkan order dengan nama customer"
-- memerlukan JOIN setiap kali → lambat untuk dashboard

-- Solusi 1: Redundant column di tabel orders
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);
ALTER TABLE orders ADD COLUMN customer_email VARCHAR(150);

-- Update saat customer di-update (trigger atau app logic)
UPDATE orders o
JOIN customers c ON o.customer_id = c.customer_id
SET o.customer_name = c.nama,
    o.customer_email = c.email
WHERE c.customer_id = 42;

-- Query sekarang TANPA JOIN → cepat!
SELECT order_id, customer_name, customer_email, total, status
FROM orders
WHERE status = 'selesai'
ORDER BY tanggal DESC
LIMIT 50;

-- Solusi 2: Materialized View / Summary Table
CREATE TABLE monthly_sales_summary (
    bulan         DATE PRIMARY KEY,
    total_orders  INT,
    total_revenue DECIMAL(15,2),
    avg_order     DECIMAL(12,2),
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Refresh secara berkala (cron job atau trigger)
INSERT INTO monthly_sales_summary
SELECT
    DATE_FORMAT(tanggal, '%Y-%m-01') AS bulan,
    COUNT(*) AS total_orders,
    SUM(total) AS total_revenue,
    AVG(total) AS avg_order
FROM orders
WHERE status = 'selesai'
GROUP BY DATE_FORMAT(tanggal, '%Y-%m-01')
ON DUPLICATE KEY UPDATE
    total_orders = VALUES(total_orders),
    total_revenue = VALUES(total_revenue),
    avg_order = VALUES(avg_order),
    updated_at = CURRENT_TIMESTAMP;

9. Schema Design Patterns

Berikut beberapa pola desain schema yang umum digunakan dalam pengembangan aplikasi nyata. Pola-pola ini sudah teruji dan bisa langsung diterapkan.

Pattern 1: Soft Delete

SQL — Soft Delete Pattern

-- Alih-alih DELETE, tandai data sebagai "dihapus"
ALTER TABLE posts ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;

-- Query normal (hanya data aktif)
SELECT * FROM posts WHERE deleted_at IS NULL;

-- "Hapus" data (soft delete)
UPDATE posts SET deleted_at = NOW() WHERE post_id = 42;

-- Restore data yang terhapus
UPDATE posts SET deleted_at = NULL WHERE post_id = 42;

-- Lihat semua data termasuk yang terhapus
SELECT * FROM posts;  -- Atau WHERE deleted_at IS NOT NULL

-- Index partial untuk efisiensi
CREATE INDEX idx_posts_active ON posts(created_at)
WHERE deleted_at IS NULL;  -- PostgreSQL syntax

Pattern 2: Audit Trail / History

SQL — Audit Trail

-- Tabel audit untuk mencatat semua perubahan
CREATE TABLE audit_log (
    log_id      BIGINT PRIMARY KEY AUTO_INCREMENT,
    table_name  VARCHAR(50) NOT NULL,
    record_id   INT NOT NULL,
    action      ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    old_values  JSON,
    new_values  JSON,
    changed_by  INT,
    changed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address  VARCHAR(45)
);

CREATE INDEX idx_audit_table ON audit_log(table_name, record_id);
CREATE INDEX idx_audit_time ON audit_log(changed_at);

-- Contoh trigger MySQL untuk auto-log perubahan
DELIMITER //
CREATE TRIGGER trg_customers_audit
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, record_id, action,
                           old_values, new_values, changed_at)
    VALUES (
        'customers',
        NEW.customer_id,
        'UPDATE',
        JSON_OBJECT('nama', OLD.nama, 'email', OLD.email),
        JSON_OBJECT('nama', NEW.nama, 'email', NEW.email),
        NOW()
    );
END //
DELIMITER ;

Pattern 3: Polymorphic Association

SQL — Polymorphic Association

-- Skenario: Comments bisa untuk post, product, atau video
-- Solusi yang BAIK (referential integrity terjaga):

CREATE TABLE comments (
    comment_id    INT PRIMARY KEY AUTO_INCREMENT,
    content       TEXT NOT NULL,
    user_id       INT NOT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Junction table terpisah untuk setiap tipe
CREATE TABLE post_comments (
    comment_id INT PRIMARY KEY,
    post_id    INT NOT NULL,
    FOREIGN KEY (comment_id) REFERENCES comments(comment_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
);

CREATE TABLE product_comments (
    comment_id INT PRIMARY KEY,
    product_id INT NOT NULL,
    FOREIGN KEY (comment_id) REFERENCES comments(comment_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Alternatif: Single table dengan tipe (lebih sederhana tapi kurang ketat)
CREATE TABLE comments_simple (
    comment_id   INT PRIMARY KEY AUTO_INCREMENT,
    content      TEXT NOT NULL,
    user_id      INT NOT NULL,
    commentable_type ENUM('post','product','video') NOT NULL,
    commentable_id   INT NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- ⚠️ Tidak bisa pakai foreign key ke commentable_id
-- Karena merujuk ke tabel yang berbeda-beda

Pattern 4: Multi-Tenancy

SQL — Multi-Tenancy

-- Pendekatan 1: Shared schema dengan tenant_id
-- (Paling umum untuk SaaS)

ALTER TABLE customers ADD COLUMN tenant_id INT NOT NULL;
ALTER TABLE orders ADD COLUMN tenant_id INT NOT NULL;
ALTER TABLE products ADD COLUMN tenant_id INT NOT NULL;

-- Setiap query WAJIB filter tenant_id
SELECT * FROM orders WHERE tenant_id = 5 AND status = 'pending';

-- Composite index yang efisien
CREATE INDEX idx_orders_tenant ON orders(tenant_id, status, tanggal);
CREATE INDEX idx_products_tenant ON products(tenant_id, nama_produk);

-- Row Level Security (PostgreSQL)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::int);

-- Pendekatan 2: Schema per tenant (PostgreSQL)
CREATE SCHEMA tenant_001;
CREATE TABLE tenant_001.orders (LIKE public.orders INCLUDING ALL);
CREATE SCHEMA tenant_002;
CREATE TABLE tenant_002.orders (LIKE public.orders INCLUDING ALL);

10. Quiz: Uji Pemahamanmu!

Setelah membaca tutorial di atas, jawablah 5 pertanyaan berikut untuk menguji pemahamanmu tentang Database Design & Normalisasi:

Pertanyaan 1: Apa syarat utama agar sebuah tabel memenuhi 1NF (First Normal Form)?

a) Harus memiliki minimal 2 foreign key

b) Setiap kolom berisi nilai atomik (satu nilai per sel) dan ada primary key

c) Tidak boleh ada kolom yang NULL

d) Semua kolom harus bertipe VARCHAR

Pertanyaan 2: Apa yang dimaksud dengan "transitive dependency" dalam konteks 3NF?

a) Kolom A bergantung langsung pada primary key

b) Kolom A menentukan kolom B, dan kolom B menentukan kolom C — sehingga A secara tidak langsung menentukan C

c) Dua tabel yang saling memiliki foreign key

d) Data yang disimpan di dua tabel sekaligus

Pertanyaan 3: Untuk merepresentasikan relasi "satu mahasiswa mengambil banyak mata kuliah", teknik apa yang digunakan?

a) Menambah kolom mata_kuliah di tabel mahasiswa

b) Membuat junction table (tabel penghubung) dengan foreign key dari kedua tabel

c) Menggabungkan kedua tabel menjadi satu

d) Menggunakan JSON array di salah satu tabel

Pertanyaan 4: Kapan sebaiknya melakukan denormalisasi?

a) Sejak awal desain database

b) Saat ada bukti query terlalu lambat dan optimasi index tidak cukup

c) Setiap kali membuat tabel baru

d) Denormalisasi tidak pernah diperlukan

Pertanyaan 5: Apa yang dimaksud dengan "leftmost prefix rule" pada composite index?

a) Index hanya bisa dibuat di kolom pertama tabel

b) Composite index (A, B, C) bisa digunakan untuk query yang memfilter A, A+B, atau A+B+C — tapi bukan B saja atau C saja

c) Nama index harus dimulai dengan huruf pertama tabel

d) Primary key harus selalu di kolom pertama