Menu
📱 Lihat versi lengkap (non-AMP)
Database Design MySQL MariaDB

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

Editor: Hendra WIjaya
Update: 3 February 2026
Baca: 7 menit

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

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.

Artikel Terkait

Bagikan:

Link Postingan: https://www.tirinfo.com/best-practices-database-design-normalization-relasi/