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

Contoh Implementasi SQL

Diagram

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