Merancang Database Schema

Sebelum aplikasi bisa menyimpan satu baris data pun, kita perlu menjawab pertanyaan mendasar: data apa saja yang disimpan, dalam bentuk apa, dan bagaimana data tersebut saling terhubung. Jawaban pertanyaan tersebut dituangkan dalam schema database — struktur tabel, kolom, dan relasi yang menjadi cetak biru penyimpanan aplikasi. Schema yang dirancang baik membuat kode aplikasi sederhana; schema yang dibuat asal jadi membuat setiap fitur baru terasa seperti renovasi rumah tanpa gambar. Pada chapter ini kita berlatih proses lengkap merancang schema: membaca requirements, mengidentifikasi entitas, memilih nama dan tipe data, sampai mengeksekusi CREATE TABLE dan mengujinya dengan data contoh. Semua contoh dijalankan pada SQLite in-memory melalui PDO, sehingga Anda bisa mengikutinya tanpa memasang server database apa pun.

Dari Requirements ke Tabel #

Rancangan database yang baik selalu berangkat dari requirements, bukan dari keinginan langsung membuat kolom. Cara termudah memulainya adalah membaca requirements pelan-pelan sambil menandai setiap kata benda penting: pengguna, artikel, komentar. Kata benda yang muncul berulang adalah kandidat entitas — sesuatu yang datanya perlu kita simpan. Setiap entitas menjadi satu tabel, setiap sifat entitas menjadi kolom, dan kata kerja hubungan seperti “menulis” atau “mengomentari” menunjukkan relasi antar tabel.

Anggaplah kita menerima requirements blog berikut:

• “Pengguna mendaftar dengan username dan email.”
• “Pengguna menulis artikel yang memiliki judul, isi, dan status draf atau terbit.”
• “Pembaca dapat menulis komentar pada setiap artikel.”

Dari tiga kalimat tersebut kita bisa memetakan entitas dan kolomnya:

Relasi antar tabel mengikuti akal sehat domain: satu pengguna boleh menulis banyak artikel, dan satu artikel boleh menerima banyak komentar — keduanya relasi one-to-many. Setiap relasi semacam itu diwujudkan dengan kolom foreign key, misalnya user_id pada tabel posts yang merujuk ke users.id. Aturan main key tersebut akan kita bedah tuntas pada chapter Primary dan Foreign Keys .

Konvensi Penamaan Tabel dan Kolom #

Schema yang sama bisa ditulis dengan puluhan gaya penamaan; yang terpenting adalah memilih satu konvensi lalu memakainya secara konsisten. Konvensi yang paling umum di industri PHP adalah nama tabel bentuk plural bahasa Inggris dengan huruf kecil dan garis bawah (snake_case), misalnya users, posts, dan order_items. Nama kolom memakai snake_case, dan kolom foreign key dinamai mengikuti pola <tabel_singular>_id seperti user_id — begitu dibaca, kita langsung tahu kolom tersebut merujuk ke tabel users.

-- Konvensi yang dipakai di seluruh section ini
CREATE TABLE posts (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title   TEXT NOT NULL
);

-- Hindari: prefiks tabel, campur bahasa, kata reserved seperti "desc"
CREATE TABLE tbl_post (
    judul_post  TEXT,
    `desc`      TEXT
);

Memilih Tipe Data #

Setiap kolom membutuhkan tipe data yang sesuai kebutuhan, dan pilihan tepatnya bergantung pada database yang dipakai. Kita berlatih pada SQLite yang hanya mengenal lima kategori penyimpanan lewat mekanisme type affinity, sementara MySQL menyediakan tipe yang lebih spesifik dengan batasan panjang dan rentang. Tabel berikut merangkum pemetaan dari kebutuhan data di sisi PHP ke tipe kolom di kedua database:

Memilih Primary Key #

Setiap tabel membutuhkan primary key: kolom (atau gabungan kolom) yang menjadi identitas unik setiap baris. Dua pilihan yang paling sering dibandingkan adalah integer auto-increment dan UUID. Integer auto-increment dihasilkan database secara berurutan dan murah; UUID adalah pengenal 128-bit acak yang dibuat aplikasi, biasanya ditulis sebagai 36 karakter heksadesimal.

Untuk aplikasi web biasa, auto-increment adalah pilihan default yang tepat karena kecil, cepat, dan mudah dibaca. UUID menjadi menarik ketika data dibuat di banyak server sekaligus atau ketika id perlu ada sebelum menyimpan baris, karena nilainya bisa dibuat aplikasi tanpa menunggu database. Kekurangan UUID — lebih besar dan tidak berurutan — baru terasa pada tabel dengan puluhan juta baris.

Menggambar Diagram ER #

Sebelum menulis CREATE TABLE, ada baiknya menggambar hubungan antar tabel dalam diagram ER (Entity-Relationship). Diagram membantu memvalidasi rancangan secara cepat: entitas apa saja yang ada, kolom apa yang dimiliki, dan bagaimana relasinya terhubung. Berikut diagram ER untuk blog kita:

Notasi ||--o{ berarti relasi one-to-many: satu baris users terhubung ke nol atau banyak baris posts, dan satu baris posts terhubung ke banyak baris comments. Kolom berlabel PK adalah primary key dan FK adalah foreign key; catatan dalam tanda kutip menandai kolom yang harus unik. Dengan diagram ini sebagai pegangan, kita siap menulis kode pembuat tabel.

Membuat Tabel Pertama #

Sesuai konvensi Section Database dan SQL , kita memakai PDO dengan mode error ERRMODE_EXCEPTION agar setiap kesalahan SQL langsung melempar PDOException. Database sqlite::memory: hidup hanya di memori dan hilang saat skrip selesai — sempurna untuk latihan. Berikut tabel users pertama kita lengkap dengan penyisipan dua baris data:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email    TEXT NOT NULL UNIQUE
)");

$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->execute(["budi", "[email protected]"]);
$stmt->execute(["siti", "[email protected]"]);

foreach ($pdo->query("SELECT id, username, email FROM users ORDER BY id") as $row) {
    echo $row["id"] . " | " . $row["username"] . " | " . $row["email"] . "\n";
}

Output yang Dihasilkan:

1 | budi | [email protected]
2 | siti | [email protected]

Mari kita telaah kode di atas baris per baris:

1. Baris 2-3: koneksi PDO ke database in-memory dibuat, lalu mode error diubah agar kegagalan SQL melempar exception alih-alih diam-diam mengembalikan false.
2. Baris 5-9: CREATE TABLE mendefinisikan tiga kolom — id sebagai primary key integer, username yang wajib terisi, dan email yang wajib terisi sekaligus unik.
3. Baris 11-13: satu prepared statement dipakai dua kali untuk menyisipkan Budi dan Siti; kolom id sengaja tidak diisi karena SQLite mengisinya secara otomatis.
4. Baris 15-17: hasil query ditampilkan per baris, dan kolom id terbukti terisi 1 dan 2 tanpa kita tulis manual.

Uji Cepat Schema dengan Smoke Test #

Schema baru sebaiknya langsung diuji, bukan menunggu aplikasi selesai dibangun. Smoke test adalah pengujian minimal: buat semua tabel, sisipkan beberapa baris data contoh, lalu jalankan query yang paling sering dipakai aplikasi — biasanya JOIN antar tabel. Jika query tersebut berjalan dan hasilnya masuk akal, kerangka schema kemungkinan besar sudah benar. Berikut smoke test untuk ketiga tabel blog kita:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("PRAGMA foreign_keys = ON");

$pdo->exec("CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email    TEXT NOT NULL UNIQUE
)");

$pdo->exec("CREATE TABLE posts (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title   TEXT NOT NULL,
    body    TEXT NOT NULL DEFAULT '',
    status  TEXT NOT NULL DEFAULT 'draft',
    FOREIGN KEY (user_id) REFERENCES users(id)
)");

$pdo->exec("CREATE TABLE comments (
    id          INTEGER PRIMARY KEY,
    post_id     INTEGER NOT NULL,
    author_name TEXT NOT NULL,
    body        TEXT NOT NULL,
    FOREIGN KEY (post_id) REFERENCES posts(id)
)");

$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->execute(["budi", "[email protected]"]);
$stmt->execute(["siti", "[email protected]"]);

$stmt = $pdo->prepare("INSERT INTO posts (user_id, title, body, status) VALUES (?, ?, ?, ?)");
$stmt->execute([1, "Belajar Merancang Schema", "Langkah pertama: identifikasi entitas.", "published"]);

$stmt = $pdo->prepare("INSERT INTO comments (post_id, author_name, body) VALUES (?, ?, ?)");
$stmt->execute([1, "Siti", "Penjelasannya jelas, terima kasih!"]);

$rows = $pdo->query(
    "SELECT p.title, u.username AS author, c.author_name AS commenter
     FROM comments c
     JOIN posts p ON p.id = c.post_id
     JOIN users u ON u.id = p.user_id"
)->fetchAll(PDO::FETCH_ASSOC);

echo json_encode($rows, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE), "\n";

Output yang Dihasilkan:

[
    {
        "title": "Belajar Merancang Schema",
        "author": "budi",
        "commenter": "Siti"
    }
]

Beberapa hal penting dari smoke test di atas:

1. Baris 4: PRAGMA foreign_keys = ON mengaktifkan penegakan foreign key pada koneksi SQLite; tanpa pragma ini, relasi hanya menjadi catatan di atas kertas.
2. Klausul FOREIGN KEY ... REFERENCES ... pada posts dan comments mendeklarasikan relasi persis seperti diagram ER: user_id merujuk ke users.id, post_id merujuk ke posts.id.
3. Kolom body dan status memiliki nilai bawaan (DEFAULT), sehingga penyisipan tanpa kedua kolom tersebut tetap sah.
4. Query JOIN tiga tabel di akhir skrip membuktikan rantai relasi berfungsi: komentar Siti bisa dilacak sampai ke penulis artikelnya, Budi.

Memeriksa Struktur Tabel #

Pada database yang sudah ada, kita sering perlu memeriksa struktur tabel tanpa membuka berkas definisi lama. SQLite menyimpan struktur tersebut di schema internal yang bisa dibaca lewat PRAGMA table_info(nama_tabel). Hasilnya berisi nama kolom, tipe, nilai bawaan, dan penanda primary key:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("CREATE TABLE posts (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title   TEXT NOT NULL,
    status  TEXT NOT NULL DEFAULT 'draft'
)");

foreach ($pdo->query("PRAGMA table_info(posts)") as $column) {
    $default = $column["dflt_value"] ?? "-";
    echo str_pad($column["name"], 8) . " " . $column["type"] . " | default: " . $default . "\n";
}

Output yang Dihasilkan:

id       INTEGER | default: -
user_id  INTEGER | default: -
title    TEXT | default: -
status   TEXT | default: 'draft'

Setiap baris hasil PRAGMA table_info mewakili satu kolom tabel posts. Kolom dflt_value bernilai NULL untuk kolom tanpa nilai bawaan, sehingga kode menggantinya dengan tanda - agar rapi; kolom status menunjukkan bawaan 'draft' lengkap dengan tanda kutipnya. Jika Anda memakai SQLite lewat terminal, perintah .schema posts pada alat sqlite3 menampilkan definisi CREATE TABLE yang setara.

Ringkasan #

Kita telah berjalan dari requirements mentah menjadi tiga tabel blog yang hidup dan teruji. Alur kerjanya konsisten: identifikasi entitas dari kata benda requirements, tetapkan nama snake_case plural, pilih tipe data sesuai kebutuhan, tentukan primary key, gambar diagram ER, lalu tulis CREATE TABLE dan jalankan smoke test. Namun fondasi ini belum menjamin data yang masuk benar — belum ada aturan yang mencegah dua baris identik atau komentar yang menunjuk artikel yang tidak ada. Aturan tersebut hidup di primary key dan foreign key, topik chapter berikutnya.

Referensi #

• PDO — php.net
• Koneksi PDO — php.net
• CREATE TABLE — SQLite
• Tipe Data dan Type Affinity — SQLite