SQL 5 — Thiết kế CSDL & Chuẩn hoá

Vì sao thiết kế CSDL lại quan trọng

Một truy vấn chậm thường có thể vá bằng index. Nhưng một lược đồ (schema) sai thì khó sửa hơn nhiều: dữ liệu đã dư thừa, đã mâu thuẫn, và mọi ứng dụng phía trên đều phụ thuộc vào cấu trúc bảng đó. Vì vậy, thiết kế đúng từ đầu là khoản đầu tư rẻ nhất bạn có thể làm.

Bài này đi từ những viên gạch nền — khoá và ràng buộc — đến cách mô hình hoá quan hệ giữa các thực thể, rồi tới chuẩn hoá (1NF, 2NF, 3NF) bằng ví dụ "trước/sau" cụ thể. Cuối cùng là khi nào nên cố tình phi chuẩn hoá để đọc nhanh hơn.

Bối cảnh xuyên suốt: một hệ thống ngân hàng đơn giản gồm khách hàng — tài khoản — giao dịch.

Khoá: định danh và liên kết dữ liệu

Khoá chính (Primary Key)

Khoá chính là cột (hoặc tập cột) định danh duy nhất mỗi dòng trong bảng. Quy tắc:

• Không bao giờ NULL.
• Không bao giờ trùng.
• Lý tưởng là không bao giờ thay đổi giá trị.

-- (minh hoạ, không chạy trong sandbox)
CREATE TABLE customer (
    customer_id   BIGINT       PRIMARY KEY,
    full_name     VARCHAR(120) NOT NULL,
    national_id   VARCHAR(20)  NOT NULL
);

Khoá tự nhiên vs khoá thay thế

Đây là một quyết định thiết kế cốt lõi.

• Khoá tự nhiên (natural key): dùng dữ liệu nghiệp vụ vốn có làm khoá, ví dụ national_id (số CMND/CCCD), email, hay mã ISO quốc gia.
• Khoá thay thế (surrogate key): một giá trị nhân tạo không mang ý nghĩa nghiệp vụ, thường là số tự tăng (BIGINT IDENTITY/SERIAL) hoặc UUID.

Vì sao surrogate key thường được ưa chuộng trong thực tế:

1. Ổn định: số CCCD có thể bị cấp lại, email có thể đổi — nhưng customer_id = 1024 thì không bao giờ đổi.
2. Gọn và đồng nhất: khoá ngoại trỏ tới một BIGINT nhanh và nhẹ hơn trỏ tới chuỗi dài.
3. Tránh lộ thông tin: dùng số nội bộ thay vì để national_id rải khắp các bảng.

Nhược điểm: khoá thay thế không tự ngăn trùng lặp nghiệp vụ. Vì vậy ngay cả khi đã có surrogate key, ta vẫn nên đặt UNIQUE lên khoá tự nhiên (xem phần ràng buộc).

Khoá tổng hợp (Composite Key)

Khi không một cột đơn lẻ nào định danh được dòng, ta ghép nhiều cột. Thường gặp ở bảng trung gian của quan hệ n-n.

-- (minh hoạ, không chạy trong sandbox)
CREATE TABLE account_holder (
    account_id    BIGINT NOT NULL,
    customer_id   BIGINT NOT NULL,
    role          VARCHAR(20) NOT NULL,   -- 'owner', 'authorized'...
    PRIMARY KEY (account_id, customer_id)
);

Cặp (account_id, customer_id) là duy nhất: một khách hàng chỉ xuất hiện một lần cho mỗi tài khoản.

Khoá ngoại (Foreign Key)

Khoá ngoại là cột tham chiếu tới khoá chính của bảng khác, tạo nên liên kết và toàn vẹn tham chiếu (referential integrity): không thể có giao dịch trỏ tới một tài khoản không tồn tại.

-- (minh hoạ, không chạy trong sandbox)
CREATE TABLE account (
    account_id    BIGINT PRIMARY KEY,
    customer_id   BIGINT NOT NULL,
    balance       NUMERIC(18,2) NOT NULL DEFAULT 0,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

Bạn có thể điều khiển hành vi khi dòng cha bị xoá/sửa:

• ON DELETE CASCADE — xoá luôn các dòng con.
• ON DELETE RESTRICT (mặc định ở nhiều hệ) — chặn xoá nếu còn con.
• ON DELETE SET NULL — đặt khoá ngoại về NULL.

Hãy chọn có chủ đích. Với dữ liệu tài chính, RESTRICT thường an toàn hơn CASCADE vì bạn không muốn xoá khách hàng lại làm bay sạch lịch sử giao dịch.

Ràng buộc (Constraints): để CSDL tự bảo vệ dữ liệu

Ràng buộc giúp dữ liệu luôn hợp lệ bất kể ứng dụng nào ghi vào. Đừng phó mặc toàn bộ việc kiểm tra cho tầng ứng dụng — CSDL là tuyến phòng thủ cuối cùng.

Ví dụ tổng hợp:

-- (minh hoạ, không chạy trong sandbox)
CREATE TABLE account (
    account_id    BIGINT PRIMARY KEY,
    customer_id   BIGINT NOT NULL,
    account_no    VARCHAR(20) NOT NULL UNIQUE,           -- khoá tự nhiên
    currency      CHAR(3) NOT NULL DEFAULT 'VND',
    balance       NUMERIC(18,2) NOT NULL DEFAULT 0
                  CHECK (balance >= 0),                  -- không cho âm
    status        VARCHAR(10) NOT NULL DEFAULT 'active'
                  CHECK (status IN ('active','frozen','closed')),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

Lưu ý vài điều tinh tế:

• UNIQUE và NULL: trong chuẩn SQL, nhiều NULL không bị coi là trùng nhau, nên một cột UNIQUE vẫn cho phép nhiều dòng NULL.
• CHECK chỉ nên dùng cho điều kiện trong phạm vi một dòng. Quy tắc liên dòng (ví dụ "tổng dư nợ ≤ hạn mức") thường cần trigger hoặc xử lý ở tầng nghiệp vụ.
• Đặt status IN (...) bằng CHECK là một dạng "enum nghèo" — đơn giản và đủ dùng; với danh mục hay thay đổi thì nên tách thành bảng tham chiếu (lookup table).

Quan hệ giữa các thực thể

Một-một (1:1)

Mỗi dòng A ứng với tối đa một dòng B và ngược lại. Thường dùng để tách cột ít dùng/nhạy cảm ra bảng riêng, ví dụ tách thông tin định danh KYC nhạy cảm khỏi bảng khách hàng chính.

-- (minh hoạ, không chạy trong sandbox)
CREATE TABLE customer_kyc (
    customer_id  BIGINT PRIMARY KEY,        -- vừa PK vừa FK
    id_document  VARCHAR(40) NOT NULL,
    verified_at  TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

Mẹo: cho khoá chính của bảng phụ trùng luôn với khoá ngoại — đó là cách áp 1:1 chặt chẽ.

Một-nhiều (1:n)

Phổ biến nhất. Một khách hàng có nhiều tài khoản; một tài khoản có nhiều giao dịch. Cài đặt bằng cách đặt khoá ngoại ở bên "nhiều":

-- (minh hoạ, không chạy trong sandbox)
CREATE TABLE transaction (
    txn_id      BIGINT PRIMARY KEY,
    account_id  BIGINT NOT NULL,            -- bên "nhiều" giữ FK
    amount      NUMERIC(18,2) NOT NULL,
    txn_type    VARCHAR(10) NOT NULL CHECK (txn_type IN ('debit','credit')),
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES account(account_id)
);

Nhiều-nhiều (n:n)

Một khách hàng có thể đồng sở hữu nhiều tài khoản, và một tài khoản có thể có nhiều đồng sở hữu. SQL không biểu diễn trực tiếp n:n; ta tách thành hai quan hệ 1:n qua một bảng trung gian (junction/bridge table) — chính là bảng account_holder ở trên, với khoá tổng hợp (account_id, customer_id).

Bảng trung gian còn là nơi tự nhiên để lưu thuộc tính của chính mối quan hệ, ví dụ cột role ('owner' / 'authorized').

Sơ đồ ERD

Đọc sơ đồ: ||--o{ nghĩa là "một và chỉ một" phía bên trái ứng với "không hoặc nhiều" phía bên phải (1:n). CUSTOMER và ACCOUNT nối với nhau gián tiếp qua ACCOUNT_HOLDER, thể hiện quan hệ n:n.

Chuẩn hoá: loại bỏ dư thừa một cách có hệ thống

Chuẩn hoá là quá trình tổ chức lại các bảng để giảm dư thừa và tránh các dị thường (anomaly) khi thêm/sửa/xoá. Ta sẽ đi qua ba bậc đầu — đủ dùng cho hầu hết hệ thống nghiệp vụ.

Hãy bắt đầu từ một bảng "tất cả trong một" tệ hại, ghi lại từng giao dịch kèm mọi thông tin liên quan:

Trước chuẩn hoá (dư thừa)

Bảng này mắc đủ loại lỗi:

• Dị thường khi sửa: nếu Lan đổi tên, phải sửa nhiều dòng; bỏ sót một dòng là dữ liệu mâu thuẫn.
• Dị thường khi xoá: xoá giao dịch cuối của một khách có thể xoá luôn thông tin khách hàng.
• Dị thường khi thêm: không thể lưu một khách hàng mới chưa có giao dịch nào.

1NF — Mỗi ô một giá trị nguyên tử

Quy tắc 1NF: mỗi ô chứa đúng một giá trị (không danh sách, không lặp nhóm), và mỗi dòng là duy nhất.

Vấn đề ở bảng trên: cust_phone chứa "0901, 0902" và products chứa "Phí thẻ; Lãi tiết kiệm" — đây là các ô đa trị, vi phạm 1NF.

Sau 1NF

Tách giá trị đa trị thành nhiều dòng / bảng riêng. Ví dụ, số điện thoại tách ra bảng riêng:

customer_phone(customer_id, phone)
(101, '0901')
(101, '0902')
(102, '0987')

Và mỗi giao dịch chỉ gắn với một sản phẩm; nếu một giao dịch thật sự có nhiều dòng sản phẩm thì đó là quan hệ 1:n cần bảng transaction_line riêng. Sau bước này, không còn ô nào chứa danh sách.

2NF — Loại phụ thuộc bộ phận vào khoá

Quy tắc 2NF: đã ở 1NF, và mọi cột không khoá phải phụ thuộc vào TOÀN BỘ khoá chính, không phụ thuộc vào chỉ một phần.

2NF chỉ có ý nghĩa khi bảng có khoá tổng hợp. Xét một bảng dòng giao dịch với khoá (txn_id, product_id):

Trước 2NF

Phân tích phụ thuộc:

• product_name phụ thuộc chỉ vào product_id (một phần của khoá) → phụ thuộc bộ phận, vi phạm 2NF. Hệ quả: product_name của P-10 bị lặp ở mọi giao dịch dùng nó.
• txn_date phụ thuộc chỉ vào txn_id → cũng phụ thuộc bộ phận.

Sau 2NF

Tách các phần phụ thuộc bộ phận ra bảng riêng:

product(product_id PK, product_name)
transaction(txn_id PK, txn_date, ...)
transaction_line(txn_id, product_id, line_amount)   -- PK (txn_id, product_id)

Giờ tên sản phẩm chỉ lưu một chỗ trong product; ngày giao dịch chỉ lưu một chỗ trong transaction.

3NF — Loại phụ thuộc bắc cầu

Quy tắc 3NF: đã ở 2NF, và không cột không khoá nào phụ thuộc vào một cột không khoá khác (loại bỏ phụ thuộc bắc cầu — transitive dependency).

Xét bảng tài khoản nhúng cả thông tin chi nhánh:

Trước 3NF

Ở đây account_id → branch_id → branch_name, branch_city. Tức branch_name phụ thuộc vào branch_id (cột không khoá), rồi mới gián tiếp gắn với khoá. Đây là phụ thuộc bắc cầu: tên và thành phố chi nhánh bị lặp ở mọi tài khoản cùng chi nhánh, và đổi tên chi nhánh phải sửa nhiều dòng.

Sau 3NF

-- (minh hoạ, không chạy trong sandbox)
CREATE TABLE branch (
    branch_id    VARCHAR(10) PRIMARY KEY,
    branch_name  VARCHAR(60) NOT NULL,
    branch_city  VARCHAR(40) NOT NULL
);

CREATE TABLE account (
    account_id  BIGINT PRIMARY KEY,
    branch_id   VARCHAR(10) NOT NULL,
    -- ... các cột khác
    FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
);

Bây giờ thuộc tính của chi nhánh chỉ tồn tại đúng một lần trong bảng branch.

Câu thần chú ghi nhớ: mỗi cột không khoá phải phụ thuộc vào khoá (1NF gom dữ liệu nguyên tử), vào toàn bộ khoá (2NF), và không gì ngoài khoá (3NF) — "the key, the whole key, and nothing but the key".

Khi nào nên CỐ TÌNH phi chuẩn hoá

Chuẩn hoá tối ưu cho toàn vẹn dữ liệu và ghi (write): mỗi sự thật lưu đúng một nơi. Nhưng nó đánh đổi bằng việc đọc phải JOIN nhiều bảng. Trong một số tình huống, ta cố tình đi ngược lại — phi chuẩn hoá (denormalization) — để đọc nhanh hơn.

Các trường hợp hợp lý:

• Báo cáo & BI / data warehouse: mô hình sao (star schema) cố tình gom thuộc tính vào bảng dimension rộng để tránh JOIN sâu khi tổng hợp. Đọc nhiều, ghi theo mẻ — đánh đổi này đáng giá.
• Cột tổng hợp được tính sẵn (precomputed): lưu account.balance thay vì luôn SUM toàn bộ giao dịch; hay lưu order.total_amount thay vì cộng lại các dòng hàng mỗi lần đọc.
• Sao chép dữ liệu ít đổi: nhúng currency hay tên danh mục để tránh JOIN bảng tra cứu trong truy vấn nóng.

Cái giá phải trả luôn là nguy cơ mâu thuẫn: dữ liệu trùng lặp phải được giữ đồng bộ. Khi phi chuẩn hoá, hãy:

1. Có cơ chế cập nhật rõ ràng (trigger, job nền, hoặc logic ứng dụng) để các bản sao luôn khớp.
2. Ghi lại lý do trong tài liệu để người sau không tưởng nhầm là lỗi thiết kế.
3. Đo đạc trước: chỉ phi chuẩn hoá khi có bằng chứng JOIN thật sự là nút cổ chai.

Nguyên tắc thực dụng: chuẩn hoá tới 3NF trước, rồi mới phi chuẩn hoá có chủ đích ở những điểm nóng đã đo được — đừng làm ngược lại.

Tóm tắt

• Khoá chính định danh dòng; ưu tiên khoá thay thế (surrogate) cho sự ổn định, nhưng vẫn đặt UNIQUE lên khoá tự nhiên.
• Khoá tổng hợp thường xuất hiện ở bảng trung gian của quan hệ n:n; khoá ngoại đảm bảo toàn vẹn tham chiếu.
• Ràng buộc (NOT NULL, UNIQUE, CHECK, DEFAULT, FK) để CSDL tự bảo vệ dữ liệu, không phó mặc tầng ứng dụng.
• Quan hệ: 1:1 (tách bảng), 1:n (FK ở bên "nhiều"), n:n (bảng trung gian).
• Chuẩn hoá: 1NF (giá trị nguyên tử), 2NF (bỏ phụ thuộc bộ phận), 3NF (bỏ phụ thuộc bắc cầu) — diệt dị thường thêm/sửa/xoá.
• Phi chuẩn hoá là công cụ có chủ đích cho đọc nhanh/BI, chấp nhận giữ đồng bộ — chuẩn hoá trước, phi chuẩn hoá sau.

Tự kiểm tra

1. Khác biệt giữa khoá tự nhiên và khoá thay thế là gì, và vì sao thực tế thường dùng khoá thay thế?
2. Quan hệ n:n được cài đặt trong SQL như thế nào, và khoá chính của bảng trung gian thường gồm những cột nào?
3. Cho bảng (txn_id, product_id, product_name) với khoá (txn_id, product_id) — bảng này vi phạm bậc chuẩn hoá nào và sửa ra sao?
4. "Phụ thuộc bắc cầu" trong 3NF nghĩa là gì? Cho một ví dụ.
5. Nêu hai tình huống mà phi chuẩn hoá là lựa chọn hợp lý, kèm cái giá phải trả.
6. Vì sao nên đặt ràng buộc CHECK/FK ở CSDL thay vì chỉ kiểm tra ở tầng ứng dụng?

Use case thực tế

Đội core banking NCB nhận yêu cầu hỗ trợ tài khoản đồng sở hữu (joint account) cho khách hàng doanh nghiệp nhỏ và hộ gia đình. Thiết kế cũ chỉ có một cột owner_id trên bảng account, nghĩa là mỗi tài khoản chỉ gắn được một chủ — không thể mô tả hai vợ chồng cùng đứng tên, hay một kế toán được uỷ quyền giao dịch.

Cách xử lý theo đúng bài này (số liệu minh hoạ):

1. Nhận diện quan hệ n:n: một khách hàng có nhiều tài khoản, một tài khoản có nhiều người liên quan. Không thể nhồi danh sách owner_id vào một ô (vi phạm 1NF).
2. Tạo bảng trung gian account_holder(account_id, customer_id, role) với khoá tổng hợp (account_id, customer_id), cột role phân biệt 'owner' / 'authorized'.
3. Ràng buộc bảo vệ dữ liệu: FK trỏ về account và customer với ON DELETE RESTRICT để đóng tài khoản không làm mất lịch sử người đứng tên; CHECK (role IN ('owner','authorized')).
4. Kết quả (ước lượng): khoảng 2,3 triệu tài khoản hiện có được migrate bằng cách đổ mỗi owner_id thành một dòng account_holder role 'owner'; sau đó nghiệp vụ thêm đồng sở hữu chỉ là INSERT thêm dòng, không cần đổi schema. Báo cáo "tài khoản có từ 2 người đứng tên trở lên" trở thành một câu GROUP BY account_id HAVING COUNT(*) > 1 thay vì phải parse chuỗi.

Ghi nhớ

• Lược đồ sai đắt hơn truy vấn chậm nhiều: index vá được, schema sai thì kéo theo cả ứng dụng — thiết kế đúng từ đầu.
• Ưu tiên khoá thay thế (surrogate) cho sự ổn định, nhưng luôn đặt UNIQUE lên khoá tự nhiên để chặn trùng nghiệp vụ.
• Quan hệ n:n không biểu diễn trực tiếp trong SQL — luôn tách qua bảng trung gian với khoá tổng hợp; bảng này cũng là nơi lưu thuộc tính của chính mối quan hệ.
• Khoá ngoại giữ toàn vẹn tham chiếu; chọn hành vi ON DELETE có chủ đích — với dữ liệu tài chính RESTRICT thường an toàn hơn CASCADE.
• Đặt ràng buộc ở CSDL (NOT NULL, UNIQUE, CHECK, FK), đừng phó mặc tầng ứng dụng — CSDL là tuyến phòng thủ cuối.
• Câu thần chú 3NF: mỗi cột không khoá phụ thuộc vào khoá, toàn bộ khoá, và không gì ngoài khoá.
• Chuẩn hoá tới 3NF trước, chỉ phi chuẩn hoá có chủ đích ở điểm nóng đã đo được và luôn có cơ chế giữ đồng bộ.

Nguồn tham khảo

• PostgreSQL Documentation — Chapter "Data Definition" (Constraints)
• PostgreSQL Documentation — "CREATE TABLE" (Primary/Foreign Key, UNIQUE, CHECK)
• E. F. Codd, "A Relational Model of Data for Large Shared Data Banks", Communications of the ACM, 1970 (nền tảng mô hình quan hệ và chuẩn hoá)
• C. J. Date, "An Introduction to Database Systems" (chương về chuẩn hoá 1NF/2NF/3NF/BCNF)
• ISO/IEC 9075 — "Information technology — Database languages — SQL" (chuẩn ngôn ngữ SQL)

Đọc tiếp

SQL 6 — Index & Tối ưu truy vấn