Thiết kế bảng và chuẩn hóa (Schema 3 tầng)

Bộ dữ liệu cho bài này — orders_flat (ví dụ chưa chuẩn hóa)

Chuẩn hóa (kỹ thuật thiết kế tách bảng để giảm dữ liệu trùng lặp và không nhất quán) là ý tưởng phá vỡ một bảng khổng lồ chứa mọi thứ thành nhiều bảng dễ giữ nhất quán hơn.

Trong bài này bạn sẽ thấy các vấn đề xảy ra trong một bảng chưa chuẩn hóa với các cột lặp, rồi xây sự hiểu biết về 1NF / 2NF / 3NF và schema 3 tầng qua các bài tập thiết kế thực hành.

Bộ dữ liệu là orders_flat (2 dòng), nhồi dữ liệu đơn hàng vào một bảng duy nhất.

Đó là một bảng chưa chuẩn hóa kinh điển nơi tên khách, số điện thoại, sản phẩm 1 và sản phẩm 2 đều xếp hàng trong một dòng — và bạn sẽ chuẩn hóa nó thành ba bảng customer / order_record / order_line.

Trước khi vào bài tập, hãy xem định nghĩa cột và dữ liệu mẫu của bảng orders_flat.

① Chạy PRAGMA table_info(orders_flat); để kiểm tra tên và kiểu cột. Để ý các cột sản phẩm xuất hiện dưới dạng các cột lặp như item1 / item2.

② Chạy SELECT * FROM orders_flat; để xem trước tất cả các dòng. Cũng quan sát các dòng chỉ có một sản phẩm có chuỗi rỗng hoặc NULL ở item2.

Ba bất thường trong bảng chưa chuẩn hóa

Thiết kế như orders_flat nhồi mọi thứ vào một bảng duy nhất dễ dàng sinh ra mâu thuẫn khi bạn thêm, sửa, hoặc xóa dữ liệu.

Ví dụ kinh điển là ba: bất thường update / bất thường insert / bất thường delete.

Bất thường update xảy ra khi cùng một sự thật bị trùng lặp qua nhiều dòng — ví dụ, nếu bạn muốn đổi số điện thoại, bạn phải update mọi dòng mà không sót.

Bất thường insert là khi bạn không thể đăng ký một khách hàng chưa đặt đơn nào, vì không có dòng đơn để gắn thông tin khách vào.

Bất thường delete là khi xóa một đơn cũng xóa luôn thông tin bạn muốn giữ, như số điện thoại của khách.

Những điều này xảy ra vì thiết kế không giữ "một sự thật ở một nơi".

-- Ví dụ bất thường delete: xóa đơn 2 cũng xóa thông tin liên hệ của Bob
-- (quan sát hành vi trên bảng tạm flat_demo để không ảnh hưởng orders_flat thật)
DROP TABLE IF EXISTS flat_demo;
CREATE TABLE flat_demo(
  order_id INTEGER, customer TEXT, customer_tel TEXT, product TEXT
);
INSERT INTO flat_demo VALUES
  (1,'Alice','03-1111','Pen'),
  (2,'Bob','03-2222','Clip');

-- Bob chỉ có một đơn. Xóa nó cũng mất số điện thoại 03-2222 của Bob
DELETE FROM flat_demo WHERE order_id = 2;
SELECT * FROM flat_demo;
-- -> Thông tin liên hệ của Bob giờ biến mất khỏi cơ sở dữ liệu (bất thường delete)

Tái hiện trên một bảng tạm tình huống "số điện thoại của Alice đã đổi và bạn muốn cập nhật, nhưng cùng số điện thoại trùng lặp qua nhiều dòng". Bài tập này tự đứng vững — bạn sẽ tạo bảng riêng flat_anomaly để giữ nó độc lập. (Nếu bạn chạy đúng, phần giải thích sẽ xuất hiện.)

① DROP TABLE IF EXISTS bảng flat_anomaly, rồi CREATE nó với 4 cột: order_id / customer / customer_tel / product.

② Chèn 2 dòng cho các đơn của Alice (cả hai cùng số điện thoại 03-1111; sản phẩm là Pen và Note).

③ Chạy một UPDATE đổi số điện thoại của Alice thành 03-9999 nhưng chỉ chạm vào một dòng (ví dụ với điều kiện như WHERE order_id = 1), rồi chạy SELECT * FROM flat_anomaly; và xác nhận số điện thoại của hai dòng không khớp.

1NF đến 3NF — tách theo từng bước

Bảng được tổ chức qua chuẩn hóa theo các giai đoạn.

1NF — loại bỏ các cột lặp

Bỏ các cột lặp đánh số như item1 / item2.

Trước — các cột lặp item1 / item2

Sau — một dòng cho mỗi sản phẩm, với khóa chính composite `(order_id, product)`

2NF — chuyển các cột phụ thuộc vào chỉ một phần khóa chính vào bảng khác

Bảng từ bước trước có khóa chính composite (order_id, product). Nhưng nhìn kỹ: customer và tel được xác định chỉ bởi `order_id` (cùng order_id luôn có cùng khách).

Chuyển các cột phụ thuộc vào chỉ một phần khóa chính (phụ thuộc một phần) vào một bảng riêng là 2NF. Bạn tách nó thành header đơn hàng order_header và chi tiết đơn order_line.

Trước — customer và tel trùng trên mọi dòng

Sau — tách thành 2 bảng

order_header (một dòng cho mỗi đơn)

order_line (khóa chính composite (order_id, product), trong đó order_id là khóa ngoại tham chiếu order_header)

3NF — chuyển các cột được xác định bởi cột không khóa vào một bảng khác

Nhìn vào order_header từ bước trước, tel không được xác định bởi order_id mà bởi `customer` (cùng Alice luôn có 03-1111).

Loại bỏ các phụ thuộc đi qua một cột không khóa khác thay vì trực tiếp từ khóa chính (phụ thuộc bắc cầu: order_id -> customer -> tel) là điều 3NF nói tới.

Bạn trích xuất một bảng master khách hàng customer và để order_record chỉ giữ khóa ngoại đến khách.

Trước — tel phụ thuộc customer

Sau — tách thành 3 bảng

customer (master khách hàng)

order_record (header đơn hàng: tham chiếu khách qua khóa ngoại)

order_line (chi tiết đơn: giống bước trước)

Khi đã tách đến mức này, mỗi sự thật (số điện thoại của một khách, liên kết giữa đơn và khách, số lượng của một sản phẩm trong một đơn) sống ở đúng một nơi. Điều đó loại bỏ các bất thường update, insert và delete.

Schema 3 tầng và dạng cuối — customer / order_record / order_line

Schema sau chuẩn hóa có thể được tổ chức thành ba tầng với các vai trò khác nhau.

• Tầng master: các thực thể ít thay đổi và được tham chiếu bởi các bảng khác, như customer
• Tầng transaction: các bảng ghi nhận sự kiện kinh doanh, như order_record
• Tầng detail: các bảng giữ các dòng chi tiết của một sự kiện, như order_line

Bằng cách trỏ phía tham chiếu vào master bằng khóa ngoại, mỗi sự thật (như số điện thoại của một khách) nằm ở một nơi.

Dạng cuối là ba bảng này:

customer(customer_id khóa chính, customer_name, customer_tel), order_record(order_id khóa chính, customer_id -> customer), order_line(order_id -> order_record, product, qty, khóa chính composite (order_id, product)).

Số điện thoại nằm chỉ ở một dòng trong customer, nên một cập nhật là một dòng đơn; một khách không có đơn vẫn có thể được thêm vào customer (bất thường insert được giải quyết); và xóa một đơn vẫn để khách trong customer (bất thường delete được giải quyết).

-- Lợi ích của chuẩn hóa (chỉ đọc): số điện thoại của một khách nằm ở một dòng của customer
-- Trong dạng orders_flat bạn phải sửa mọi dòng đơn của Alice = bất thường update
UPDATE customer SET customer_tel = '03-9999'
WHERE customer_name = 'Alice';

-- Sửa một chỗ, và số mới chảy vào mọi đơn khi JOIN
SELECT o.order_id, c.customer_name, c.customer_tel
FROM order_record o
JOIN customer c ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Hãy tưởng tượng yêu cầu: "Chuẩn hóa orders_flat thành ba bảng — khách hàng, đơn hàng, và chi tiết đơn." Thiết kế schema cuối với khóa ngoại liên kết chúng, di chuyển dữ liệu sang, và dựng lại danh sách gốc bằng JOIN.

① Bật kiểm tra khóa ngoại, sau đó DROP TABLE IF EXISTS theo thứ tự order_line -> order_record -> customer (xóa con trước an toàn hơn).

② CREATE bảng customer (customer_id INTEGER khóa chính / customer_name TEXT NOT NULL / customer_tel TEXT).

③ CREATE bảng order_record (order_id INTEGER khóa chính / customer_id INTEGER NOT NULL, khóa ngoại đến customer).

④ CREATE bảng order_line (order_id INTEGER, khóa ngoại đến order_record / product TEXT NOT NULL / qty INTEGER NOT NULL / khóa chính composite (order_id, product)).

⑤ Chèn Alice(1, 03-1111) và Bob(2, 03-2222) vào customer; đơn 1 -> khách 1 và đơn 2 -> khách 2 vào order_record; Pen(đơn 1, 2) / Note(đơn 1, 1) / Clip(đơn 2, 5) vào order_line.

⑥ Kết hợp 3 bảng theo order_id và customer_id và chọn order_id / customer_name / customer_tel / product / qty theo thứ tự tăng dần của order_id và product để xác nhận bạn có thể dựng lại danh sách gốc.

Dùng các bảng customer / order_record / order_line từ Bài tập 2, xác nhận từng cái rằng các bất thường update, insert và delete từng làm phiền bảng chưa chuẩn hóa không còn xảy ra. Đây là bài tập cuối cùng của bài viết.

① Bất thường update được giải quyết: Đổi số điện thoại của Alice thành '03-9999'. Xác nhận đó là UPDATE một dòng đơn trên bảng customer.

② Bất thường insert được giải quyết: INSERT một khách mới Charlie (customer_id 3 / customer_name 'Charlie' / customer_tel '03-3333') vào customer. Xác nhận bạn có thể đăng ký một khách không có đơn.

③ Bất thường delete được giải quyết: Xóa đơn 1. Xóa từ con order_line trước, rồi từ order_record. Chạy SELECT * FROM customer ORDER BY customer_id; và xác nhận dòng của Alice vẫn ở trong customer.

Tips — Luôn đặt khóa chính trên mọi bảng

Dù bạn có chuẩn hóa hay không, hãy thiết kế mọi bảng với một khóa chính (`PRIMARY KEY`). Một khóa chính cho bạn ba điều:

- Định danh dòng duy nhất: không có hai dòng nào có cùng khóa, nên UPDATE / DELETE nhắm chính xác một dòng qua WHERE pk = ...

- Có thể tham chiếu từ bảng khác: một khóa ngoại cần một khóa chính (hoặc UNIQUE) ở phía kia. Đó là nền tảng để liên kết các bảng bạn tách qua chuẩn hóa

- Lập chỉ mục tự động: các cột khóa chính tự động nhận chỉ mục B-Tree, tăng tốc tìm kiếm bằng và JOIN

Nếu không có cột đơn nào duy nhất tự thân, dùng khóa chính composite như PRIMARY KEY (order_id, product). Bỏ qua với "để sau thêm" làm các dòng trùng không phân biệt được và mất cả ba lợi ích trên.