Kursus MariaDB / Mengurangi Pengulangan Data dengan Normalisasi Desain Pesanan
Normalisasi menganalisis kunci yang menentukan setiap fakta dan memisahkan tabel untuk mengurangi pengulangan informasi saat ini serta ketidakkonsistenan saat perubahan. Membedakan atribut pelanggan atau produk sekarang dari fakta pembelian memungkinkan nama diperbarui di satu tempat sambil mempertahankan harga satuan historis pesanan.
Temukan masalah akibat pengulangan fakta yang sama
Menyalin nama pelanggan saat ini ke setiap rincian pesanan dapat membuat satu pelanggan mempunyai beberapa nama setelah pembaruan sebagian. Menghapus rincian terakhir juga dapat menghapus satu-satunya informasi pelanggan, dan pelanggan baru mungkin tidak dapat disimpan sebelum memesan. Pemisahan subjek fakta mengurangi anomali pembaruan, penghapusan, serta penyisipan ini.
Anomali adalah keadaan yang membuat operasi biasa menghasilkan kehilangan informasi atau ketidakkonsistenan yang tidak diinginkan. Panduan relasi dan kunci asing menjelaskan hubungan antarobjek yang perlu didefinisikan secara nyata, bukan hanya digambar sebagai beberapa tabel.
| Fakta | Kunci penentu | Tempat penyimpanan |
|---|---|---|
| Nama dan kota pelanggan saat ini | customer_id | customers |
| Nama dan harga jual produk saat ini | product_id | products |
| Pelanggan, waktu, dan status pesanan | order_id | orders |
| Jumlah dan harga satuan pembelian | order_id + product_id | items |
Arti nilai atomik bergantung pada kebutuhan penggunaan. Daftar produk yang dipisahkan koma tidak sesuai jika jumlah setiap produk harus dicari atau dijumlahkan, tetapi tidak berarti setiap teks atau JSON selalu harus dipecah tanpa mempertimbangkan kebutuhan.
Penjelasan bentuk normal ketiga MariaDB membahas ketergantungan tidak langsung; referensi kunci asing menjelaskan batasan hubungan yang dapat diberlakukan.
Buat data terpisah dan tabel pembanding gabungan
Hubungkan ke server belajar sebagai course_user dan pilih shopcourse. Panduan klien dan berkas SQL UTF-8 menjelaskan penyimpanan serta pelaksanaan menggunakan SOURCE C:/mariadb-lab/lesson.sql;. Ganti jalur dengan berkas sebenarnya dan jalankan SQL di dalam klien MariaDB.
Sebelum membuat c37_customers, c37_products, c37_orders, dan c37_items, periksa SELECT VERSION(), DATABASE(); serta SHOW TABLES;. Jika objek sudah ada, periksa definisi dan barisnya dahulu. Gunakan hanya objek latihan yang boleh dibuang; jangan mengganti tabel aplikasi. Blok berikut menyediakan persiapan lengkap.
USE shopcourse;
CREATE TABLE c37_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c37_order_customer FOREIGN KEY (customer_id)
REFERENCES c37_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c37_item_order FOREIGN KEY (order_id)
REFERENCES c37_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c37_item_product FOREIGN KEY (product_id)
REFERENCES c37_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c37_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c37_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c37_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c37_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
SQL mempertahankan nama dan kota Korea dari sumber. Produk 1–4 adalah buku catatan, pena, cangkir, dan lampu. Pelanggan 1 serta 3 berada di Seoul, pelanggan 2 di Busan, dan pelanggan 4 di Daejeon. Pertahankan teks contoh dengan UTF-8 dan gunakan ID untuk membandingkan hasil.
Pastikan c37_flat juga belum ada. CREATE TABLE AS SELECT membuat salinan pembanding untuk mengamati pengulangan. Perintah itu tidak otomatis menyalin seluruh kunci dan batasan sumber sehingga bukan cadangan lengkap. Pastikan mesin tabel pembanding mendukung transaksi sebelum mengandalkan ROLLBACK pada latihan ini.
CREATE TABLE c37_flat AS
SELECT o.order_id,i.product_id,c.customer_id,c.customer_name,
p.product_name,i.quantity,i.unit_price
FROM c37_orders o JOIN c37_customers c ON c.customer_id=o.customer_id
JOIN c37_items i ON i.order_id=o.order_id
JOIN c37_products p ON p.product_id=i.product_id;
SELECT customer_id,customer_name,COUNT(*) AS repeated_rows
FROM c37_flat GROUP BY customer_id,customer_name ORDER BY customer_id;
START TRANSACTION;
UPDATE c37_flat SET customer_name='김민수 변경' WHERE order_id=101;
SELECT DISTINCT customer_id,customer_name FROM c37_flat
WHERE customer_id=1 ORDER BY customer_name;
ROLLBACK;
Nama pelanggan 1 muncul pada tiga baris rincian. Mengubah hanya salinan pada pesanan 101 membuat nama lama dan nama berubah, 김민수 변경, hadir bersamaan untuk pelanggan yang sama. ROLLBACK membatalkan perubahan tabel pembanding; tabel pelanggan terpisah tidak diubah oleh blok ini.
Salinan c37_flat juga bukan view: perubahan tabel pelanggan sesudah pembuatan salinan tidak otomatis memperbarui data salinan. Panduan VIEW menjelaskan perbedaan definisi kueri dari baris yang disimpan secara terpisah.
Telusuri ketergantungan sebagian dan tidak langsung
Pada kunci gabungan order_id dan product_id, tanggal pesanan hanya ditentukan oleh order_id. Menyimpannya pada setiap rincian berarti mengulang fakta yang bergantung hanya pada sebagian kunci. Menempatkannya di orders memisahkan fakta pesanan dari jumlah barang dan harga transaksi pada rincian.
Pesanan menentukan pelanggannya dan pelanggan menentukan nama pelanggan saat ini. Mengulang nama saat ini pada setiap pesanan menyebarkan pembaruan ke banyak tempat. Rujukan customer_id menyediakan satu sumber nama saat ini. Pertanyaan tentang ketergantungan ini memberi arti praktis pada pemeriksaan bentuk normal kedua dan ketiga.
Penjelasan bentuk normal kedua MariaDB membahas penghilangan ketergantungan sebagian setelah bentuk normal pertama. Pemeriksaan bentuk normal ketiga kemudian memperhatikan ketergantungan tidak langsung antarfakta nonkunci. Mulailah dari arti data dan aturan kunci sebenarnya agar pemisahan mempunyai alasan yang dapat dijelaskan.
Pertahankan harga historis sebagai fakta yang berbeda
Jangan menghapus items.unit_price hanya karena nilainya saat ini sama dengan products.price. Kolom pertama merekam harga saat pembelian, sedangkan yang kedua harga jual sekarang. Keduanya mewakili fakta pada waktu berbeda; total pesanan lama membutuhkan nilai transaksi setelah harga katalog berubah.
Pada contoh, dua unit produk 1 dengan harga pembelian 3500 tetap bernilai 7000 meskipun harga katalog diubah menjadi 4000. Perubahan katalog tidak seharusnya menulis ulang harga transaksi yang perlu dipertahankan. Bedakan nilai yang memang mewakili fakta sama dari dua nilai yang kebetulan sama sekarang.
Alamat pengiriman atau nama tampilan juga dapat memerlukan rekaman saat pembelian. Berikan arti, waktu pencatatan, serta kebijakan pembaruan yang jelas bagi atribut saat ini dan bukti historis. Menyimpan riwayat yang diperlukan berbeda dari menggandakan fakta saat ini tanpa aturan pemeliharaan.
Terapkan kunci dan kondisi bisnis yang dimaksud
Membuat empat tabel saja tidak menolak referensi yang salah. Definisikan PRIMARY KEY, UNIQUE sesuai kebutuhan, NOT NULL, dan kunci asing, kemudian uji referensi induk yang tidak ada serta kunci ganda. Pelanggan tanpa pesanan dapat disimpan terpisah, dan penghapusan rincian terakhir tidak perlu menghapus identitas pelanggan.
Model sederhana ini menggabungkan satu produk menjadi satu baris rincian dalam satu pesanan. Pilihan produk, nomor kelompok barang, atau pengiriman sebagian mungkin membutuhkan ID rincian terpisah dan kunci tambahan. Sesuaikan arti baris dengan kebutuhan daripada menganggap contoh sebagai jawaban lengkap untuk semua sistem pesanan.
Ukur kebutuhan sebelum melakukan denormalisasi
Normalisasi memberi dasar konsistensi pembaruan. Kecepatan kueri bergantung pada kueri nyata, indeks, serta volume data. Jika diperlukan tabel ringkasan laporan, tentukan sumber data yang berwenang, aturan penyegaran, dan pemulihan kesalahan. Banyak JOIN saja bukan alasan mengulang informasi saat ini tanpa kebijakan pemeliharaan.
Sebelum mengulang latihan, periksa basis data yang dipilih, SHOW CREATE TABLE, serta isi seluruh objek dan cadangkan data yang perlu dipertahankan. Hapus hanya objek c37_ milik artikel ini, dimulai dari salinan pembanding dan tabel anak, lalu induk. DROP TABLE menghapus struktur dan data sekaligus serta tidak dibalik dengan ROLLBACK biasa; pisahkan pembersihan dari percobaan transaksi.
DROP TABLE shopcourse.c37_flat;
DROP TABLE shopcourse.c37_items;
DROP TABLE shopcourse.c37_orders;
DROP TABLE shopcourse.c37_products;
DROP TABLE shopcourse.c37_customers;




