Nguyên tắc thiết kế Database cho Web Applications

Thiết kế database tốt là nền tảng của một ứng dụng web thành công. Hãy cùng tìm hiểu các nguyên tắc cơ bản và patterns để thiết kế database hiệu quả.

Database Normalization

First Normal Form (1NF)

Mỗi cell chỉ chứa một giá trị atomic:

-- ❌ Không tuân thủ 1NF
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    phone_numbers VARCHAR(255) -- "123-456-7890, 098-765-4321"
);

-- ✅ Tuân thủ 1NF
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE user_phones (
    id INT PRIMARY KEY,
    user_id INT REFERENCES users(id),
    phone_number VARCHAR(20)
);

Second Normal Form (2NF)

Loại bỏ partial dependencies:

-- ❌ Không tuân thủ 2NF
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100), -- Phụ thuộc vào product_id
    product_price DECIMAL(10,2), -- Phụ thuộc vào product_id
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- ✅ Tuân thủ 2NF
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

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

Third Normal Form (3NF)

Loại bỏ transitive dependencies:

-- ❌ Không tuân thủ 3NF
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100), -- Phụ thuộc gián tiếp qua department_id
    department_budget DECIMAL(12,2) -- Phụ thuộc gián tiếp qua department_id
);

-- ✅ Tuân thủ 3NF
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    budget DECIMAL(12,2)
);

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

Khi nào nên Denormalize

Read-heavy workloads

-- Normalized (nhiều JOINs)
SELECT 
    u.name,
    p.title,
    c.content,
    COUNT(pl.id) as likes_count
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id
LEFT JOIN post_likes pl ON p.id = pl.post_id
GROUP BY u.id, p.id, c.id;

-- Denormalized (ít JOINs hơn)
CREATE TABLE post_stats (
    post_id INT PRIMARY KEY,
    likes_count INT DEFAULT 0,
    comments_count INT DEFAULT 0,
    shares_count INT DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexing Strategies

Single Column Indexes

-- Index cho frequent WHERE clauses
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created_at ON posts(created_at);
CREATE INDEX idx_orders_status ON orders(status);

Composite Indexes

-- Index cho multi-column queries
CREATE INDEX idx_user_posts ON posts(user_id, created_at DESC);
CREATE INDEX idx_product_category ON products(category_id, price, name);

-- Query sẽ sử dụng index hiệu quả
SELECT * FROM posts 
WHERE user_id = 123 
ORDER BY created_at DESC 
LIMIT 10;

Partial Indexes

-- Index chỉ cho records thỏa mãn điều kiện
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
CREATE INDEX idx_published_posts ON posts(created_at) WHERE status = 'published';

Covering Indexes

-- Index chứa tất cả columns cần thiết
CREATE INDEX idx_user_profile_covering 
ON users(id) 
INCLUDE (name, email, avatar_url, created_at);

-- Query không cần access table
SELECT id, name, email, avatar_url, created_at 
FROM users 
WHERE id = 123;

Relationship Design Patterns

One-to-Many

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    slug VARCHAR(100) UNIQUE
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(200),
    category_id INT NOT NULL REFERENCES categories(id),
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Many-to-Many

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE roles (
    id INT PRIMARY KEY,
    name VARCHAR(50) UNIQUE,
    permissions TEXT[]
);

-- Junction table
CREATE TABLE user_roles (
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    role_id INT REFERENCES roles(id) ON DELETE CASCADE,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by INT REFERENCES users(id),
    PRIMARY KEY (user_id, role_id)
);

Self-referencing (Tree structures)

-- Adjacency List Model
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT REFERENCES categories(id),
    level INT DEFAULT 0,
    path VARCHAR(500) -- "/electronics/computers/laptops"
);

-- Nested Set Model (cho read-heavy scenarios)
CREATE TABLE categories_nested (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    lft INT NOT NULL,
    rgt INT NOT NULL,
    level INT DEFAULT 0
);

Data Types và Storage Optimization

Chọn đúng data types

-- ❌ Lãng phí storage
CREATE TABLE users (
    id BIGINT, -- INT đủ cho hầu hết cases
    name VARCHAR(500), -- VARCHAR(100) thường đủ
    age DECIMAL(10,2), -- SMALLINT đủ
    is_active VARCHAR(10) -- BOOLEAN tốt hơn
);

-- ✅ Optimized
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age SMALLINT CHECK (age >= 0 AND age <= 150),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

JSON columns (PostgreSQL, MySQL 5.7+)

CREATE TABLE user_preferences (
    user_id INT PRIMARY KEY REFERENCES users(id),
    settings JSONB, -- PostgreSQL
    metadata JSON,   -- MySQL
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index trên JSON fields
CREATE INDEX idx_user_preferences_theme 
ON user_preferences USING GIN ((settings->>'theme'));

-- Query JSON data
SELECT * FROM user_preferences 
WHERE settings->>'theme' = 'dark'
AND settings->'notifications'->>'email' = 'true';

Constraints và Data Integrity

Check Constraints

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    discount_percent INT CHECK (discount_percent >= 0 AND discount_percent <= 100),
    status VARCHAR(20) CHECK (status IN ('draft', 'active', 'inactive', 'discontinued')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Unique Constraints

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    
    CONSTRAINT uk_users_username UNIQUE (username),
    CONSTRAINT uk_users_email UNIQUE (email),
    CONSTRAINT uk_users_phone UNIQUE (phone) -- Có thể NULL
);

Foreign Key Constraints với Actions

CREATE TABLE posts (
    id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    user_id INT NOT NULL,
    category_id INT,
    
    CONSTRAINT fk_posts_user 
        FOREIGN KEY (user_id) REFERENCES users(id) 
        ON DELETE CASCADE,
    
    CONSTRAINT fk_posts_category 
        FOREIGN KEY (category_id) REFERENCES categories(id) 
        ON DELETE SET NULL
);

Performance Best Practices

Query Optimization

-- ❌ Tránh SELECT *
SELECT * FROM posts WHERE user_id = 123;

-- ✅ Chỉ select columns cần thiết
SELECT id, title, created_at FROM posts WHERE user_id = 123;

-- ❌ Tránh functions trong WHERE
SELECT * FROM users WHERE UPPER(name) = 'JOHN';

-- ✅ Sử dụng functional index hoặc store normalized data
CREATE INDEX idx_users_name_upper ON users(UPPER(name));
-- hoặc
ALTER TABLE users ADD COLUMN name_normalized VARCHAR(100) 
GENERATED ALWAYS AS (UPPER(name)) STORED;

Pagination hiệu quả

-- ❌ OFFSET có performance kém với large datasets
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- ✅ Cursor-based pagination
SELECT * FROM posts 
WHERE created_at < '2023-12-01 10:00:00'
ORDER BY created_at DESC 
LIMIT 20;

Backup và Recovery Strategy

Logical Backups

# PostgreSQL
pg_dump -h localhost -U username -d database_name > backup.sql

# MySQL
mysqldump -u username -p database_name > backup.sql

Point-in-time Recovery

-- PostgreSQL WAL archiving
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'
wal_level = replica

Monitoring và Maintenance

Query Performance Monitoring

-- PostgreSQL - Enable query logging
log_statement = 'all'
log_min_duration_statement = 1000 -- Log queries > 1 second

-- Analyze slow queries
SELECT query, mean_time, calls, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Index Maintenance

-- PostgreSQL - Reindex
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Kết luận

Thiết kế database hiệu quả đòi hỏi:

1. Hiểu rõ business requirements và access patterns
2. Cân bằng giữa normalization và performance
3. Sử dụng indexes một cách thông minh
4. Implement proper constraints để đảm bảo data integrity
5. Monitor và optimize liên tục

Việc thiết kế database đúng cách từ đầu sẽ giúp ứng dụng của bạn scale tốt và dễ maintain trong tương lai.

---

Tags: Database, PostgreSQL, SQL, Backend, Performance, Design Patterns