Database Design Fundamentals — Panduan Lengkap Merancang Database Scalable

Pengenalan: Fondasi Aplikasi yang Reliable dan Scalable

Database adalah jantung dari hampir setiap aplikasi modern. Desain database yang baik adalah fondasi dari aplikasi yang reliable, scalable, dan maintainable. Banyak developer junior fokus pada kode aplikasi namun mengabaikan pentingnya desain database yang solid. Kenyataannya, database design yang buruk dapat menyebabkan performance issues, data integrity problems, dan kesulitan dalam scaling aplikasi.

Dalam panduan ini, kita akan menjelajahi prinsip-prinsip fundamental database design, dari normalisasi data untuk menghilangkan redundancy, hingga advanced topics seperti indexing strategies, query optimization, dan denormalization trade-offs. Dengan pemahaman mendalam ini, Kamu akan dapat merancang database yang tidak hanya efisien hari ini, tetapi juga siap untuk pertumbuhan masa depan.

Daftar Isi

• Data Modeling Basics
• Normalization: 1NF, 2NF, 3NF
• Primary Keys dan Foreign Keys
• Relationships: One-to-Many, Many-to-Many
• Indexing Strategies
• Query Optimization
• ACID Properties
• Performance Tuning
• Database Types dan Trade-offs
• Denormalization dan Caching
• Real-World Design Patterns
• Kesimpulan

Data Modeling Basics

Data modeling adalah proses mengidentifikasi entities, attributes, dan relationships dalam domain aplikasi Kamu. Ini adalah foundation dari good database design.

-- Contoh: Blog System dengan Users, Posts, Comments

-- Entity: User
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Entity: Post
CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  content LONGTEXT NOT NULL,
  published_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Entity: Comment
CREATE TABLE comments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

Entity Relationship Diagram (ERD):

┌─────────┐
│ USERS   │
├─────────┤
│ id (PK) │
│ username│
│ email   │
└────┬────┘
     │ 1
     │ has many
     ├─────────────────┐
     │                 │
     ├─────────────────┤
     │                 │
     │ Many            │ Many
     │
   ┌─────────┐      ┌──────────┐
   │ POSTS   │      │ COMMENTS │
   ├─────────┤      ├──────────┤
   │ id (PK) │      │ id (PK)  │
   │ user_id │      │ post_id  │
   │ title   │      │ user_id  │
   │ content │      │ content  │
   └─────────┘      └──────────┘

Normalization: 1NF, 2NF, 3NF

Normalisasi adalah proses sistematis mengorganisir data ke well-structured tables, mengeliminasi redundancy dan improve data integrity. Ini mengikuti normal forms yang progressively enforce stricter rules.

First Normal Form (1NF)

Setiap kolom harus mengandung hanya atomic (indivisible) values.

-- ✗ BURUK: Kolom berisi data yang tidak atomic
CREATE TABLE authors_bad (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  books VARCHAR(255) -- "Harry Potter, The Hobbit, Dune"
);

-- ✓ BAIK: Memisahkan ke table terpisah
CREATE TABLE authors (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

Second Normal Form (2NF)

Harus dalam 1NF + semua non-key attributes harus fully dependent pada entire primary key, bukan hanya bagian dari composite key.

-- ✗ BURUK: student_name dependent hanya pada student_id, bukan course
CREATE TABLE student_courses_bad (
  student_id INT,
  course_id INT,
  student_name VARCHAR(100), -- Partial dependency!
  grade CHAR(1),
  PRIMARY KEY (student_id, course_id)
);

-- ✓ BAIK: Memisahkan student info
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  grade CHAR(1),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id)
);

Third Normal Form (3NF)

Harus dalam 2NF + tidak ada transitive dependencies. Non-key attributes harus depend directly pada primary key, bukan pada non-key attributes lainnya.

-- ✗ BURUK: Transitive dependency - department_name dependent pada department_id, bukan employee_id
CREATE TABLE employees_bad (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(100),
  department_id INT,
  department_name VARCHAR(100) -- Transitive dependency!
);

-- ✓ BAIK: Memisahkan department info
CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  location VARCHAR(100)
);

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

Primary Keys dan Foreign Keys

Primary Keys dan Foreign Keys adalah foundation dari relational database integrity, establishing unique identification dan relationships antar tables.

-- Primary Key (PK): Unique identifier untuk setiap row
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT, -- PK
  email VARCHAR(100) UNIQUE NOT NULL,
  name VARCHAR(100)
);

-- Foreign Key (FK): Link ke primary key di table lain
CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  user_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Composite Primary Key
CREATE TABLE user_roles (
  user_id INT,
  role_id INT,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

ON DELETE Actions:

• CASCADE: Hapus parent → child juga dihapus
• SET NULL: Hapus parent → FK menjadi NULL
• RESTRICT: Cegah delete jika ada child
• NO ACTION: Similar ke RESTRICT

Relationships: One-to-Many, Many-to-Many

Relationships mendefinisikan bagaimana data dalam table berbeda berhubungan satu sama lain. Memahami relationship types adalah essential untuk design yang correct.

One-to-Many Relationship

-- User memiliki banyak Posts
CREATE TABLE users (
  id INT PRIMARY KEY
);

CREATE TABLE posts (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Query: Dapatkan semua posts dari user tertentu
SELECT p.* FROM posts p 
WHERE p.user_id = 1;

Many-to-Many Relationship

-- Student dapat enroll di banyak Course
-- Course dapat memiliki banyak Student

-- Join table (pivot table)
CREATE TABLE students_courses (
  student_id INT,
  course_id INT,
  enrollment_date DATE,
  grade CHAR(1),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Query: Dapatkan semua courses untuk student_id 5
SELECT c.* FROM courses c
INNER JOIN students_courses sc ON c.id = sc.course_id
WHERE sc.student_id = 5;

Indexing Strategies

Indexing adalah salah satu teknik paling powerful untuk query optimization. Indexes create efficient lookup structures yang dramatically reduce query execution time.

-- Single Column Index
CREATE INDEX idx_users_email ON users(email);

-- Query akan cepat karena menggunakan index
SELECT * FROM users WHERE email = '[email protected]'; -- O(log n)

-- Multi-Column Index (Composite Index)
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

-- Query yang efficient dengan composite index
SELECT * FROM posts 
WHERE user_id = 1 AND created_at > '2025-01-01'
ORDER BY created_at DESC;

-- Unique Index (enforce uniqueness + faster lookups)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Full-text Index untuk search
CREATE FULLTEXT INDEX idx_posts_title_content ON posts(title, content);

SELECT * FROM posts 
WHERE MATCH(title, content) AGAINST('web development' IN BOOLEAN MODE);

Index Best Practices:

• Index pada kolom yang sering di-WHERE clause
• Index pada kolom JOIN (Foreign Keys)
• Composite index untuk queries dengan multiple conditions
• Hindari indexing kolom dengan banyak NULL values
• Regular maintenance: ANALYZE, OPTIMIZE tables

Query Optimization

Menulis efficient queries adalah critical untuk application performance.

-- ✗ BURUK: N+1 Query problem
SELECT * FROM posts; -- Query 1

-- Dalam aplikasi:
for each post:
  SELECT * FROM users WHERE id = post.user_id; -- N queries!

-- ✓ BAIK: JOIN untuk menghindari N+1
SELECT p.*, u.name as author_name 
FROM posts p
INNER JOIN users u ON p.user_id = u.id;

-- ✗ BURUK: SELECT * (memuat kolom yang tidak diperlukan)
SELECT * FROM posts WHERE user_id = 1;

-- ✓ BAIK: SELECT hanya kolom yang diperlukan
SELECT id, title, created_at FROM posts WHERE user_id = 1;

-- ✗ BURUK: Fungsi pada kolom indexed
SELECT * FROM users WHERE YEAR(created_at) = 2025;

-- ✓ BAIK: Operasi pada literal value
SELECT * FROM users WHERE created_at >= '2025-01-01' 
AND created_at < '2026-01-01';

-- ✗ BURUK: OR dengan multiple conditions
SELECT * FROM posts WHERE user_id = 1 OR user_id = 2 OR user_id = 3;

-- ✓ BAIK: Gunakan IN clause
SELECT * FROM posts WHERE user_id IN (1, 2, 3);

ACID Properties

ACID properties memastikan database transactions yang reliable dan consistent.

-- ACID Example: Transfer uang antar akun

-- Atomicity: Semua atau tidak sama sekali
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT; -- Atau ROLLBACK jika error

-- Consistency: Data selalu dalam valid state
-- Constraint: account balance tidak bisa negatif
ALTER TABLE accounts ADD CONSTRAINT check_positive_balance 
CHECK (balance >= 0);

-- Isolation: Transactions terpisah, tidak interfere
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1; -- Row locked untuk exclusive access
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Durability: Committed data persistent
-- Database memastikan data tersimpan permanent di disk
COMMIT; -- Data aman di disk

Performance Tuning

Query Execution Plan Analysis

-- EXPLAIN: Melihat bagaimana MySQL mengeksekusi query
EXPLAIN SELECT * FROM posts WHERE user_id = 1;

-- Output:
-- id  select_type  table  type  key           rows  Extra
-- 1   SIMPLE       posts  ref   idx_user_id   5     Using index

-- type='ref' berarti menggunakan index (good!)
-- type='ALL' berarti full table scan (bad!)

-- EXPLAIN EXTENDED: Detail informasi
EXPLAIN EXTENDED SELECT p.*, u.name FROM posts p 
JOIN users u ON p.user_id = u.id;

Denormalization untuk Read Performance

-- NORMALIZED (3NF):
-- Perlu JOIN untuk aggregate data
SELECT p.id, p.title, COUNT(c.id) as comment_count
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id;

-- DENORMALIZED: Cepat untuk read tapi redundant
ALTER TABLE posts ADD COLUMN comment_count INT DEFAULT 0;

-- Update denormalized field saat ada comment baru
INSERT INTO comments (...) VALUES (...);
UPDATE posts SET comment_count = comment_count + 1 WHERE id = ?;

-- Query sekarang instant
SELECT id, title, comment_count FROM posts WHERE id = 1;

Database Types dan Trade-offs

Relational Database (SQL)

-- MySQL, PostgreSQL, Oracle
-- Structured data, ACID guaranteed, complex queries

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  created_at TIMESTAMP
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

Pros: ACID, Data integrity, Complex queries, Scalable for structured data Cons: Rigid schema, Vertical scaling limited, Slower for unstructured data

NoSQL Database (MongoDB)

// Document-oriented, flexible schema, horizontal scalability

// User document
{
  "_id": ObjectId("..."),
  "name": "John Doe",
  "email": "[email protected]",
  "orders": [
    {
      "id": 1,
      "amount": 99.99,
      "date": ISODate("2025-01-10")
    }
  ]
}

// Query
db.users.findOne({ email: "[email protected]" })

Pros: Flexible schema, Horizontal scalability, Fast for unstructured data Cons: No ACID (in traditional sense), Denormalization redundancy, Eventual consistency

Denormalization dan Caching

Caching Strategy

-- Problem: Expensive query
SELECT u.*, 
  COUNT(p.id) as post_count,
  COUNT(c.id) as comment_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
LEFT JOIN comments c ON p.id = c.post_id
WHERE u.id = 1;

-- Solution: Cache di Redis
redis.set("user:1:stats", json.stringify({
  post_count: 42,
  comment_count: 156
}), EX: 3600); // 1 hour TTL

-- Di aplikasi: Cek cache dulu
stats = redis.get("user:1:stats");
if (!stats) {
  stats = executeExpensiveQuery();
  redis.set("user:1:stats", stats, EX: 3600);
}

Real-World Design Patterns

Event Sourcing Pattern

-- Simpan semua events, bukan current state

CREATE TABLE events (
  id INT PRIMARY KEY AUTO_INCREMENT,
  aggregate_id INT, -- User ID, Order ID, etc
  aggregate_type VARCHAR(50), -- 'user', 'order'
  event_type VARCHAR(100), -- 'user_created', 'payment_received'
  data JSON, -- Event payload
  timestamp TIMESTAMP,
  version INT
);

-- Example events
INSERT INTO events VALUES 
  (1, 1, 'order', 'order_created', '{"amount": 100}', NOW(), 1),
  (2, 1, 'order', 'payment_received', '{"amount": 100}', NOW(), 2),
  (3, 1, 'order', 'order_shipped', '{"tracking": "..."}', NOW(), 3);

-- Rebuild current state dari events
SELECT data FROM events 
WHERE aggregate_id = 1 AND aggregate_type = 'order'
ORDER BY version;

Soft Deletes Pattern

-- Jangan delete data, gunakan flag
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  deleted_at TIMESTAMP NULL -- NULL = active, timestamp = deleted
);

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = 1;

-- Query hanya active users
SELECT * FROM users WHERE deleted_at IS NULL;

-- Query dengan deleted users
SELECT * FROM users WHERE id = 1; -- Dengan/tanpa deleted

Kesimpulan

Database design yang solid adalah investasi jangka panjang dalam kesuksesan aplikasi. Dengan memahami normalization principles, relationship types, indexing strategies, dan query optimization techniques, Kamu dapat membangun database yang tidak hanya memenuhi kebutuhan hari ini tetapi juga siap untuk growth masa depan.

Checklist untuk Production Database:

• ✓ Apply normalization principles (at least 3NF)
• ✓ Implement proper indexes pada frequently queried columns
• ✓ Use transactions untuk maintain data consistency
• ✓ Regular monitoring dan maintenance (ANALYZE, OPTIMIZE)
• ✓ Implement backup dan disaster recovery plans
• ✓ Use connection pooling untuk efficiency
• ✓ Document schema dan relationships clearly
• ✓ Plan untuk scalability (read replicas, sharding)

-- Create index untuk faster queries
CREATE INDEX idx_user_email ON users(email);

Query Optimization

• Use EXPLAIN untuk analyze queries
• Select specific columns bukan *
• Use JOINs efficiently
• Avoid N+1 queries

Database Types

• SQL: PostgreSQL, MySQL, MariaDB
• NoSQL: MongoDB, Firebase, DynamoDB
• Graph: Neo4j
• Search: Elasticsearch

Pilih database yang sesuai dengan kebutuhan aplikasi Kamu!