Kiến thức nền
Dạng chuẩn: 1NF → BCNF, với ví dụ
Các quy tắc thiết kế bảng của Codd — mỗi dị thường trông như thế nào, dạng chuẩn tiếp theo sửa nó như thế nào, và khi nào cố tình phá vỡ quy tắc cho hiệu năng.
TL;DR
Dạng chuẩn là một tập các quy tắc phân cấp để cấu trúc các bảng quan hệ nhằm ngăn ngừa dư thừa và dị thường cập nhật. Codd định nghĩa ba dạng đầu tiên (1NF, 2NF, 3NF) trong một bài báo tiếp nối năm 1971 cho bài báo nền tảng năm 1970. Dạng Chuẩn Boyce-Codd (BCNF) theo sau vào năm 1974 sau khi Ray Boyce chỉ ra một trường hợp 3NF bỏ sót. 4NF và 5NF tồn tại nhưng hiếm khi được viện dẫn. Hầu hết schema sản xuất nhắm đến 3NF hoặc BCNF. Phi chuẩn hóa dưới đó — có chủ ý, cho hiệu năng — là một đánh đổi kỹ thuật có chủ ý, không phải một sai lầm. Điểm mấu chốt của việc biết quy tắc là biết bạn đang phá vỡ quy tắc nào.
Các dị thường mà dạng chuẩn ngăn ngừa
Trước khi đi qua các dạng chuẩn, đáng để xem các vấn đề chúng tồn tại để giải quyết. Tất cả đều đến từ dư thừa — lưu trữ cùng một sự thật ở nhiều nơi, để cập nhật, chèn, hoặc xóa có thể để cơ sở dữ liệu ở trạng thái không nhất quán.
Xem xét một thiết kế một-bảng cho khách hàng và đơn hàng của họ:
┌────┬─────────────┬──────────┬─────────┬──────────┐
│ id │ customer │ city │ order_id│ amount │
├────┼─────────────┼──────────┼─────────┼──────────┤
│ 1 │ Ada │ London │ 1001 │ 250 │
│ 2 │ Ada │ London │ 1002 │ 430 │
│ 3 │ Turing │ London │ 1003 │ 80 │
└────┴─────────────┴──────────┴─────────┴──────────┘Ba dị thường:
- Dị thường cập nhật. Nếu Ada chuyển đến Paris, chúng ta phải cập nhật hai hàng. Sai một cái, và cơ sở dữ liệu giờ nói Ada sống ở hai thành phố cùng lúc.
- Dị thường chèn. Chúng ta không thể ghi một khách hàng chưa có đơn hàng — không có hàng nào để đặt họ vào.
- Dị thường xóa. Nếu chúng ta xóa đơn hàng duy nhất của Turing (1003), chúng ta cũng xóa luôn sự thật Turing tồn tại.
Dạng chuẩn là các quy tắc hình thức để tái cấu trúc dữ liệu sao cho các dị thường này không thể xảy ra.
Dạng Chuẩn Thứ Nhất (1NF)
Mỗi cột chứa một giá trị nguyên tử duy nhất. Không có nhóm lặp, không có cấu trúc lồng nhau.
Trước 1NF — một bảng như thế này không ở 1NF:
┌────┬──────────┬──────────────────────────────┐
│ id │ customer │ phones │
├────┼──────────┼──────────────────────────────┤
│ 1 │ Ada │ "555-1234, 555-5678" │
│ 2 │ Turing │ "555-9999" │
└────┴──────────┴──────────────────────────────┘Cột phones lưu một danh sách phân cách bởi dấu phẩy. Điều này làm truy vấn trở nên khủng khiếp (“tìm tất cả khách hàng với một mã vùng nhất định” yêu cầu khớp chuỗi con), làm cập nhật nguy hiểm (dấu phân cách không nhất quán), và vi phạm 1NF.
Sửa 1NF: tách thuộc tính đa-giá-trị vào bảng riêng.
customers phones
┌────┬──────────┐ ┌────┬──────────┬──────────┐
│ id │ customer │ │ id │ cust_id │ number │
├────┼──────────┤ ├────┼──────────┼──────────┤
│ 1 │ Ada │ │ 1 │ 1 │ 555-1234 │
│ 2 │ Turing │ │ 2 │ 1 │ 555-5678 │
└────┴──────────┘ │ 3 │ 2 │ 555-9999 │
└────┴──────────┴──────────┘Yêu cầu “nguyên tử” thú vị trong SQL hiện đại. Postgres cho phép bạn lưu mảng và tài liệu JSON trong một cột, về mặt kỹ thuật vi phạm 1NF cổ điển. Trong thực tế, ổn thôi nếu bạn dùng các cột đó cho dữ liệu bạn không bao giờ cần truy vấn từng phần. Nếu bạn thấy mình đang truy vấn vào một mảng, có lẽ bạn nên chuẩn hóa nó ra ngoài.
Dạng Chuẩn Thứ Hai (2NF)
1NF, cộng với: mọi thuộc tính không-khóa phụ thuộc vào toàn bộ khóa chính, không chỉ một phần của nó.
Quy tắc này chỉ quan trọng cho các bảng có khóa chính tổng hợp (khóa chính gồm nhiều cột). Các bảng với khóa một cột mặc nhiên ở 2NF.
Xem xét một bảng order-items nơi khóa chính là (order_id, product_id):
┌──────────┬────────────┬───────────┬──────────────┬────────┐
│ order_id │ product_id │ product_name│ unit_price │ quantity│
├──────────┼────────────┼───────────┼──────────────┼────────┤
│ 1001 │ 42 │ Teapot │ 9.99 │ 2 │
│ 1001 │ 43 │ Mug │ 4.99 │ 4 │
│ 1002 │ 42 │ Teapot │ 9.99 │ 1 │
└──────────┴────────────┴───────────┴──────────────┴────────┘Vấn đề: product_name và unit_price chỉ phụ thuộc vào product_id, không phải khóa đầy đủ (order_id, product_id). Tên và giá của teapot bị lặp lại mỗi khi nó xuất hiện trong một đơn hàng.
Sửa 2NF: kéo dữ liệu sản phẩm ra bảng riêng.
order_items products
┌──────────┬────────────┬────────┐ ┌─────┬───────────┬────────┐
│ order_id │ product_id │quantity│ │ id │ name │ price │
├──────────┼────────────┼────────┤ ├─────┼───────────┼────────┤
│ 1001 │ 42 │ 2 │ │ 42 │ Teapot │ 9.99 │
│ 1001 │ 43 │ 4 │ │ 43 │ Mug │ 4.99 │
│ 1002 │ 42 │ 1 │ └─────┴───────────┴────────┘
└──────────┴────────────┴────────┘Bây giờ tên và giá của mỗi sản phẩm sống chính xác một lần. Cập nhật giá teapot ở một chỗ; mọi đơn hàng sử dụng nó tự động phản ánh thay đổi (ờ — giả định bạn không cũng ghi giá lịch sử, đó là một mối quan tâm riêng).
Dạng Chuẩn Thứ Ba (3NF)
2NF, cộng với: không thuộc tính không-khóa nào phụ thuộc vào thuộc tính không-khóa khác (không có phụ thuộc bắc cầu).
Xem xét một bảng orders:
┌────┬─────────┬──────────────┬──────────┐
│ id │ cust_id │ cust_name │ amount │
├────┼─────────┼──────────────┼──────────┤
│ 1001│ 101 │ Ada │ 250 │
│ 1002│ 101 │ Ada │ 430 │
│ 1003│ 102 │ Turing │ 80 │
└────┴─────────┴──────────────┴──────────┘cust_name phụ thuộc vào cust_id, không trực tiếp vào khóa chính id. Đây là một phụ thuộc bắc cầu: id → cust_id → cust_name. Nếu Ada đổi tên, chúng ta sẽ phải cập nhật mọi hàng đơn hàng.
Sửa 3NF: di chuyển tên khách hàng vào bảng customers, được tham chiếu bởi cust_id của đơn hàng:
orders customers
┌────┬─────────┬────────┐ ┌─────┬──────────┐
│ id │ cust_id │ amount │ │ id │ name │
├────┼─────────┼────────┤ ├─────┼──────────┤
│1001│ 101 │ 250 │ │ 101 │ Ada │
│1002│ 101 │ 430 │ │ 102 │ Turing │
│1003│ 102 │ 80 │ └─────┴──────────┘
└────┴─────────┴────────┘Cấu trúc ba bảng: customers, orders, mối quan hệ join qua cust_id. Đây là nơi hầu hết schema sản xuất dừng lại. 3NF là mục tiêu cho đa số hệ thống OLTP, và một khi bạn đạt được nó, bạn đã tránh được phần lớn dị thường cập nhật.
Dạng Chuẩn Boyce-Codd (BCNF)
3NF, cộng với: mọi phụ thuộc hàm có siêu khóa ở vế trái.
BCNF là phiên bản nghiêm ngặt hơn của 3NF bao phủ một trường hợp mà 3NF về mặt kỹ thuật cho phép. Tưởng tượng một bảng theo dõi giáo sư nào dạy khóa học nào, với ràng buộc rằng mỗi giáo sư chỉ dạy một khóa nhưng nhiều giáo sư có thể dạy cùng một khóa:
┌───────────┬────────────┬───────────────┐
│ student │ course │ professor │
├───────────┼────────────┼───────────────┤
│ Alice │ 6.001 │ Sussman │
│ Bob │ 6.001 │ Sussman │
│ Alice │ 6.004 │ Knight │
│ Carol │ 6.001 │ Abelson │
└───────────┴────────────┴───────────────┘Khóa chính: (student, course). Vì professor phụ thuộc vào toàn bộ khóa (giáo sư nào phụ thuộc vào ai là sinh viên và khóa nào), nó ở 3NF.
Nhưng có một phụ thuộc ẩn: professor → course. Sussman dạy 6.001. Đó là sự thật về Sussman, không phải về bất kỳ cặp sinh-viên-khóa nào cụ thể. Nếu chúng ta muốn ghi rằng Abelson sẽ bắt đầu dạy 6.001 kỳ sau, chúng ta không có chỗ nào để đặt sự thật đó mà không có một sinh viên cũng liên quan.
Sửa BCNF: tách thành hai bảng.
enrollments teaches
┌───────────┬────────────┐ ┌───────────┬────────────┐
│ student │ professor │ │ professor │ course │
├───────────┼────────────┤ ├───────────┼────────────┤
│ Alice │ Sussman │ │ Sussman │ 6.001 │
│ Bob │ Sussman │ │ Knight │ 6.004 │
│ Alice │ Knight │ │ Abelson │ 6.001 │
│ Carol │ Abelson │ └───────────┴────────────┘
└───────────┴────────────┘Mỗi bảng giờ có một phụ thuộc hàm mà vế trái là siêu khóa.
BCNF được Codd và Boyce giới thiệu năm 1974 như một tinh chỉnh bắt các trường hợp 3NF bỏ sót. Trong thực tế, hầu hết schema 3NF cũng ở BCNF — các trường hợp chúng khác nhau hiếm và thường liên quan đến các bảng mô hình hóa quy tắc kinh doanh với nhiều khóa độc lập.
4NF: Phụ thuộc đa giá trị
BCNF, cộng với: không có phụ thuộc đa giá trị phi-tầm-thường.
Xem xét một bảng nhân viên với nhiều kỹ năng và nhiều ngôn ngữ, nơi hai thuộc tính này hoàn toàn độc lập:
┌────────────┬─────────┬──────────┐
│ employee │ skill │ language │
├────────────┼─────────┼──────────┤
│ Dylan │ Python │ English │
│ Dylan │ Python │ Spanish │
│ Dylan │ SQL │ English │
│ Dylan │ SQL │ Spanish │
└────────────┴─────────┴──────────┘Tích Descartes của kỹ năng và ngôn ngữ được lưu. Nếu Dylan học một ngôn ngữ mới, chúng ta thêm hai hàng mới (một cho mỗi kỹ năng hiện có). Đây là một phụ thuộc đa giá trị — employee →→ skill và employee →→ language — và 4NF nói: tách chúng ra.
employee_skills employee_languages
┌────────────┬─────────┐ ┌────────────┬──────────┐
│ employee │ skill │ │ employee │ language │
├────────────┼─────────┤ ├────────────┼──────────┤
│ Dylan │ Python │ │ Dylan │ English │
│ Dylan │ SQL │ │ Dylan │ Spanish │
└────────────┴─────────┘ └────────────┴──────────┘4NF hiếm khi được gọi tên trong thiết kế schema sản xuất, vì tình huống nó ngăn ngừa tương đối hiếm và hầu hết nhà thiết kế theo bản năng tách các thuộc tính độc lập mà không nghĩ “4NF.”
5NF và 6NF
5NF (Project-Join Normal Form) xử lý các trường hợp nơi một quan hệ có thể được phân rã thành ba hay nhiều quan hệ nhỏ hơn nhưng không thể thành hai. Nó gần như không bao giờ được viện dẫn — các trường hợp bí ẩn, và hầu hết người thực hành không thiết kế rõ ràng cho nó.
6NF là một dạng trong thế giới nghiên cứu chủ yếu được dùng trong cơ sở dữ liệu thời gian, nơi mọi sự thật được lưu với một phạm vi timestamp. Ngoài các ngữ cảnh chuyên biệt, 6NF là một đối tượng học thuật hơn.
Nếu bạn đang hỏi liệu schema của mình có ở 5NF hay không, bạn đang làm việc trên một bài báo nghiên cứu, không phải một cơ sở dữ liệu sản xuất.
Phi chuẩn hóa có chủ ý
Chuẩn hóa là một mặc định, không phải một luật. Có các lý do hợp pháp để cố tình phá vỡ quy tắc.
Khối lượng công việc đọc-nặng với cập nhật không thường xuyên hưởng lợi từ phi chuẩn hóa. Một kho dữ liệu lưu transactions có thể phi chuẩn hóa vị trí khách hàng, danh mục sản phẩm, và tiền tệ lên bảng fact, tạo ra một star schema nơi truy vấn có thể trả lời hầu hết câu hỏi không cần join. Snowflake, BigQuery, Redshift — tất cả đều có schema trông hoàn toàn không giống 3NF theo thiết kế.
Các hệ thống ghi-nặng, quy mô thấp hưởng lợi từ chuẩn hóa. Một cơ sở dữ liệu OLTP phục vụ ~1000 ghi/giây với các quy tắc kinh doanh phức tạp dễ lý giải hơn nhiều ở 3NF.
Caching và materialized view là một dạng phi chuẩn hóa. Bạn không thay đổi schema nguồn-của-sự-thật; bạn tạo các bản sao dẫn xuất, phi chuẩn hóa cho hiệu năng. Điều này có thể là tốt nhất của cả hai thế giới nếu bạn có thể chịu đựng sự lỗi thời.
Một vài quy tắc để phi chuẩn hóa có trách nhiệm:
- Giữ nguồn-của-sự-thật đã chuẩn hóa. Phi chuẩn hóa là một cache, không phải dữ liệu chính.
- Làm cho phi chuẩn hóa rõ ràng. Đừng sao chép một trường “để phòng”; sao chép nó vì có một truy vấn cụ thể cần nó nhanh.
- Ghi lại các đường cập nhật. Khi dữ liệu nguồn thay đổi, thứ gì đó phải cập nhật bản sao phi chuẩn hóa. Nếu bạn không biết cái gì, bản sao sẽ trôi.
- Đo điểm chuẩn trước khi phi chuẩn hóa. Hầu hết các vấn đề hiệu năng có các cách sửa khác (index, truy vấn tốt hơn, các lớp cache) không yêu cầu vi phạm chuẩn hóa.
Tại sao các dạng đến theo thứ tự
Một tính chất đáng chú ý của dạng chuẩn: chúng lồng nhau. BCNF ngụ ý 3NF, 3NF ngụ ý 2NF, 2NF ngụ ý 1NF. Nếu bạn đạt được BCNF, bạn cũng thỏa mãn mọi dạng trước đó.
Đây không phải sự trùng hợp. Mỗi dạng định nghĩa một loại dư thừa mới mà các dạng trước cho phép. Bạn không thể nói về phụ thuộc bắc cầu (3NF) nếu bạn chưa thiết lập đầu tiên rằng có một tập thuộc tính được định nghĩa tốt để lý giải (1NF). Bạn không thể có phụ thuộc một phần (2NF) trừ khi bạn có khóa tổng hợp, điều mà sự tồn tại của khóa chính (1NF) thiết lập.
Các dạng được giới thiệu theo trình tự thời gian vì chúng giải quyết dư thừa ngày càng tinh vi. Cơ sở dữ liệu những năm 1970 vật lộn với 1NF (người ta vẫn dùng các cột độ dài thay đổi với dấu phân cách nhúng). Đến cuối những năm 1980, 3NF là mẫu thiết kế mặc định, và đến những năm 1990, người ta gần như đã ngừng tái phái sinh BCNF vì thực hành thiết kế tiêu chuẩn đã tạo ra nó tự động.
Điều các dạng chuẩn thực sự cho bạn
Bài về Mô hình Quan hệ tóm tắt chuẩn hóa là “các quy tắc để cấu trúc các bảng nhằm giảm thiểu dư thừa và ngăn ngừa mâu thuẫn.” Điều đó chính xác trong phạm vi của nó. Điều sâu hơn mà dạng chuẩn cung cấp là một từ vựng thiết kế — một tập khái niệm bạn có thể dùng để mô tả tại sao một schema cảm thấy sai.
“Đó là một dị thường cập nhật” là một chỉ trích chính xác, không phải mơ hồ. “Cái này không phải 2NF” là một vấn đề cụ thể, có thể sửa. Có ngôn ngữ cho phép bạn chẩn đoán schema nhanh chóng và có các cuộc trò chuyện có cấu trúc về tại sao điều gì đó đau đớn.
Trong sản xuất: nhắm đến 3NF hoặc BCNF như mặc định. Phi chuẩn hóa có chủ ý, với bằng chứng, cho các khối lượng công việc cụ thể. Sử dụng dạng chuẩn như một công cụ để suy nghĩ về dữ liệu, không phải như một danh sách kiểm tra tuân thủ để qua.
Bài báo năm 1971 của Codd giới thiệu dạng chuẩn phần lớn là một dọn dẹp đề xuất năm 1970 của ông. Năm mươi năm sau, nó vẫn là từ vựng làm việc của thiết kế schema. Đó là một thuộc tính hiếm cho một tác phẩm khoa học máy tính những năm 1970. Nó đứng vững vì nó ít về cơ sở dữ liệu và nhiều về cách dữ liệu từ chối cư xử khi bạn biểu diễn nó một cách dư thừa — một sự thật vẫn đúng bất kể bạn đang làm việc với engine lưu trữ, ngôn ngữ truy vấn, hay ngành nào.