Lewati ke konten
Kembali ke Blog
Database Design MariaDB MySQL

Best Practices Database Design: Normalization dan Relasi Tabel untuk Performa Optimal

Hendra Wijaya
Β· Β· 8 menit baca

Database design yang baik adalah fondasi dari aplikasi yang scalable dan maintainable. Setelah mereview ratusan database schema untuk client, saya menyaksikan pola-pola desain yang bagus dan buruk. Artikel ini akan membahas best practices untuk relational database design.

Prinsip Desain Database

1. ACID Compliance

Atomicity: Transaksi all-or-nothing
Consistency: Data selalu valid
Isolation: Transaksi tidak mengganggu
Durability: Data persist meski crash

-- Contoh transaksi ACID
START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

2. Design for Growth

  • Plan untuk 10x current volume
  • Avoid hard limits
  • Use proper data types
  • Consider partitioning untuk tabel besar

Normalization: Level dan Implementasi

First Normal Form (1NF)

Aturan:
– Atomic values (tidak ada repeating groups)
– Setiap kolom harus atomic
– Tidak ada array dalam satu cell

Jangan:

-- Anti-pattern: Multiple values dalam satu kolom
CREATE TABLE orders_bad (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    products VARCHAR(255)  -- 'laptop, mouse, keyboard'
);

Lakukan:

-- 1NF: Pisahkan ke tabel terpisah
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
price DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Second Normal Form (2NF)

Aturan:
– Sudah 1NF
– Tidak ada partial dependency (non-key attributes depend on full primary key)

Contoh Kasus:

-- Sebelum 2NF (composite PK: order_id + product_id)
CREATE TABLE order_items_2nf (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- Ini depend hanya pada product_id
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- Setelah 2NF
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(id)
);


CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2)
);

Third Normal Form (3NF)

Aturan:
– Sudah 2NF
– Tidak ada transitive dependency (non-key attributes tidak depend pada non-key attributes lain)

Contoh:

-- Sebelum 3NF
CREATE TABLE employees_bad (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100),  -- Transitif depend on department_id
    department_location VARCHAR(100)  -- Transitif depend on department_id
);

-- Setelah 3NF
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(id)
);


CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(100),
location_id INT,
FOREIGN KEY (location_id) REFERENCES locations(id)
);


CREATE TABLE locations (
id INT PRIMARY KEY,
name VARCHAR(100),
address VARCHAR(255)
);

BCNF (Boyce-Codd Normal Form)

Aturan:
– Untuk setiap functional dependency X β†’ Y, X harus superkey

Kapan 3NF cukup:
– Untuk 99% aplikasi bisnis
– Kecuali data warehouse/analytics

Desain Relasi Tabel

1. One-to-One (1:1)

Gunakan untuk:
– Pisahkan data sensitive (encryption)
– Split tabel besar (performance)
– Extended attributes

-- Users dan Profiles (1:1)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_profiles (
user_id INT PRIMARY KEY,
bio TEXT,
avatar_url VARCHAR(255),
birth_date DATE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

2. One-to-Many (1:N)

Most common relationship:

-- Categories dan Products (1:N)
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
category_id INT,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2),
FOREIGN KEY (category_id) REFERENCES categories(id)
);


CREATE INDEX idx_category_id ON products(category_id);

3. Many-to-Many (M:N)

Selalu gunakan junction table:

-- Students dan Courses (M:N)
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE courses (
id INT PRIMARY KEY AUTO_INCREMENT,
code VARCHAR(20),
name VARCHAR(100)
);


CREATE TABLE student_courses (
student_id INT,
course_id INT,
enrollment_date DATE,
grade DECIMAL(4,2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);

4. Self-Referencing

Untuk hierarchical data:

-- Employee hierarchy
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    position VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- Categories tree
CREATE TABLE categories (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
parent_id INT,
level INT,
FOREIGN KEY (parent_id) REFERENCES categories(id)
);


CREATE INDEX idx_parent_id ON categories(parent_id);

Best Practices Data Types

1. Integer Types

-- Pilih yang sesuai range
TINYINT   (-128 to 127) atau (0 to 255) UNSIGNED - Boolean, small counters
SMALLINT  (-32K to 32K) - Status codes
MEDIUMINT (-8M to 8M)   - Not recommended
INT       (-2B to 2B)   - Primary keys, counters
BIGINT    (-9E to 9E)   - Timestamps, large counters

2. String Types

-- CHAR untuk fixed length
CHAR(2)     -- Country code: 'US', 'ID'
CHAR(1)     -- Status: 'A', 'I'

-- VARCHAR untuk variable length
VARCHAR(50)  -- Username
VARCHAR(100) -- Email
VARCHAR(255) -- URLs, titles


-- TEXT untuk large content
TEXT         -- Descriptions
MEDIUMTEXT   -- Articles
LONGTEXT     -- JSON, logs

3. Numeric Types

-- DECIMAL untuk exact precision
DECIMAL(10,2)  -- Currency (99999999.99)
DECIMAL(5,4)   -- Exchange rates (99.9999)

-- FLOAT/DOUBLE untuk scientific (hati-hati dengan precision!)
FLOAT   -- 7 digit precision
DOUBLE  -- 15 digit precision

4. Date and Time

DATE        -- Birthday, anniversaries
TIME        -- Work hours
DATETIME    -- Timestamps, created_at
TIMESTAMP   -- Auto-update timestamps
YEAR        -- Copyright years

5. Boolean

-- MySQL/MariaDB tidak punya BOOLEAN native
TINYINT(1)  -- 0 atau 1
-- Atau ENUM
ENUM('active', 'inactive')

Indexing Best Practices

1. Primary Key Design

-- Auto-increment untuk OLTP
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    -- ...
);

-- UUID untuk distributed systems
CREATE TABLE orders (
id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
-- ...
);


-- Composite PK untuk junction tables
CREATE TABLE order_items (
order_id INT,
item_number INT,
-- ...
PRIMARY KEY (order_id, item_number)
);

2. Foreign Key Indexes

-- Selalu index FK columns
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_order_id (order_id),      -- untuk JOIN
    INDEX idx_product_id (product_id)   -- untuk JOIN
);

3. Query Pattern Indexes

-- WHERE clause columns
CREATE INDEX idx_status_created ON orders(status, created_at);

-- ORDER BY columns
CREATE INDEX idx_created_sort ON posts(created_at DESC);


-- Covering index
CREATE INDEX idx_user_covering ON orders(user_id, status, total_amount);
-- Query: SELECT user_id, status, total FROM orders WHERE user_id = ?

Anti-Patterns yang Harus Dihindari

1. EAV (Entity-Attribute-Value)

Jangan:

-- Anti-pattern: EAV
CREATE TABLE attributes (
    entity_id INT,
    attribute_name VARCHAR(50),
    attribute_value VARCHAR(255)
);
-- Sulit query, no type safety, no constraints

Solusi:
– Use proper columns
– Atau JSON column (MySQL 5.7+) untuk truly dynamic data

2. Polymorphic Associations

Jangan:

-- Anti-pattern
CREATE TABLE comments (
    id INT PRIMARY KEY,
    commentable_id INT,
    commentable_type VARCHAR(20)  -- 'post', 'video', 'product'
);
-- Tidak bisa FK constraint

Solusi:

-- Separate tables
CREATE TABLE post_comments (...);
CREATE TABLE video_comments (...);
-- Atau
CREATE TABLE comments (
    id INT PRIMARY KEY,
    post_id INT NULL,
    video_id INT NULL,
    CHECK (post_id IS NOT NULL OR video_id IS NOT NULL)
);

3. Not Using Foreign Keys

Jangan:

-- No FK constraints
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,  -- No FK!
    ...
);

Lakukan:

-- With FK
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    ...
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

4. Storing Calculated Values

Jangan:

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    quantity INT,
    unit_price DECIMAL(10,2),
    total_price DECIMAL(10,2)  -- Calculated: quantity * unit_price
);
-- Data inconsistency risk!

Solusi:
– Gunakan generated column (MySQL 5.7+)
– Atau calculate saat query dengan view

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    quantity INT,
    unit_price DECIMAL(10,2),
    total_price DECIMAL(10,2) AS (quantity * unit_price) STORED
);

Constraint dan Validation

1. NOT NULL untuk Required Fields

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

2. CHECK Constraints (MySQL 8.0.16+)

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    status ENUM('active', 'inactive') DEFAULT 'active',
    CHECK (price >= 0),
    CHECK (stock >= 0)
);

3. UNIQUE Constraints

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    UNIQUE KEY uk_phone (phone)  -- NULL diizinkan
);

Denormalization: Kapan Diperlukan

1. Read-Heavy Systems

-- Counter table untuk read-heavy
CREATE TABLE post_stats (
    post_id INT PRIMARY KEY,
    view_count INT DEFAULT 0,
    comment_count INT DEFAULT 0,
    like_count INT DEFAULT 0,
    last_updated TIMESTAMP
);
-- Update via trigger atau batch job

2. Reporting Tables

-- Materialized view (manual)
CREATE TABLE daily_sales_summary (
    date DATE PRIMARY KEY,
    total_orders INT,
    total_revenue DECIMAL(12,2),
    avg_order_value DECIMAL(10,2)
);
-- Refresh via scheduled job

Kesimpulan

Database design yang baik memerlukan keseimbangan antara:
– Normalization (data integrity)
– Performance (query optimization)
– Flexibility (future changes)
– Maintainability (developer experience)

Mulai dengan 3NF, lalu denormalize jika ada masalah performa yang jelas.

Ditulis oleh

Hendra Wijaya

Hanya hamba Allah Ta'ala yang berusaha berbuat baik..

Artikel Sebelumnya Tutorial Hugo Partials: Membuat Reusable Template Components Artikel Selanjutnya Cara Menghasilkan $1000 per Bulan dari YouTube: Complete Blueprint

Artikel Terkait

Cara Install MySQL di Windows 10 dan 11: Panduan Lengkap Server Setup

Cara Install MySQL di Windows 10 dan 11: Panduan Lengkap Server Setup

Februari 3, 2026
Cara Rename Database, Tabel, dan Column di MySQL dengan Aman

Cara Rename Database, Tabel, dan Column di MySQL dengan Aman

Februari 3, 2026
MariaDB Spider Storage Engine: Sharding untuk Scalable Database

MariaDB Spider Storage Engine: Sharding untuk Scalable Database

Februari 3, 2026

Tinggalkan Komentar

Email tidak akan ditampilkan.