Pemodelan Data

Menulis CREATE TABLE terlalu cepat adalah resep skema yang menyulitkan: kolom saling menumpuk, satu tabel dipakai untuk banyak hal, dan query analitik jadi berat. Pemodelan data membalik urutannya — kita gambarkan dulu apa yang disimpan dan bagaimana hubungannya, baru diterjemahkan ke tabel. Alat gambar paling berpengaruh di sini adalah model ER (Entity-Relationship), dan chapter ini memakainya untuk merancang ulang inti data toko online yang sudah kita kenal dari section Dasar SQL.

Entitas, Atribut, dan Relasi #

Model ER dibangun dari tiga unsur: entitas adalah objek yang datanya perlu disimpan (pelanggan, produk, pesanan) dan menjadi kandidat tabel; atribut adalah properti entitas (nama pelanggan, harga produk) dan menjadi kandidat kolom; relasi adalah hubungan antar entitas (pelanggan membuat pesanan) dan diimplementasikan lewat foreign key. Pemetaannya lugas:

Cara cepat menemukan entitas: tandai kata benda penting dalam deskripsi kebutuhan. Kalimat “pelanggan membuat pesanan yang memuat produk” langsung memberi tiga entitas dan dua relasi.

Kardinalitas: 1-1, 1-N, dan N-N #

Kardinalitas menyatakan berapa banyak baris satu entitas boleh berhubungan dengan entitas lain. Tiga pola dasarnya menentukan bentuk tabel:

Relasi N-N tidak bisa hidup di kedua tabel induknya. Menyimpan daftar produk di kolom pesanan akan membuat pencarian “pesanan mana yang memuat Kemeja Batik” mustahil dikerjakan tanpa membedah teks. Solusinya adalah tabel detail_pesanan yang menampung satu baris per pasangan pesanan-produk.

Diagram ER Toko Online #

Rancangan lengkap toko online kita tampak seperti berikut — notasi || berarti “tepat satu”, o| “nol atau satu”, o{ “nol atau banyak”, dan |{ “satu atau banyak”:

Dibaca dari kiri: satu PELANGGAN boleh memiliki nol atau satu KARTU_MEMBER dan membuat nol atau banyak PESANAN; satu PESANAN memuat satu atau banyak baris DETAIL_PESANAN; satu PRODUK dirinci oleh nol atau banyak DETAIL_PESANAN. Dua relasi terakhir memecah N-N pesanan-produk menjadi dua relasi 1-N yang bertemu di tabel perantara.

Menerjemahkan Model ke Tabel SQL #

Diagram di atas menjadi lima tabel. Berikut implementasinya beserta data uji — semuanya dijalankan nyata di SQLite:

import sqlite3

koneksi = sqlite3.connect(":memory:")
koneksi.execute("PRAGMA foreign_keys = ON")
koneksi.executescript("""
CREATE TABLE pelanggan (
    id INTEGER PRIMARY KEY,
    nama TEXT NOT NULL,
    kota TEXT
);
CREATE TABLE kartu_member (
    nomor TEXT PRIMARY KEY,
    pelanggan_id INTEGER NOT NULL UNIQUE REFERENCES pelanggan(id),
    poin INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE produk (
    id INTEGER PRIMARY KEY,
    nama TEXT NOT NULL,
    harga INTEGER NOT NULL
);
CREATE TABLE pesanan (
    id INTEGER PRIMARY KEY,
    pelanggan_id INTEGER NOT NULL REFERENCES pelanggan(id),
    tanggal TEXT NOT NULL
);
CREATE TABLE detail_pesanan (
    pesanan_id INTEGER NOT NULL REFERENCES pesanan(id),
    produk_id INTEGER NOT NULL REFERENCES produk(id),
    jumlah INTEGER NOT NULL,
    PRIMARY KEY (pesanan_id, produk_id)
);
INSERT INTO pelanggan (id, nama, kota) VALUES
    (1, 'Budi Santoso', 'Jakarta'),
    (2, 'Siti Rahayu', 'Bandung'),
    (3, 'Dewi Lestari', 'Surabaya');
INSERT INTO kartu_member VALUES ('MBR-001', 1, 1200);
INSERT INTO produk (id, nama, harga) VALUES
    (1, 'Smartphone X1', 3500000),
    (2, 'TWS Earbuds', 750000),
    (3, 'Kemeja Batik', 250000);
INSERT INTO pesanan (id, pelanggan_id, tanggal) VALUES
    (1, 1, '2024-06-01'),
    (2, 2, '2024-06-03'),
    (3, 1, '2024-07-20');
INSERT INTO detail_pesanan (pesanan_id, produk_id, jumlah) VALUES
    (1, 1, 1), (1, 3, 2), (2, 2, 1), (2, 3, 1), (3, 3, 1);
""")

print("-- isi pesanan 1: satu pesanan, banyak produk --")
for b in koneksi.execute("""
    SELECT p.nama, d.jumlah FROM detail_pesanan d
    JOIN produk p ON p.id = d.produk_id
    WHERE d.pesanan_id = 1
"""):
    print(b)

print("-- Kemeja Batik: satu produk, banyak pesanan --")
for b in koneksi.execute("""
    SELECT d.pesanan_id, pl.nama FROM detail_pesanan d
    JOIN produk p ON p.id = d.produk_id
    JOIN pesanan ps ON ps.id = d.pesanan_id
    JOIN pelanggan pl ON pl.id = ps.pelanggan_id
    WHERE p.nama = 'Kemeja Batik' ORDER BY d.pesanan_id
"""):
    print(b)

Output yang Dihasilkan:

-- isi pesanan 1: satu pesanan, banyak produk --
('Smartphone X1', 1)
('Kemeja Batik', 2)
-- Kemeja Batik: satu produk, banyak pesanan --
(1, 'Budi Santoso')
(2, 'Siti Rahayu')
(3, 'Budi Santoso')

Mari kita telaah bagian-bagian kunci skema di atas:

1. pelanggan_id ... UNIQUE REFERENCES pelanggan(id): kombinasi UNIQUE dan NOT NULL pada tabel kartu_member memaksakan relasi 1-1 — setiap pelanggan boleh muncul sekali saja.
2. pelanggan_id ... REFERENCES pelanggan(id) di pesanan: tanpa UNIQUE, kolom yang sama memaksakan 1-N; satu pelanggan boleh muncul di banyak baris pesanan.
3. PRIMARY KEY (pesanan_id, produk_id): primary key komposit pada tabel perantara — satu pasangan pesanan-produk hanya boleh ada satu baris, sementara masing-masing kolom tetap boleh berulang.
4. Dua query penutup: query pertama membaca sisi “satu pesanan banyak produk”, query kedua membaca sisi “satu produk banyak pesanan” — keduanya kini query SQL biasa, bukan pembedahan teks.

Batasan 1-1 juga bisa dibuktikan ditolak oleh database. Percobaan membuat kartu kedua untuk Budi gagal dengan galat batasan:

try:
    koneksi.execute("INSERT INTO kartu_member VALUES ('MBR-002', 1, 50)")
except sqlite3.IntegrityError as galat:
    print("IntegrityError:", galat)

Output yang Dihasilkan:

IntegrityError: UNIQUE constraint failed: kartu_member.pelanggan_id

Sintaks lengkap CREATE TABLE yang membangun tabel-tabel tersebut terdokumentasi di dokumentasi SQLite .

Model ER menghasilkan tabel yang rapi per entitas, tetapi belum menjamin setiap tabel menyimpan fakta yang atomik — satu tabel pesanan masih boleh memuat nama pelanggan, kota, dan daftar produk sekaligus. Chapter Normalisasi Database memberi aturan bertahap untuk memecah tabel seperti itu sampai setiap tabel hanya menyimpan satu jenis fakta.