Contoh SQL menggunakan PostgreSQL kecuali dialek lain disebutkan. Cuplikan singkat mengasumsikan tabel yang direferensikan sudah ada. Validasi DDL di basis data target Anda sebelum menerapkannya.
Mengapa Desain Skema Penting
Skema basis data yang dirancang dengan baik adalah fondasi dari setiap aplikasi yang andal. Lakukan dengan benar dan kueri menjadi cepat, data tetap konsisten, dan fitur masa depan masuk tanpa migrasi yang menyakitkan. Lakukan dengan salah dan Anda akan melawan anomali data, kinerja lamban, dan solusi sementara tanpa akhir selama masa pakai proyek.
Panduan ini menjelaskan proses enam langkah untuk merancang skema dari awal โ dan diakhiri dengan contoh dunia nyata lengkap yang dapat Anda adaptasi untuk proyek Anda sendiri.
Langkah 1 โ Kumpulkan Persyaratan
Sebelum membuka alat apa pun, bicaralah dengan para pemangku kepentingan. Pahami apa aplikasi tersebut melakukan, data apa yang dikelolanya, dan bagaimana data tersebut berelasi. Ajukan pertanyaan seperti:
- "Hal-hal" utama apa yang dilacak oleh sistem? (Pengguna, pesanan, produk...)
- Data mana yang harus unik? (Email, SKU, slug โฆ)
- Kueri apa yang akan paling sering dijalankan oleh aplikasi?
- Apakah ada batasan regulasi? (GDPR, HIPAA โฆ)
- Berapa volume data yang diharapkan selama 1โ3 tahun ke depan?
Tulis jawabannya โ jawaban tersebut menjadi spesifikasi Anda dan akan memandu setiap keputusan selanjutnya.
Langkah 2 โ Identifikasi Entitas
Sebuah entitas adalah apa pun yang perlu disimpan datanya oleh sistem. Setiap entitas biasanya dipetakan ke satu tabel database. Untuk platform blog, daftar entitas awal mungkin terlihat seperti:
- Pengguna โ penulis dan pembaca
- Kirim โ artikel yang diterbitkan di blog
- Kategori โ pengelompokan untuk kiriman (misalnya, "React", "SQL")
- Komentar โ tanggapan pembaca terhadap kiriman
- Tag โ label berbutir halus (banyak-ke-banyak dengan kiriman/postingan)
Sketsakan ini sebagai kotak di papan tulis atau gunakan Pembuat ERD untuk membahas model konseptual sebelum memilih jenis kolom.
Langkah 3 โ Tentukan Atribut & Tipe Data
Untuk setiap entitas, daftarkan atribut yang diperlukan dan pilih tipe data SQL yang paling sesuai. Jadilah sespesifik mungkin โ lebih disukai VARCHAR(255) over TEXT ketika Anda tahu panjang maksimumnya, dan lebih menyukai TIMESTAMPTZ over TIMESTAMP ketika zona waktu penting.
-- Users
id SERIAL PRIMARY KEY
username VARCHAR(50) NOT NULL UNIQUE
email VARCHAR(255) NOT NULL UNIQUE
password_hash TEXT NOT NULL -- store a password hash, never plaintext
bio TEXT
avatar_url VARCHAR(500)
created_at TIMESTAMPTZ DEFAULT now()
-- Posts
id SERIAL PRIMARY KEY
author_id INT NOT NULL -- FK โ users.id
category_id INT -- FK โ categories.id
title VARCHAR(200) NOT NULL
slug VARCHAR(220) NOT NULL UNIQUE
body TEXT NOT NULL
status VARCHAR(20) DEFAULT 'draft' -- draft | published | archived
published_at TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT now()
updated_at TIMESTAMPTZ DEFAULT now()Langkah 4 โ Tetapkan Relasi
Tentukan bagaimana entitas berhubungan satu sama lain. Tiga jenis fundamental adalah:
- Satu-ke-Banyak (1:N) โ Seorang pengguna memiliki banyak pos; sebuah pos milik satu pengguna.
- Banyak-ke-Banyak (M:N) โ Sebuah pos dapat memiliki banyak tag; sebuah tag dapat menjadi milik banyak pos. Ini memerlukan tabel perantara.
- Satu-ke-Satu (1:1) โ Seorang pengguna memiliki satu profil. Sering kali digabungkan menjadi satu tabel kecuali data profil tersebut besar atau jarang diakses.
-- Junction table for the posts โ tags many-to-many relationship
CREATE TABLE post_tags (
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
tag_id INT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);Langkah 5 โ Terapkan Batasan & Indeks
Batasan melindungi kualitas data. Minimal Anda harus mempertimbangkan:
NOT NULLon every column that must have a value.UNIQUEon natural identifiers (emails, slugs, usernames).CHECKkendala untuk aturan domain (misalnya, status harus berupa salah satu dari tiga nilai).DEFAULTnilai untuk stempel waktu dan kolom status.- Kunci asing dengan eksplisit
ON DELETEtindakan.
Lalu tambahkan indeks pada kolom yang sering Anda gunakan untuk pemfilteran, pengurutan, atau penggabungan. Kolom foreign key merupakan kandidat utama.
-- Index foreign-key columns for faster joins
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_category ON posts (category_id);
CREATE INDEX idx_comments_post ON comments (post_id);
-- Partial index: only published posts (PostgreSQL)
CREATE INDEX idx_posts_published
ON posts (published_at DESC)
WHERE status = 'published';Langkah 6 โ Normalisasi (Lalu Denormalisasi Jika Diperlukan)
Normalisasi menghilangkan data yang redundan dengan mengatur kolom dan tabel menurut seperangkat "bentuk normal." Sebagian besar skema harus mencapai Bentuk Normal Ketiga (3NF):
- 1NF โ setiap kolom memegang nilai atomik; tidak ada grup berulang.
- 2NF โ tidak ada atribut non-prima yang bergantung hanya pada sebagian dari kandidat kunci.
- 3NF โ setiap dependensi non-trivial memiliki penentu superkey atau atribut dependen yang termasuk dalam kandidat kunci.
Setelah dinormalisasi, Anda dapat secara selektif denormalisasi untuk kinerja โ misalnya, menyimpan comment_count on the posts tabel untuk menghindari COUNT(*) setiap kali halaman dimuat. Lakukan denormalisasi secara sengaja dan dokumentasikan alasannya.
Skema Platform Blog Lengkap
-- Full SQL for the blog platform schema
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
bio TEXT,
avatar_url VARCHAR(500),
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
slug VARCHAR(110) NOT NULL UNIQUE
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
author_id INT NOT NULL REFERENCES users (id),
category_id INT REFERENCES categories (id) ON DELETE SET NULL,
title VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL UNIQUE,
body TEXT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft','published','archived')),
published_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE post_tags (
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
tag_id INT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
user_id INT NOT NULL REFERENCES users (id),
body TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
-- Indexes
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_category ON posts (category_id);
CREATE INDEX idx_comments_post ON comments (post_id);
CREATE INDEX idx_comments_user ON comments (user_id);Tempel skema lengkap ke dalam Editor SQL ke ERD untuk memeriksa hubungannya. Gunakan Pembuat ERD untuk gambar konseptual dan konversi SQL yang ditinjau.



