Kenapa penting

Query yang bagus tidak bisa menyelamatkan skema yang buruk. Desain tabel menentukan apakah data tetap benar saat aplikasi tumbuh: apakah email pelanggan bisa tidak sinkron di sepuluh tempat, apakah stok bisa negatif, apakah order bisa tersimpan tanpa item-nya. Bug data jauh lebih mahal daripada bug kode — kode bisa di-deploy ulang, data yang rusak harus diperbaiki baris demi baris. Bab ini membahas cara memodelkan data, membiarkan database menjaga aturan bisnis lewat constraint, dan memakai transaksi agar operasi seperti checkout tidak setengah jadi walau ada request bersamaan. Semua ini juga fondasi JPA/Hibernate di level Spring Boot.

Konsep inti

Entity dan relasi

Entity adalah "benda" yang datanya disimpan — pelanggan, produk, order — dan biasanya menjadi satu tabel. Antar entity ada relasi:

Relasi	Contoh	Cara di tabel
1-N	satu pelanggan punya banyak order	kolom customer_id (foreign key) di tabel sisi "banyak"
N-M	produk ↔ kategori; order ↔ produk	tabel penghubung berisi dua foreign key
1-1	pelanggan ↔ profil detail	foreign key + UNIQUE, atau cukup kolom di tabel yang sama

Relasi N-M tidak bisa disimpan langsung; ia dipecah menjadi dua relasi 1-N lewat tabel penghubung:

CREATE TABLE category (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE product_category (
    product_id  bigint NOT NULL REFERENCES product (id) ON DELETE CASCADE,
    category_id bigint NOT NULL REFERENCES category (id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, category_id)   -- pasangan yang sama tidak boleh dobel
);

Untuk menampilkan kategori per produk, cukup dua JOIN:

SELECT p.sku, string_agg(c.name, ', ' ORDER BY c.name) AS kategori
FROM product p
JOIN product_category pc ON pc.product_id = p.id
JOIN category c ON c.id = pc.category_id
GROUP BY p.sku
ORDER BY p.sku;

   sku    |      kategori
----------+--------------------
 KOPI-250 | Minuman, Oleh-oleh
 TEH-100  | Minuman

Normalisasi: 1NF, 2NF, 3NF

Normalisasi adalah aturan agar setiap fakta disimpan tepat sekali. Tiga bentuk pertama sudah cukup untuk hampir semua aplikasi:

Bentuk	Aturan	Contoh pelanggaran
1NF	setiap kolom berisi satu nilai atomik; tidak ada grup berulang	category_ids = '1,4,7', atau kolom item1, item2, item3
2NF	1NF + kolom non-key bergantung pada seluruh primary key gabungan	product_name di order_item (order_id, product_id) — hanya bergantung pada product_id
3NF	2NF + kolom non-key tidak bergantung pada kolom non-key lain	customer_email di orders — bergantung pada customer_id

Gejala skema yang tidak normal disebut anomali: update anomaly (ganti email harus di banyak baris, sebagian terlewat), insert anomaly (tidak bisa menyimpan kategori baru sebelum ada produknya), dan delete anomaly (menghapus order terakhir ikut menghapus satu-satunya data pelanggan). Ringkasnya: setiap kolom harus menjelaskan "key, seluruh key, dan hanya key".

Denormalisasi — menyimpan data turunan atau salinan — boleh, asalkan disengaja dan alasannya jelas:

• Snapshot historis. order_item.unit_price menyimpan harga saat checkout. Ini bukan duplikasi product.price, karena faktanya berbeda: harga produk boleh naik besok, harga di order lama tidak boleh berubah.
• Kinerja baca. orders.total sebenarnya bisa dihitung dari order_item, tetapi disimpan agar daftar order tidak perlu agregasi setiap kali. Konsekuensinya, kode wajib memperbarui keduanya dalam transaksi yang sama.

Mulailah dari skema 3NF, lalu denormalisasi hanya setelah ada kebutuhan nyata.

Primary key: bigint identity vs uuid

Primary key mengidentifikasi satu baris secara unik, tidak boleh NULL, dan otomatis diberi index. Gunakan surrogate key (ID buatan) daripada data bisnis seperti email atau nomor HP yang bisa berubah.

Pilihan	Kelebihan	Kekurangan
bigint GENERATED ALWAYS AS IDENTITY	kecil (8 byte), cepat, index rapat, mudah dibaca	bisa ditebak (/orders/124), dibuat oleh database
uuid DEFAULT gen_random_uuid()	sulit ditebak, bisa dibuat di aplikasi/antarlayanan	16 byte, UUID v4 acak membuat index B-tree terfragmentasi

CREATE TABLE payment (
    id     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    amount numeric(12,2) NOT NULL
);
INSERT INTO payment (amount) VALUES (210000) RETURNING id;
-- id: d31bfac1-8635-4165-bf06-16b6fa92854d (acak)

Default yang aman: bigint identity untuk tabel internal. Pilih uuid bila ID terlihat publik atau dibuat di luar database. PostgreSQL 18 menambahkan fungsi uuidv7() (UUID berurutan waktu, lebih ramah index); di PostgreSQL 17 fungsi itu belum ada. Perlu dicatat: nilai identity yang terpakai oleh INSERT gagal atau transaksi yang di-rollback tidak dikembalikan — lubang di urutan ID itu normal, jangan dijadikan nomor faktur berurutan.

Constraint: biarkan database menjaga aturan

Validasi di Java tetap perlu untuk pesan error yang ramah, tetapi constraint di database adalah pertahanan terakhir yang berlaku untuk semua jalur — aplikasi lain, script migrasi, atau query manual di psql.

Constraint	Menjamin
NOT NULL	kolom wajib diisi
UNIQUE	tidak ada nilai ganda (email, SKU); otomatis punya index
CHECK (...)	aturan per baris (price >= 0, status valid)
REFERENCES	foreign key: nilai harus ada di tabel induk
PRIMARY KEY	NOT NULL + UNIQUE untuk identitas baris

Pelanggaran constraint menghasilkan error yang jelas (dari skema di bagian contoh nanti):

ERROR:  duplicate key value violates unique constraint "customer_email_key"
DETAIL:  Key (email)=(budi@contoh.id) already exists.

ERROR:  new row for relation "product" violates check constraint "product_price_check"
DETAIL:  Failing row contains (3, GULA-500, Gula Aren 500 g, -1000.00, 0).

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(99) is not present in table "customer".

ERROR:  update or delete on table "product" violates foreign key constraint "order_item_product_id_fkey" on table "order_item"
DETAIL:  Key (id)=(1) is still referenced from table "order_item".

Foreign key secara default menolak penghapusan induk yang masih dirujuk (error terakhir). ON DELETE CASCADE ikut menghapus baris anak — cocok untuk order_item saat orders dihapus, berbahaya bila dipasang sembarangan. PostgreSQL tidak otomatis membuat index untuk kolom foreign key; tambahkan sendiri bila sering di-JOIN atau difilter.

Transaksi dan ACID

Transaksi mengelompokkan beberapa perintah menjadi satu unit: semua berhasil (COMMIT) atau semua batal (ROLLBACK). Tanpa BEGIN, setiap perintah adalah transaksinya sendiri (autocommit).

Huruf	Sifat	Artinya untuk checkout
A	Atomicity	order, item, dan pengurangan stok tersimpan semua atau tidak sama sekali
C	Consistency	transaksi tidak boleh meninggalkan data yang melanggar constraint
I	Isolation	transaksi bersamaan tidak saling mengacaukan (diatur isolation level)
D	Durability	setelah COMMIT, data tetap ada walau server mati sesaat kemudian

Bila satu perintah gagal di tengah transaksi, PostgreSQL menolak perintah berikutnya sampai transaksi diakhiri:

ERROR:  new row for relation "product" violates check constraint "product_stock_check"
DETAIL:  Failing row contains (2, TEH-100, Teh Melati 100 g, 40000.00, -3).
ERROR:  current transaction is aborted, commands ignored until end of transaction block

ROLLBACK (atau COMMIT, yang dalam kondisi ini dijawab ROLLBACK) membatalkan semuanya, termasuk INSERT order yang tadinya sukses.

Isolation level dan anomali konkurensi

Saat banyak transaksi berjalan bersamaan, ada empat anomali klasik:

Anomali	Yang terjadi
Dirty read	membaca perubahan transaksi lain yang belum commit (dan mungkin di-rollback)
Non-repeatable read	membaca baris yang sama dua kali, nilainya berbeda karena transaksi lain commit
Phantom read	query rentang yang sama dua kali, muncul/hilang baris karena insert/delete lain
Lost update	dua transaksi membaca nilai sama lalu menulis; salah satu perubahan tertimpa

Perilaku isolation level di PostgreSQL:

Level	Dirty read	Non-repeatable	Phantom	Lost update
READ COMMITTED (default)	tidak	bisa	bisa	bisa
REPEATABLE READ	tidak	tidak	tidak¹	dideteksi → error
SERIALIZABLE	tidak	tidak	tidak	dideteksi → error

¹ Standar SQL mengizinkan phantom di REPEATABLE READ, tetapi implementasi snapshot PostgreSQL mencegahnya. READ UNCOMMITTED di PostgreSQL berperilaku sama dengan READ COMMITTED — dirty read tidak pernah terjadi.

Berikut hasil nyata dua sesi psql. Sesi A membaca stok, sesi B menjalankan UPDATE product SET stock = 50 WHERE id = 2 dan commit, lalu sesi A membaca lagi:

BEGIN ISOLATION LEVEL READ COMMITTED;     -- atau REPEATABLE READ
SELECT stock FROM product WHERE id = 2;   -- 2
-- ... sesi B commit stock = 50 ...
SELECT stock FROM product WHERE id = 2;   -- READ COMMITTED: 50, REPEATABLE READ: 2
COMMIT;

Lost update muncul bila aplikasi membaca nilai, menghitung di Java, lalu menulis nilai absolut. Dua sesi sama-sama membaca stock = 2 dan menulis SET stock = 1. Di READ COMMITTED keduanya sukses dan stok akhir 1 padahal dua barang terjual. Di REPEATABLE READ, sesi kedua ditolak:

ERROR:  could not serialize access due to concurrent update

Level yang lebih ketat berarti aplikasi wajib siap mengulang transaksi saat error ini muncul. Untuk kasus stok, solusi yang lebih sederhana tetap di READ COMMITTED:

• Update atomik: UPDATE product SET stock = stock - 1 WHERE id = 2 AND stock >= 1; — database menghitung dari nilai terbaru dan mengunci barisnya; UPDATE 0 berarti stok habis.
• Kunci baris eksplisit: SELECT stock FROM product WHERE id = 2 FOR UPDATE; di dalam transaksi membuat transaksi lain yang ingin mengubah baris itu menunggu sampai kamu commit.

Contoh di dunia backend

Skema e-commerce lengkap dengan constraint — pelanggan, produk, order, dan item order:

CREATE TABLE customer (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text        NOT NULL UNIQUE,
    full_name  text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE product (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku   text          NOT NULL UNIQUE,
    name  text          NOT NULL,
    price numeric(12,2) NOT NULL CHECK (price >= 0),
    stock integer       NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint        NOT NULL REFERENCES customer (id),
    status      text          NOT NULL DEFAULT 'NEW'
                CHECK (status IN ('NEW', 'PAID', 'SHIPPED', 'CANCELLED')),
    total       numeric(12,2) NOT NULL CHECK (total >= 0),
    created_at  timestamptz   NOT NULL DEFAULT now()
);
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

CREATE TABLE order_item (
    order_id   bigint        NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    product_id bigint        NOT NULL REFERENCES product (id),
    quantity   integer       NOT NULL CHECK (quantity > 0),
    unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),   -- snapshot harga saat checkout
    PRIMARY KEY (order_id, product_id)
);
CREATE INDEX idx_order_item_product_id ON order_item (product_id);

order_item adalah tabel penghubung N-M antara orders dan product yang punya atribut sendiri (quantity, unit_price). Checkout Budi — 2 kopi dan 1 teh — dijalankan dalam satu transaksi:

BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 210000) RETURNING id;   -- id = 1
INSERT INTO order_item (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 2, 85000),
    (1, 2, 1, 40000);
UPDATE product SET stock = stock - 2 WHERE id = 1;
UPDATE product SET stock = stock - 1 WHERE id = 2;
COMMIT;

Checkout berikutnya meminta 5 teh padahal stok tinggal 2. UPDATE stok melanggar CHECK (stock >= 0), transaksi di-ROLLBACK, dan order kedua tidak pernah ada:

 id | status |   total
----+--------+-----------
  1 | NEW    | 210000.00

   sku    | stock
----------+-------
 KOPI-250 |     8
 TEH-100  |     2

Constraint CHECK di sini menjadi jaring pengaman terakhir terhadap overselling. Di bab JDBC, pola yang sama ditulis di Java dengan setAutoCommit(false), commit(), dan rollback().

Pertanyaan interview

1. Jelaskan ACID dengan contoh.

Pada checkout: atomicity menjamin order, item, dan pengurangan stok tersimpan semua atau tidak sama sekali; consistency menjamin constraint seperti stock >= 0 tidak pernah dilanggar setelah transaksi; isolation mengatur agar checkout bersamaan tidak saling menimpa; durability menjamin order yang sudah di-commit tidak hilang walau server mati.

2. Apa itu lost update dan bagaimana mencegahnya?

Dua transaksi membaca nilai yang sama, menghitung nilai baru di aplikasi, lalu menulis — perubahan yang pertama tertimpa. Pencegahannya: update atomik (SET stock = stock - 1 dengan syarat di WHERE), kunci baris dengan SELECT ... FOR UPDATE, optimistic locking dengan kolom versi, atau isolation REPEATABLE READ yang mendeteksi konflik dan menuntut transaksi diulang.

3. Kapan kamu sengaja melakukan denormalisasi?

Saat data harus membeku secara historis (harga di order_item, alamat kirim di order) atau saat query baca terbukti terlalu mahal dan nilai turunan seperti orders.total layak disimpan. Syaratnya: alasannya tercatat dan ada mekanisme yang menjaga konsistensinya, misalnya diperbarui dalam transaksi yang sama.

Jebakan umum

• Hanya validasi di Java tanpa constraint di database. Script, aplikasi lain, atau race condition akan melewatinya; pasang NOT NULL, UNIQUE, CHECK, dan foreign key.
• Read-modify-write di aplikasi (stock = 2 - 1 lalu SET stock = 1) → lost update. Pakai update atomik atau FOR UPDATE.
• Lupa index pada kolom foreign key. PostgreSQL hanya membuat index otomatis untuk primary key dan UNIQUE.
• Menyimpan daftar ID dalam satu kolom teks ('1,4,7') — melanggar 1NF; pakai tabel penghubung.
• ON DELETE CASCADE di mana-mana. Satu DELETE bisa menghapus data penting secara berantai; pakai hanya untuk data anak yang memang tidak bermakna tanpa induknya.
• Menganggap ID identity selalu berurutan tanpa lubang. Rollback dan insert gagal tetap memakai nomor.

Ringkasan

• Entity menjadi tabel; relasi 1-N memakai foreign key di sisi "banyak", relasi N-M memakai tabel penghubung.
• Normalisasi 3NF menyimpan setiap fakta sekali; denormalisasi hanya untuk snapshot historis atau kinerja yang terbukti.
• bigint identity adalah default primary key; uuid saat ID publik atau dibuat di luar database.
• Constraint (NOT NULL, UNIQUE, CHECK, foreign key) adalah pertahanan terakhir integritas data.
• Transaksi memberi ACID: semua perintah commit bersama atau di-rollback bersama.
• READ COMMITTED (default) masih rawan non-repeatable read dan lost update; cegah dengan update atomik, FOR UPDATE, atau REPEATABLE READ + retry.

Bacaan lanjutan

• PostgreSQL 17 — Constraints
• PostgreSQL 17 — Transactions (tutorial)
• PostgreSQL 17 — Transaction Isolation