Thiết kế database cho đơn hàng: khóa, quan hệ và chuẩn hóa

Thiết kế database cho đơn hàng: vì sao phải chia bảng, khóa chính và khóa ngoại làm gì, chuẩn hóa là gì, kèm lỗi sửa, thêm, xóa của một bảng duy nhất.

Thiết kế bảng tốt nghĩa là chia dữ liệu thành các bảng nhỏ, mỗi bảng nói về một thứ, nối nhau bằng khóa chính và khóa ngoại, để mỗi thông tin chỉ nằm một chỗ. Việc đó gọi là chuẩn hóa, và nó ngăn các lỗi sửa, thêm, xóa mà một bảng duy nhất gây ra.

Ở bài trước, bạn đã đọc, ghi và nối bảng bằng SQL trên ba bảng của quán Mộc (quán cà phê tình huống minh họa của loạt bài). Cuối bài mình còn nợ bạn câu hỏi: vì sao lại đúng ba bảng đó, vì sao order_lines có khóa chính gồm hai cột, và vì sao tên khách nằm ngay trong orders? Bài này trả lời từng câu, bắt đầu từ việc xem cách làm ngược lại hỏng ở đâu.

Nếu nhét tất cả vào một bảng thì hỏng ở đâu?

Cách tự nhiên nhất với người mới là làm một bảng giống bảng tính: mỗi dòng là một món trong một đơn, ghi luôn tên khách và giá món ở trên dòng ấy. Mình dựng thử bảng don_hang_phang với đúng dữ liệu của các đơn ở bài trước (trừ đơn số 6 chưa có món, vì bảng này không biểu diễn được một đơn không có dòng nào):

bang-phang.sql
CREATE TABLE don_hang_phang (
    ma_don   INT  NOT NULL,
    khach    TEXT NOT NULL,
    mon      TEXT NOT NULL,
    gia_mon  BIGINT NOT NULL,
    so_luong INT  NOT NULL
);

INSERT INTO don_hang_phang (ma_don, khach, mon, gia_mon, so_luong) VALUES
    (1, 'Lan',  'Cà phê sữa',  35000, 2),
    (1, 'Lan',  'Bánh mì que', 15000, 1),
    (2, 'Hùng', 'Bạc xỉu',     39000, 1),
    (3, 'Mai',  'Trà đào',     45000, 3),
    (3, 'Mai',  'Cà phê sữa',  35000, 1),
    (4, 'Nam',  'Bạc xỉu',     39000, 2),
    (5, 'Lan',  'Cà phê sữa',  35000, 1),
    (5, 'Lan',  'Cà phê đen',  29000, 2);

Nhìn thì ổn: đọc một dòng là biết ai mua gì, giá bao nhiêu. Vấn đề chỉ lộ ra khi bạn sửa, thêm và xóa. Bạn có thể chạy các câu tiếp theo bằng psql, hoặc dùng script PGlite trong repo demo, với lệnh node chay-sql.mjs bang-phang.sql bai-1-loi-bang-phang.sql sau khi npm install (cách này cần Node.js 22 trở lên). Dưới đây mình chạy từng lỗi một.

Lỗi thứ nhất là sửa sót. Quán tăng giá cà phê sữa lên 37.000, nhưng người sửa chỉ chỉnh dòng của đơn hôm nay (đơn 5) và bỏ quên đơn cũ. Cà phê sữa xuất hiện ở ba dòng, nên mình đếm xem hiện có những mức giá nào:

bai-1-loi-bang-phang.sql (phần 1)
UPDATE don_hang_phang SET gia_mon = 37000 WHERE mon = 'Cà phê sữa' AND ma_don = 5;

SELECT mon, gia_mon, count(*) AS so_dong FROM don_hang_phang
WHERE mon = 'Cà phê sữa' GROUP BY mon, gia_mon ORDER BY gia_mon;
Kết quả
UPDATE 1
    mon     | gia_mon | so_dong
------------+---------+---------
 Cà phê sữa |   35000 |       2
 Cà phê sữa |   37000 |       1
(2 rows)

Cùng một món mà có hai giá. Bảng không biết giá nào là đúng, và không có gì ngăn việc này xảy ra, vì giá được chép vào từng dòng. Càng nhiều đơn, càng nhiều chỗ để sót.

Lỗi thứ hai là không thêm được dữ liệu. Quán muốn đưa món mới "Sinh tố bơ" vào thực đơn khi chưa ai gọi:

bai-1-loi-bang-phang.sql (phần 2)
INSERT INTO don_hang_phang (mon, gia_mon) VALUES ('Sinh tố bơ', 52000);
Kết quả
ERROR: null value in column "ma_don" of relation "don_hang_phang" violates not-null constraint

Một món chỉ tồn tại khi có ít nhất một đơn mua nó. Bạn buộc phải bịa ra một đơn giả để cất tên món, và đơn giả ấy sẽ lọt vào báo cáo doanh thu.

Lỗi thứ ba là xóa xong mất luôn thông tin khác. Đơn 3 của Mai là đơn duy nhất có Trà đào. Mình xóa đơn đó và xem các món còn lại:

bai-1-loi-bang-phang.sql (phần 3)
SELECT DISTINCT mon FROM don_hang_phang ORDER BY mon;
DELETE FROM don_hang_phang WHERE ma_don = 3;
SELECT DISTINCT mon FROM don_hang_phang ORDER BY mon;
Kết quả
     mon     
-------------
 Bánh mì que
 Bạc xỉu
 Cà phê sữa
 Cà phê đen
 Trà đào
(5 rows)
DELETE 2
     mon     
-------------
 Bánh mì que
 Bạc xỉu
 Cà phê sữa
 Cà phê đen
(4 rows)

Xóa một đơn nhưng Trà đào và giá 45.000 của nó biến mất khỏi dữ liệu. Giá của món thuộc về thực đơn, không thuộc về đơn nào, vậy mà bảng này cột chúng vào nhau.

Ba lỗi này có tên trong sách giáo khoa (các dị thường khi cập nhật, thêm và xóa), nhưng bạn không cần nhớ tên. Chỉ cần nhớ nguyên nhân chung: cùng một sự thật bị lưu ở nhiều nơi, hoặc hai sự thật khác nhau bị cột vào một dòng.

Chuẩn hóa là gì, và cắt bảng thế nào cho đúng?

Chuẩn hóa là quá trình tách bảng cho tới khi mỗi sự thật chỉ nằm ở một chỗ. Sách giáo khoa chia thành các "dạng chuẩn" 1NF, 2NF, 3NF. Mình thấy dễ nhớ hơn nếu bạn tự hỏi ba câu khi nhìn một bảng.

Câu thứ nhất: mỗi ô chỉ chứa một giá trị chứ? Nếu bạn định làm cột mon1, mon2, mon3 cho một đơn hoặc nhét "cà phê sữa, bánh mì" vào một ô, thì đó là vi phạm. Một đơn có nhiều món thì mỗi món là một dòng riêng.

Câu thứ hai: mỗi cột có phụ thuộc vào toàn bộ khóa của dòng không? Trong don_hang_phang, gia_mon chỉ phụ thuộc vào món, không phụ thuộc vào đơn, nên nó phải ra khỏi bảng đơn và về bảng món. Câu thứ ba: có cột nào phụ thuộc vào một cột không phải khóa không? Ví dụ nếu orders có thêm cột so_dien_thoai_khach bên cạnh customer_name, thì số điện thoại thật ra thuộc về khách chứ không thuộc về đơn, và nó nên sang bảng khách.

Áp ba câu đó vào bảng phẳng, mình nhận được đúng ba bảng của bài trước. Mỗi bảng giữ một loại sự thật:

Quan hệ giữa ba bảng
  menu_items                 orders
  ----------                 ------
  id    (khóa chính)         id            (khóa chính)
  name  (duy nhất)           customer_name
  price                      status
      ^                      created_at
      |                          ^
      |  menu_item_id            |  order_id
      |                          |
      +------- order_lines ------+
               ----------
               order_id, menu_item_id   (khóa chính gồm hai cột)
               quantity
               unit_price

menu_items giữ thực đơn: giá và tên món nằm đúng một chỗ. orders giữ thông tin chung của một đơn. order_lines là bảng nối: mỗi dòng nói "đơn này có món này, số lượng bao nhiêu". Mối quan hệ giữa orders và menu_items là nhiều-nhiều (một đơn có nhiều món, một món nằm trong nhiều đơn), và cách chuẩn để biểu diễn quan hệ nhiều-nhiều là một bảng nối ở giữa. Bạn sẽ gặp lại mẫu thiết kế này ở mọi dự án.

Đây là file schema.sql đầy đủ của tập này. So với bài trước nó có bốn chỗ mới mà mình sẽ giải thích dần: UNIQUE ở tên món, cột unit_price, ON DELETE CASCADE, và dòng CREATE INDEX cuối cùng.

schema.sql
CREATE TABLE menu_items (
    id    BIGSERIAL PRIMARY KEY,
    name  TEXT   NOT NULL UNIQUE,
    price BIGINT NOT NULL CHECK (price > 0)
);

CREATE TABLE orders (
    id            BIGSERIAL PRIMARY KEY,
    customer_name TEXT      NOT NULL,
    status        TEXT      NOT NULL DEFAULT 'NEW'
                  CHECK (status IN ('NEW', 'PAID', 'DONE', 'CANCELLED')),
    created_at    TIMESTAMP NOT NULL
);

CREATE TABLE order_lines (
    order_id     BIGINT NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    menu_item_id BIGINT NOT NULL REFERENCES menu_items (id),
    quantity     INT    NOT NULL CHECK (quantity > 0),
    unit_price   BIGINT NOT NULL CHECK (unit_price > 0),
    PRIMARY KEY (order_id, menu_item_id)
);

CREATE INDEX idx_order_lines_menu_item_id ON order_lines (menu_item_id);

Dữ liệu mẫu giống bài trước (5 món, 6 đơn, 8 dòng món), chỉ thêm unit_price bằng giá món lúc đặt:

du-lieu-mau.sql
INSERT INTO menu_items (name, price) VALUES
    ('Cà phê sữa', 35000),
    ('Bạc xỉu', 39000),
    ('Trà đào', 45000),
    ('Bánh mì que', 15000),
    ('Cà phê đen', 29000);

INSERT INTO orders (customer_name, status, created_at) VALUES
    ('Lan',  'DONE',      '2026-11-01 08:10:00'),
    ('Hùng', 'PAID',      '2026-11-01 09:30:00'),
    ('Mai',  'NEW',       '2026-11-02 07:45:00'),
    ('Nam',  'CANCELLED', '2026-11-02 10:05:00'),
    ('Lan',  'NEW',       '2026-11-03 08:20:00'),
    ('Phúc', 'NEW',       '2026-11-03 09:00:00');

INSERT INTO order_lines (order_id, menu_item_id, quantity, unit_price) VALUES
    (1, 1, 2, 35000),
    (1, 4, 1, 15000),
    (2, 2, 1, 39000),
    (3, 3, 3, 45000),
    (3, 1, 1, 35000),
    (4, 2, 2, 39000),
    (5, 1, 1, 35000),
    (5, 5, 2, 29000);

Khóa chính và khóa ngoại giữ cho dữ liệu đúng như thế nào?

Khóa chính (PRIMARY KEY) là cột, hoặc nhóm cột, xác định duy nhất một dòng: không trùng và không rỗng. Khóa ngoại (REFERENCES) là cột của bảng này trỏ tới khóa chính của bảng khác, và database từ chối mọi giá trị trỏ vào chỗ không có. Mình chạy sáu tình huống trong bai-3-khoa-va-rang-buoc.sql trên dữ liệu mẫu ở trên. Dưới đây mình bỏ các dòng sql> lặp lại câu lệnh, để bạn đỡ đọc hai lần.

bai-3-khoa-va-rang-buoc.sql
-- 1. Khóa chính kép: không thể ghi cùng một món hai lần trong một đơn
INSERT INTO order_lines (order_id, menu_item_id, quantity, unit_price) VALUES (1, 1, 5, 35000);

-- 2. Khóa ngoại: dòng món phải trỏ tới một đơn có thật
INSERT INTO order_lines (order_id, menu_item_id, quantity, unit_price) VALUES (99, 1, 1, 35000);

-- 3. UNIQUE: không thêm hai món trùng tên
INSERT INTO menu_items (name, price) VALUES ('Trà đào', 47000);

-- 4. Không xóa được món đang có trong đơn (khóa ngoại mặc định là chặn)
DELETE FROM menu_items WHERE name = 'Bạc xỉu';

-- 5. Xóa đơn thì xóa luôn các dòng món của đơn (ON DELETE CASCADE)
SELECT count(*) AS so_dong_mon_cua_don_1 FROM order_lines WHERE order_id = 1;
DELETE FROM orders WHERE id = 1;
SELECT count(*) AS so_dong_mon_cua_don_1 FROM order_lines WHERE order_id = 1;

-- 6. Các chỉ mục của order_lines
SELECT indexname FROM pg_indexes WHERE tablename = 'order_lines' ORDER BY indexname;
Kết quả (node chay-sql.mjs schema.sql du-lieu-mau.sql bai-3-khoa-va-rang-buoc.sql)
ERROR: duplicate key value violates unique constraint "order_lines_pkey"

ERROR: insert or update on table "order_lines" violates foreign key constraint "order_lines_order_id_fkey"

ERROR: duplicate key value violates unique constraint "menu_items_name_key"

ERROR: update or delete on table "menu_items" violates foreign key constraint "order_lines_menu_item_id_fkey" on table "order_lines"

 so_dong_mon_cua_don_1
-----------------------
                     2
(1 row)

DELETE 1
 so_dong_mon_cua_don_1
-----------------------
                     0
(1 row)

          indexname          
------------------------------
 idx_order_lines_menu_item_id
 order_lines_pkey
(2 rows)

Bốn lỗi đầu đều là database từ chối, và đó chính là mục đích.

Lỗi 1 xảy ra vì khóa chính của order_lines là cặp (order_id, menu_item_id): một món chỉ được xuất hiện một lần trong một đơn. Muốn mua năm ly thì tăng quantity, không thêm dòng mới. Nếu thiếu ràng buộc này, hai dòng cùng món sẽ làm số lượng bị đếm sai tùy người viết báo cáo cộng một dòng hay cả hai.

Lỗi 2 cho thấy khóa ngoại chặn "dòng mồ côi", tức dòng món trỏ tới đơn 99 không tồn tại. Lỗi 3 là UNIQUE chặn hai món cùng tên "Trà đào" với hai mức giá. Lỗi 4 là khóa ngoại mặc định chặn việc xóa món đang được dùng, vì nếu xóa thì các dòng món cũ sẽ trỏ vào khoảng trống.

Mục 5 là hành vi ngược lại và là lựa chọn có chủ ý: dòng món chỉ có nghĩa khi đơn của nó còn, nên mình khai báo ON DELETE CASCADE cho order_id. Xóa đơn 1 thì hai dòng món của nó cũng bị xóa theo (số dòng từ 2 về 0), và bạn không phải dọn tay. Với menu_item_id mình không dùng cascade: xóa một món mà tự động xóa luôn mọi dòng món đã bán thì lịch sử bán hàng biến mất. Quy tắc mình dùng: cascade cho dữ liệu "thuộc về" cha của nó, chặn cho dữ liệu tham chiếu mà bạn không muốn mất.

Mục 6 cho một chi tiết dễ quên: PostgreSQL tự tạo chỉ mục cho khóa chính, nhưng không tự tạo cho khóa ngoại. Khóa chính kép (order_id, menu_item_id) đã phục vụ các truy vấn theo order_id, nhưng chưa có gì phục vụ tìm theo menu_item_id, nên mình tạo thêm idx_order_lines_menu_item_id. Lý do được ghi trong tài liệu PostgreSQL về ràng buộc: khi bạn xóa một dòng ở bảng được tham chiếu, database phải quét bảng tham chiếu để kiểm tra, nên thường nên đánh chỉ mục cho cột tham chiếu. Chỉ mục và cách đo hiệu quả của nó mình sẽ nói kỹ ở chặng về dữ liệu và hiệu năng.

Còn vì sao id là số tự tăng mà không dùng luôn tên món làm khóa? Giả sử order_lines ghi tên món thay vì mã món, rồi quán đổi tên "Cà phê sữa" thành "Cà phê sữa đá". Mọi dòng món cũ phải sửa theo, và chỉ cần sót một dòng thì món ấy tách thành hai món. Mã số không mang ý nghĩa nào nên không bao giờ phải đổi. Cách làm này gọi là khóa thay thế (surrogate key), và mình giữ UNIQUE trên tên để vẫn không có hai món trùng tên.

Vì sao dòng món phải lưu đơn giá lúc đặt?

Đây là chỗ mình sửa so với bài trước: order_lines giờ có thêm cột unit_price. Nhiều người sẽ nghĩ nó là dữ liệu thừa, vì giá nằm sẵn ở menu_items. Mình thử xem điều gì xảy ra nếu bạn bỏ nó đi và luôn tính tiền theo giá hiện tại của món:

bai-2-gia-luc-dat.sql
-- Tổng tiền đơn 1 và đơn 3: tính theo giá hiện tại của món (m.price) và theo giá lúc đặt (l.unit_price)
SELECT o.id AS ma_don, SUM(l.quantity * m.price) AS theo_gia_hien_tai, SUM(l.quantity * l.unit_price) AS theo_gia_luc_dat
FROM orders o JOIN order_lines l ON l.order_id = o.id JOIN menu_items m ON m.id = l.menu_item_id
WHERE o.id IN (1, 3) GROUP BY o.id ORDER BY o.id;

-- Quán tăng giá cà phê sữa từ 35000 lên 37000
UPDATE menu_items SET price = 37000 WHERE name = 'Cà phê sữa';

-- Chạy lại đúng câu SELECT ở trên
Kết quả: lần SELECT đầu, trước khi đổi giá
 ma_don | theo_gia_hien_tai | theo_gia_luc_dat
--------+-------------------+------------------
      1 |             85000 |            85000
      3 |            170000 |           170000
(2 rows)
Kết quả: lần SELECT sau, sau khi đổi giá
 ma_don | theo_gia_hien_tai | theo_gia_luc_dat
--------+-------------------+------------------
      1 |             89000 |            85000
      3 |            172000 |           170000
(2 rows)

Trước khi đổi giá, hai cách tính ra cùng một con số. Sau khi quán tăng giá cà phê sữa 2.000 đồng, tổng tiền đơn 1 (đã giao từ ngày 1/11) tự nhảy từ 85.000 lên 89.000 nếu bạn tính theo giá hiện tại, trong khi khách đã trả 85.000. Đơn 1 có hai ly cà phê sữa nên chênh 4.000; đơn 3 có một ly nên chênh 2.000. Doanh thu tháng 11 của quán vì thế sẽ thay đổi mỗi lần đổi giá, mà không có đơn nào mới.

Cột unit_price giữ giá tại thời điểm đặt, nên tổng tiền của đơn cũ không bao giờ đổi. Điều này nghe như phá luật "mỗi sự thật một chỗ", nhưng thật ra là hai sự thật khác nhau: giá hiện tại của món và giá mà khách đã trả cho dòng này. Mình ghi thêm quy tắc đó vào tài liệu miền chung DOMAIN.md của repo demo.

Vì sao tên khách vẫn nằm trong bảng orders?

Theo đúng sách, thông tin khách nên có bảng customers riêng, và orders chỉ giữ mã khách. Mình chưa tách vì quán Mộc hiện chỉ cần tên khách để gọi khi món xong, không có tài khoản, số điện thoại hay địa chỉ. Hai đơn của Lan (đơn 1 và đơn 5) chưa chắc là cùng một người Lan, nên dù có tách bảng thì tên cũng không đủ để làm khóa.

Mình sẽ tách customers khi có một trong các nhu cầu sau: lưu thông tin cần sửa một lần cho mọi đơn (số điện thoại, địa chỉ), cho khách đăng nhập để xem lại đơn, hoặc thống kê theo khách. Thiết kế bảng không phải là chuẩn hóa càng nhiều càng tốt. Mỗi bảng thêm vào là thêm một JOIN ở mọi truy vấn, nên mình chỉ tách khi lợi ích đã rõ. Ngược lại, chuẩn hóa quá ít thì quay về những lỗi ở đầu bài.

Hỏi phỏng vấn thế nào?

Nhà tuyển dụng ở vị trí junior thường hỏi "chuẩn hóa database là gì, khi nào không nên chuẩn hóa" và "khóa chính khác khóa ngoại thế nào". Họ muốn nghe lý do, không phải định nghĩa 1NF, 2NF, 3NF. Một câu trả lời tốt đi theo hướng của bài này: nêu ví dụ lỗi sửa sót khi dữ liệu bị lặp, sau đó nói khi nào mình cố ý lặp dữ liệu như cột unit_price. Bạn có thể xem câu trả lời mẫu ở mục phỏng vấn.

Bước tiếp theo là gì?

Bạn đã có bộ bảng cho đơn hàng, với các ràng buộc để database tự giữ dữ liệu đúng. Nhưng toàn bộ vẫn chỉ là SQL gõ tay vào một công cụ, chưa có ứng dụng nào đứng giữa người dùng và database. Đã đến lúc dựng một ứng dụng web bằng Spring Boot để nhận request, và để một thứ gọi là Spring giúp ta tạo và nối các thành phần của ứng dụng. Mời bạn theo dõi tiếp trong series Học Spring Boot: xây API đặt hàng từ con số 0.

Nhận bài tiếp theo qua email.

Một email mỗi tuần, chỉ khi có điều đáng đọc. Không spam.