Các ví dụ SQL sử dụng PostgreSQL trừ khi một phương ngữ khác được chỉ định. Các đoạn mã ngắn giả định rằng các bảng được tham chiếu đã tồn tại. Hãy xác thực DDL trong cơ sở dữ liệu mục tiêu của bạn trước khi áp dụng nó.
Tại sao thiết kế lược đồ lại quan trọng
Một lược đồ cơ sở dữ liệu được thiết kế tốt là nền tảng của mọi ứng dụng đáng tin cậy. Làm đúng thì các truy vấn sẽ nhanh, dữ liệu luôn nhất quán và các tính năng tương lai sẽ được thêm vào mà không cần di chuyển đau đớn. Làm sai thì bạn sẽ phải đối mặt với các bất thường dữ liệu, hiệu suất chậm chạp và các giải pháp tạm thời vô tận trong suốt vòng đời của dự án.
Hướng dẫn này đi qua một quy trình sáu bước để thiết kế một lược đồ từ đầu — và kết thúc bằng một ví dụ thực tế đầy đủ mà bạn có thể điều chỉnh cho các dự án của riêng mình.
Bước 1 — Thu thập yêu cầu
Trước khi mở bất kỳ công cụ nào, hãy trao đổi với các bên liên quan. Hiểu rõ ứng dụng đó thực hiện, dữ liệu nào nó quản lý và dữ liệu đó liên quan như thế nào. Đặt các câu hỏi như:
- Đâu là những "thứ" chính mà hệ thống theo dõi? (Người dùng, đơn hàng, sản phẩm …)
- Dữ liệu nào phải là duy nhất? (Email, SKU, slug …)
- Ứng dụng sẽ chạy những truy vấn nào thường xuyên nhất?
- Có các ràng buộc pháp lý nào không? (GDPR, HIPAA …)
- Khối lượng dữ liệu dự kiến trong 1–3 năm tới là bao nhiêu?
Viết các câu trả lời ra — chúng trở thành đặc tả của bạn và sẽ hướng dẫn mọi quyết định tiếp theo.
Bước 2 — Xác định các thực thể
Một thực thể là bất cứ thứ gì mà hệ thống cần lưu trữ dữ liệu về nó. Mỗi thực thể thường ánh xạ tới một bảng cơ sở dữ liệu. Đối với một nền tảng blog, danh sách thực thể ban đầu có thể trông như sau:
- Người dùng — tác giả và độc giả
- Đăng — các bài viết được xuất bản trên blog
- Danh mục — các nhóm cho bài viết (ví dụ: "React", "SQL")
- Bình luận — phản hồi của người đọc đối với bài viết
- Thẻ — các nhãn chi tiết (nhiều-nhiều với các bài viết)
Phác thảo những thứ này dưới dạng các hộp trên bảng trắng hoặc sử dụng Trình dựng ERD để thảo luận về mô hình khái niệm trước khi chọn kiểu cột.
Bước 3 — Xác định thuộc tính & kiểu dữ liệu
Đối với mỗi thực thể, hãy liệt kê các thuộc tính cần thiết và chọn kiểu dữ liệu SQL phù hợp nhất. Hãy cụ thể nhất có thể — ưu tiên VARCHAR(255) trên TEXT khi bạn biết độ dài tối đa và ưu tiên TIMESTAMPTZ trên TIMESTAMP khi múi giờ là vấn đề quan trọng.
-- Users
id SERIAL PRIMARY KEY
username VARCHAR(50) NOT NULL UNIQUE
email VARCHAR(255) NOT NULL UNIQUE
password_hash TEXT NOT NULL -- store a password hash, never plaintext
bio TEXT
avatar_url VARCHAR(500)
created_at TIMESTAMPTZ DEFAULT now()
-- Posts
id SERIAL PRIMARY KEY
author_id INT NOT NULL -- FK → users.id
category_id INT -- FK → categories.id
title VARCHAR(200) NOT NULL
slug VARCHAR(220) NOT NULL UNIQUE
body TEXT NOT NULL
status VARCHAR(20) DEFAULT 'draft' -- draft | published | archived
published_at TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT now()
updated_at TIMESTAMPTZ DEFAULT now()Bước 4 — Thiết lập mối quan hệ
Xác định cách các thực thể liên quan với nhau. Ba loại cơ bản là:
- Một-nhiều (1:N) — Một người dùng có nhiều bài viết; một bài viết thuộc về một người dùng.
- Nhiều-nhiều (M:N) — Một bài viết có thể có nhiều thẻ; một thẻ có thể thuộc về nhiều bài viết. Điều này yêu cầu một bảng trung gian.
- Một-một (1:1) — Một người dùng có một hồ sơ. Thường được gộp vào một bảng duy nhất trừ khi dữ liệu hồ sơ lớn hoặc hiếm khi được truy cập.
-- Junction table for the posts ↔ tags many-to-many relationship
CREATE TABLE post_tags (
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
tag_id INT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);Bước 5 — Áp dụng các ràng buộc & chỉ mục
Các ràng buộc bảo vệ chất lượng dữ liệu. Tối thiểu bạn nên xem xét:
NOT NULLtrên mọi cột bắt buộc phải có giá trị.UNIQUEtrên các định danh tự nhiên (email, slug, tên người dùng).CHECKcác ràng buộc cho các quy tắc miền (ví dụ: trạng thái phải là một trong ba giá trị).DEFAULTcác giá trị cho các cột dấu thời gian và trạng thái.- Các khóa ngoại với
ON DELETEcác hành động.
Sau đó thêm các chỉ mục trên các cột bạn thường xuyên lọc, sắp xếp hoặc kết nối. Các cột khóa ngoại là những ứng cử viên hàng đầu.
-- Index foreign-key columns for faster joins
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_category ON posts (category_id);
CREATE INDEX idx_comments_post ON comments (post_id);
-- Partial index: only published posts (PostgreSQL)
CREATE INDEX idx_posts_published
ON posts (published_at DESC)
WHERE status = 'published';Bước 6 — Chuẩn hóa (Sau đó phi chuẩn hóa khi cần)
Chuẩn hóa loại bỏ dữ liệu dư thừa bằng cách tổ chức các cột và bảng theo một tập hợp các "dạng chuẩn". Hầu hết các lược đồ nên đạt đến Dạng chuẩn thứ ba (3NF):
- 1NF — mỗi cột chứa các giá trị nguyên tử; không có các nhóm lặp lại.
- 2NF — không có thuộc tính không khóa nào phụ thuộc vào chỉ một phần của khóa ứng viên.
- 3NF — mỗi phụ thuộc không tầm thường có một yếu tố quyết định siêu khóa hoặc một thuộc tính phụ thuộc thuộc về một khóa ứng viên.
Sau khi chuẩn hóa, bạn có thể chọn lọc phi chuẩn hóa vì hiệu suất — ví dụ, lưu trữ một comment_count trên posts bảng để tránh một COUNT(*) trên mỗi lần tải trang. Phi chuẩn hóa một cách có chủ đích và lập tài liệu cho nó.
Lược đồ nền tảng blog hoàn chỉnh
-- Full SQL for the blog platform schema
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
bio TEXT,
avatar_url VARCHAR(500),
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
slug VARCHAR(110) NOT NULL UNIQUE
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
author_id INT NOT NULL REFERENCES users (id),
category_id INT REFERENCES categories (id) ON DELETE SET NULL,
title VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL UNIQUE,
body TEXT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft','published','archived')),
published_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE post_tags (
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
tag_id INT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
user_id INT NOT NULL REFERENCES users (id),
body TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
-- Indexes
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_category ON posts (category_id);
CREATE INDEX idx_comments_post ON comments (post_id);
CREATE INDEX idx_comments_user ON comments (user_id);Dán lược đồ hoàn chỉnh vào Trình chỉnh sửa SQL-to-ERD để kiểm tra các mối quan hệ của nó. Sử dụng Trình dựng ERD cho một bản vẽ khái niệm và một bản chuyển đổi SQL đã được xem xét.



