Desain Tabel dan Normalisasi (Skema 3 Lapis)

Dataset untuk artikel ini — orders_flat (contoh tidak ternormalisasi)

Normalisasi (teknik desain yang memecah tabel untuk mengurangi data yang duplikat dan tidak konsisten) adalah gagasan memecah satu tabel raksasa yang menyimpan segalanya menjadi beberapa tabel yang lebih mudah dijaga konsistensinya.

Di artikel ini kamu akan melihat masalah yang muncul di tabel tidak ternormalisasi dengan kolom berulang, lalu membangun pemahaman tentang 1NF / 2NF / 3NF dan skema 3 lapis lewat latihan desain langsung.

Dataset-nya adalah orders_flat (2 baris), yang menjejalkan data order ke dalam satu tabel.

Ini adalah tabel tidak ternormalisasi klasik di mana nama customer, nomor telepon, produk 1, dan produk 2 semuanya disusun dalam satu baris — dan kamu akan menormalisasinya menjadi tiga tabel customer / order_record / order_line.

Sebelum masuk ke latihan, lihat definisi kolom dan sampel data dari tabel orders_flat.

① Jalankan PRAGMA table_info(orders_flat); untuk memeriksa nama kolom dan tipe. Perhatikan bagaimana kolom produk muncul sebagai kolom berulang seperti item1 / item2.

② Jalankan SELECT * FROM orders_flat; untuk mengintip semua baris. Amati juga bagaimana baris dengan hanya satu produk berakhir dengan string kosong atau NULL di item2.

Tiga anomali di tabel tidak ternormalisasi

Desain seperti orders_flat yang menjejalkan segalanya ke satu tabel mudah menghasilkan kontradiksi ketika kamu menambah, mengubah, atau menghapus data.

Contoh klasiknya ada tiga: anomali update / anomali insert / anomali delete.

Anomali update terjadi ketika fakta yang sama diduplikat di banyak baris — misalnya, kalau kamu ingin mengubah nomor telepon, kamu harus mengupdate setiap baris tanpa melewatkan satu pun.

Anomali insert adalah ketika kamu tidak bisa mendaftarkan customer yang belum melakukan order, karena tidak ada baris order untuk menempelkan informasi customer-nya.

Anomali delete adalah ketika menghapus satu order juga menghapus informasi yang ingin kamu simpan, seperti nomor telepon customer.

Ini terjadi karena desainnya tidak menjaga "satu fakta di satu tempat".

-- Contoh anomali delete: menghapus order 2 juga menghapus info kontak Bob
-- (amati perilaku di tabel sekali pakai flat_demo supaya orders_flat asli tidak tersentuh)
DROP TABLE IF EXISTS flat_demo;
CREATE TABLE flat_demo(
  order_id INTEGER, customer TEXT, customer_tel TEXT, product TEXT
);
INSERT INTO flat_demo VALUES
  (1,'Alice','03-1111','Pen'),
  (2,'Bob','03-2222','Clip');

-- Bob hanya punya satu order. Menghapusnya juga menghilangkan nomor telepon Bob 03-2222
DELETE FROM flat_demo WHERE order_id = 2;
SELECT * FROM flat_demo;
-- -> Info kontak Bob sekarang hilang dari database (anomali delete)

Reproduksi di tabel sekali pakai situasi di mana "Nomor telepon Alice berubah dan kamu ingin mengupdate-nya, tapi nomor telepon yang sama diduplikat di banyak baris." Latihan ini berdiri sendiri — kamu akan membuat tabel khusus flat_anomaly agar mandiri. (Kalau kamu menjalankannya dengan benar, penjelasan akan muncul.)

① DROP TABLE IF EXISTS tabel flat_anomaly, lalu CREATE dengan 4 kolom: order_id / customer / customer_tel / product.

② Masukkan 2 baris untuk order Alice (keduanya dengan nomor telepon yang sama 03-1111; produknya Pen dan Note).

③ Jalankan UPDATE yang mengubah nomor telepon Alice menjadi 03-9999 tapi hanya menyentuh satu baris (misalnya dengan kondisi seperti WHERE order_id = 1), lalu jalankan SELECT * FROM flat_anomaly; dan konfirmasi bahwa kedua nomor telepon tidak cocok.

1NF sampai 3NF — pecah langkah demi langkah

Tabel diorganisir melalui normalisasi dalam tahapan.

1NF — hilangkan kolom berulang

Buang kolom berulang yang diberi nomor seperti item1 / item2.

Sebelum — kolom berulang item1 / item2

Sesudah — satu baris per produk, dengan primary key komposit `(order_id, product)`

2NF — pindahkan kolom yang hanya bergantung pada sebagian primary key ke tabel lain

Tabel dari langkah sebelumnya memiliki primary key komposit (order_id, product). Tapi perhatikan: customer dan tel ditentukan oleh `order_id` saja (order_id yang sama selalu punya customer yang sama).

Memindahkan kolom yang bergantung pada hanya sebagian primary key (ketergantungan parsial) ke tabel terpisah adalah 2NF. Kamu memecahnya menjadi header order order_header dan detail order order_line.

Sebelum — customer dan tel diduplikat di setiap baris

Sesudah — dipecah menjadi 2 tabel

order_header (satu baris per order)

order_line (primary key komposit (order_id, product), di mana order_id adalah foreign key ke order_header)

3NF — pindahkan kolom yang ditentukan oleh kolom non-kunci ke tabel lain lagi

Melihat order_header dari langkah sebelumnya, tel tidak ditentukan oleh order_id tapi oleh `customer` (Alice yang sama selalu punya 03-1111).

Menghilangkan ketergantungan yang lewat kolom non-kunci lain alih-alih langsung dari primary key (ketergantungan transitif: order_id -> customer -> tel) adalah inti dari 3NF.

Kamu mengekstrak master customer customer dan meninggalkan order_record hanya menyimpan foreign key ke customer.

Sebelum — tel bergantung pada customer

Sesudah — dipecah menjadi 3 tabel

customer (master customer)

order_record (header order: referensi customer lewat foreign key)

order_line (detail order: sama dengan langkah sebelumnya)

Setelah dipecah sejauh ini, setiap fakta (nomor telepon customer, hubungan antara order dan customer, kuantitas produk dalam order) ada di tepat satu tempat. Itu menghilangkan anomali update, insert, dan delete.

Skema 3 lapis dan bentuk akhirnya — customer / order_record / order_line

Skema yang ternormalisasi bisa diorganisir sebagai tiga lapis dengan peran berbeda.

• Lapis master: entitas yang jarang berubah dan dirujuk oleh yang lain, seperti customer
• Lapis transaksi: tabel yang mencatat peristiwa bisnis, seperti order_record
• Lapis detail: tabel yang menyimpan baris-baris rincian dari satu peristiwa, seperti order_line

Dengan mengarahkan sisi referensi ke master lewat foreign key, setiap fakta (seperti nomor telepon customer) berakhir di satu tempat.

Bentuk akhirnya adalah tiga tabel ini:

customer(customer_id primary key, customer_name, customer_tel), order_record(order_id primary key, customer_id -> customer), order_line(order_id -> order_record, product, qty, primary key komposit (order_id, product)).

Nomor telepon ada hanya di satu baris customer, jadi update hanya satu baris; customer tanpa order tetap bisa ditambahkan ke customer (anomali insert terselesaikan); dan menghapus order meninggalkan customer di customer (anomali delete terselesaikan).

-- Manfaat normalisasi (read-only): nomor telepon customer ada hanya di satu baris customer
-- Dalam bentuk orders_flat kamu harus memperbaiki setiap baris order Alice = anomali update
UPDATE customer SET customer_tel = '03-9999'
WHERE customer_name = 'Alice';

-- Perbaiki satu tempat, dan nomor baru mengalir ke setiap order saat JOIN
SELECT o.order_id, c.customer_name, c.customer_tel
FROM order_record o
JOIN customer c ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Bayangkan kebutuhannya: "Normalisasi orders_flat menjadi tiga tabel — customer, order, dan detail order." Rancang skema akhir dengan foreign key yang menautkannya, pindahkan datanya, dan bangun ulang daftar asli dengan JOIN.

① Aktifkan pemeriksaan foreign key, lalu DROP TABLE IF EXISTS dengan urutan order_line -> order_record -> customer (membuang child dulu lebih aman).

② CREATE tabel customer (customer_id INTEGER primary key / customer_name TEXT NOT NULL / customer_tel TEXT).

③ CREATE tabel order_record (order_id INTEGER primary key / customer_id INTEGER NOT NULL, foreign key ke customer).

④ CREATE tabel order_line (order_id INTEGER, foreign key ke order_record / product TEXT NOT NULL / qty INTEGER NOT NULL / primary key komposit (order_id, product)).

⑤ Masukkan Alice(1, 03-1111) dan Bob(2, 03-2222) ke customer; order 1 -> customer 1 dan order 2 -> customer 2 ke order_record; Pen(order 1, 2) / Note(order 1, 1) / Clip(order 2, 5) ke order_line.

⑥ Join 3 tabel di order_id dan customer_id dan pilih order_id / customer_name / customer_tel / product / qty dengan urutan menaik order_id dan product untuk mengonfirmasi kamu bisa membangun ulang daftar asli.

Memakai tabel customer / order_record / order_line dari Latihan 2, konfirmasi satu per satu bahwa anomali update, insert, dan delete yang mengganggu tabel tidak ternormalisasi tidak lagi terjadi. Ini latihan terakhir di artikel.

① Anomali update terselesaikan: Ubah nomor telepon Alice menjadi '03-9999'. Konfirmasi bahwa itu adalah UPDATE satu baris pada tabel customer.

② Anomali insert terselesaikan: INSERT customer baru Charlie (customer_id 3 / customer_name 'Charlie' / customer_tel '03-3333') ke customer. Konfirmasi kamu bisa mendaftarkan customer tanpa order.

③ Anomali delete terselesaikan: Hapus order 1. Hapus dari child order_line dulu, lalu dari order_record. Jalankan SELECT * FROM customer ORDER BY customer_id; dan konfirmasi baris Alice masih ada di customer.

Tips — Selalu pasang primary key di setiap tabel

Baik sudah dinormalisasi atau belum, rancang setiap tabel dengan primary key (`PRIMARY KEY`). Primary key memberi kamu tiga hal:

- Identifikasi baris unik: tidak ada dua baris yang bisa memiliki kunci yang sama, jadi UPDATE / DELETE dengan andal menargetkan tepat satu baris lewat WHERE pk = ...

- Bisa dirujuk dari tabel lain: foreign key membutuhkan primary key (atau UNIQUE) di sisi lain. Ini fondasi untuk menautkan tabel yang kamu pecah lewat normalisasi

- Pengindeksan otomatis: kolom primary key mendapat indeks B-Tree secara otomatis, mempercepat pencarian kesetaraan dan JOIN

Kalau tidak ada satu kolom yang unik sendirian, pakai primary key komposit seperti PRIMARY KEY (order_id, product). Melewatkannya dengan "saya akan menambahkannya nanti" membuat baris duplikat tidak bisa dibedakan dan menyerahkan ketiga manfaat di atas.