Database Design: Desain Database yang Baik 📐

Halo SQL Explorers! 👋 Setelah belajar Transaction Management, sekarang kita bakal belajar tentang Database Design. Ini adalah fondasi untuk database yang baik!

Prinsip Database Design 📝

Normalization: Menghindari redundansi data
Relationships: Hubungan antar tabel yang tepat
Integrity: Menjaga konsistensi data
Scalability: Bisa berkembang sesuai kebutuhan

Database Schema Kita 🗂️

-- Tabel categories
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel products
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    nama VARCHAR(200) NOT NULL,
    harga DECIMAL(15,2) NOT NULL,
    stok INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Tabel customers
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    kota VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel orders
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    tanggal TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'success', 'cancelled') NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Tabel order_items
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(15,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Normalization Forms 📊

First Normal Form (1NF)

Setiap kolom atomic (tidak bisa dipecah lagi)
Tidak ada repeating groups
Primary key unik

-- Contoh yang salah (not 1NF)
CREATE TABLE orders_wrong (
    id INT PRIMARY KEY,
    customer_id INT,
    products VARCHAR(255)  -- "1,2,3" (wrong!)
);

-- Contoh yang benar (1NF)
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT
);

Second Normal Form (2NF)

Sudah 1NF
Semua non-key attributes tergantung pada seluruh primary key

-- Contoh yang salah (not 2NF)
CREATE TABLE order_items_wrong (
    order_id INT,
    product_id INT,
    quantity INT,
    product_name VARCHAR(100),  -- Tergantung hanya pada product_id
    PRIMARY KEY (order_id, product_id)
);

-- Contoh yang benar (2NF)
CREATE TABLE products (
    id INT PRIMARY KEY,
    nama VARCHAR(100)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Third Normal Form (3NF)

Sudah 2NF
Tidak ada transitive dependencies

-- Contoh yang salah (not 3NF)
CREATE TABLE orders_wrong (
    id INT PRIMARY KEY,
    customer_id INT,
    customer_city VARCHAR(100),  -- Tergantung pada customer_id
    total_amount DECIMAL(15,2)
);

-- Contoh yang benar (3NF)
CREATE TABLE customers (
    id INT PRIMARY KEY,
    kota VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    total_amount DECIMAL(15,2)
);

Relationships 🔄

One-to-One

-- Contoh: Customer profile
CREATE TABLE customer_profiles (
    customer_id INT PRIMARY KEY,
    alamat_lengkap TEXT,
    no_telepon VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

One-to-Many

-- Contoh: Customer -> Orders
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Many-to-Many

-- Contoh: Products <-> Orders
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Best Practices 💡

Naming Conventions

-- Konsisten dalam penamaan
CREATE TABLE products (
    id INT PRIMARY KEY,    -- Selalu 'id' untuk primary key
    category_id INT,       -- [table]_id untuk foreign key
    nama VARCHAR(100),     -- Nama kolom lowercase
    created_at TIMESTAMP   -- Timestamp selalu _at
);

Data Types

-- Pilih tipe data yang tepat
CREATE TABLE products (
    id INT,               -- INT untuk ID
    nama VARCHAR(100),    -- VARCHAR untuk text pendek
    deskripsi TEXT,       -- TEXT untuk text panjang
    harga DECIMAL(15,2),  -- DECIMAL untuk uang
    created_at TIMESTAMP  -- TIMESTAMP untuk waktu
);

Indexing

-- Index untuk foreign keys
CREATE INDEX idx_category_id ON products(category_id);

-- Index untuk kolom yang sering di-query
CREATE INDEX idx_email ON customers(email);

Contoh Implementasi 🛠️

Menambah Fitur Baru

-- Tambah fitur rating produk
CREATE TABLE product_reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    komentar TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    UNIQUE KEY unique_review (product_id, customer_id)  -- Satu customer satu review
);

-- View untuk rata-rata rating
CREATE VIEW product_ratings AS
SELECT 
    p.id,
    p.nama,
    COALESCE(AVG(r.rating), 0) as avg_rating,
    COUNT(r.id) as total_reviews
FROM products p
LEFT JOIN product_reviews r ON p.id = r.product_id
GROUP BY p.id, p.nama;

Audit Trail

-- Tracking perubahan harga produk
CREATE TABLE price_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    harga_lama DECIMAL(15,2) NOT NULL,
    harga_baru DECIMAL(15,2) NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Trigger untuk track perubahan harga
DELIMITER //
CREATE TRIGGER track_price_changes
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.harga != OLD.harga THEN
        INSERT INTO price_history (product_id, harga_lama, harga_baru)
        VALUES (OLD.id, OLD.harga, NEW.harga);
    END IF;
END //
DELIMITER ;

Materi Selanjutnya 📚

Selamat! Kamu sudah menyelesaikan materi SQL Intermediate! 🎉

Untuk level berikutnya, kamu bisa lanjut ke SQL Advanced untuk mempelajari:

Complex Queries
Stored Procedures
Triggers
Dan masih banyak lagi! 🚀