Bài 22: Chuẩn hóa: lưu một sự thật ở đúng chỗ

Cô Mai nhận một bảng đăng ký có tên học viên và tên lớp lặp ở nhiều dòng. Khi một bạn đổi tên hiển thị, cô phải sửa tên ấy ở từng lớp. Chỉ cần bỏ sót một dòng, hai danh sách sẽ gọi cùng một người bằng hai tên khác nhau. Cô muốn sửa thông tin ở một nơi mà mọi báo cáo vẫn lấy đúng tên mới.

Mỗi đăng ký cần một dòng riêng

Giả sử ô lớp học của An chứa cả hai tên, cách nhau bằng dấu phẩy. Bạn đọc hiểu được, nhưng cơ sở dữ liệu chỉ thấy một chuỗi. Ta dùng bảng phụ list_demo để thử kiểu lưu này.

Bình chỉ học lớp Python nâng cao trong ví dụ riêng, không học lớp Python. Dữ liệu này không thay bảng dùng chung:

Nếu tìm đúng học viên học Python bằng phép so sánh bằng, câu lệnh sau cho thấy vấn đề:

SELECT student_id
FROM list_demo
WHERE class_names = 'Python';

Kết quả rỗng vì cả 'Python,Database' và 'Python nâng cao' đều khác 'Python'. So sánh cả chuỗi bỏ sót An dù bạn ấy học lớp Python.

Thử tìm một phần chuỗi bằng LIKE, với % đại diện cho đoạn chữ bất kỳ:

SELECT student_id, class_names
FROM list_demo
WHERE class_names LIKE '%Python%'
ORDER BY student_id;

Kết quả có cả An và Bình vì hai chuỗi đều chứa 'Python'. Bình bị lấy nhầm vào danh sách lớp Python, dù chỉ học Python nâng cao. Tìm một phần chuỗi không phân biệt được hai lớp này. Việc đổi tên lớp cũng đòi hỏi cắt và ghép chuỗi ở nhiều ô.

Dạng chuẩn thứ nhất (first normal form, 1NF) yêu cầu mỗi ô chứa một giá trị theo loại thông tin đã chọn. Với mô hình đăng ký, mỗi giá trị mã lớp chỉ chỉ một lớp. Ta lưu từng cặp người và lớp thành dòng, thay vì gộp các mã vào một chuỗi.

Trong bộ dữ liệu chung, An đã có hai dòng đăng ký riêng. Hãy lấy từng lớp mà An đã đăng ký:

SELECT e.student_id, c.name AS class_name
FROM enrollments AS e
JOIN classes AS c ON c.id = e.class_id
WHERE e.student_id = 1
ORDER BY c.id;

Kết quả có hai dòng riêng. Mỗi dòng có thể kiểm khóa ngoại, lưu trạng thái và lọc theo mã lớp. Dấu phẩy trong một câu văn vẫn bình thường. Vấn đề ở đây là dùng chuỗi danh sách để thay cho nhiều quan hệ cần quản lý độc lập.

Tên học viên không phụ thuộc lớp đã đăng ký

Ta gọi phụ thuộc hàm (functional dependency) là quy tắc: biết một giá trị mã thì xác định được thông tin đi kèm. Ví dụ, trong dữ liệu đang quản lý, student_id xác định tên hiện tại của học viên. Quy tắc này đến từ ý nghĩa của mã, không chỉ từ vài dòng tình cờ giống nhau.

Bảng phụ sau mô phỏng bản đăng ký chưa tách. Nó lấy dữ liệu thật từ ba bảng chung, rồi lặp tên người và tên lớp trên từng dòng:

Hãy xem tên học viên đang bị lặp theo các đăng ký như thế nào:

SELECT student_id, class_id, student_name
FROM flat_roster
WHERE student_id = 1
ORDER BY class_id;

Hai dòng cùng ghi An vì đều thuộc student_id = 1. Đổi lớp không làm tên người đổi. Tương tự, class_id xác định tên lớp, còn cặp (student_id, class_id) mới xác định trạng thái đăng ký của người ấy ở lớp ấy.

Dạng chuẩn thứ hai (second normal form, 2NF) yêu cầu bảng đã đạt 1NF và thuộc tính không khóa phụ thuộc toàn bộ mỗi khóa ứng viên. Khóa ứng viên là tập cột tối thiểu có thể xác định duy nhất dòng. Trong bảng này, ta chọn cặp người và lớp làm khóa.

Tên người và tên lớp chỉ phụ thuộc từng phần của khóa ghép nên được chuyển ra bảng riêng. Trạng thái vẫn ở bảng đăng ký vì một học viên có thể đang học lớp này nhưng tạm dừng lớp khác. Thêm một mã dòng mới không tự xóa các phụ thuộc đã có.

Thông tin của giáo viên thuộc về giáo viên

Ngay cả khi bảng có khóa một cột, dữ liệu vẫn có thể lặp sai chỗ. Giả sử mỗi lớp có một giáo viên phụ trách, và mỗi giáo viên có một số liên hệ hiện tại. Nếu lưu số liên hệ trên mọi lớp, khi số đổi, bạn phải sửa từng lớp.

Phụ thuộc bắc cầu (transitive dependency) xuất hiện khi khóa quyết định một thuộc tính, rồi thuộc tính ấy quyết định thông tin khác. Ở đây, mã lớp xác định mã giáo viên, và mã giáo viên xác định số liên hệ.

Dạng chuẩn thứ ba (third normal form, 3NF) yêu cầu bảng đã đạt 2NF. Mọi cột không thuộc khóa chỉ phụ thuộc vào khóa, không phụ thuộc qua một cột không khóa khác.

Ta tạo hai bảng phụ với liên hệ giả định dành riêng cho ví dụ. Các chuỗi demo-01, demo-02 là nhãn minh họa, không phải thông tin liên hệ thật. Mã giáo viên là duy nhất, còn tên chỉ là thuộc tính:

Lấy liên hệ hiện tại qua mã giáo viên, thay vì chép liên hệ vào từng dòng lớp:

SELECT ct.class_id, t.name, t.contact
FROM class_teachers AS ct
JOIN teachers_demo AS t ON t.id = ct.teacher_id
ORDER BY ct.class_id;

Hai lớp đầu nhận cùng liên hệ từ một dòng giáo viên. Khi sửa liên hệ ở bảng giáo viên, các báo cáo sau đó đọc được giá trị mới. Ghép theo tên không bảo đảm điều này vì hai người có thể trùng tên.

Sơ đồ dưới đây cho thấy mã giáo viên nằm trên quan hệ phân công, còn liên hệ ở bảng người phụ trách:

Ghép lại để kiểm thông tin không bị mất

Chuẩn hóa (normalization) là tổ chức bảng theo các phụ thuộc để giảm lỗi thêm, sửa và xóa dữ liệu. Mục đích không phải tăng số bảng. Sau khi tách, phải dựng lại được những sự thật ban đầu bằng các mã liên kết.

Trong dữ liệu chung, tên được lưu riêng ở students và classes. Truy vấn sau dựng lại đầy đủ từng lần đăng ký, kể cả đăng ký tạm dừng:

SELECT s.id, s.name, c.id AS class_id,
       c.name AS class_name, e.status
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN classes AS c ON c.id = e.class_id
ORDER BY s.id, c.id;

Mỗi đăng ký vẫn có đúng tên người, tên lớp và trạng thái. Dung vẫn xuất hiện với 'paused' vì câu hỏi đang kiểm mọi quan hệ đã lưu. Tự lọc trạng thái khi đối chiếu sẽ khiến bạn tưởng một đăng ký đã bị mất sau khi tách.

Lan không có đăng ký nên không xuất hiện trong báo cáo này. Dòng học viên của Lan vẫn tồn tại trong bảng riêng. Nếu báo cáo cần mọi học viên, hãy bắt đầu từ bảng học viên và dùng phép nối giữ đủ dòng:

SELECT s.id, s.name, e.class_id
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id
ORDER BY s.id, e.class_id;

Vì vậy, kết quả thiếu người có thể do chọn sai phép nối, không phải do việc tách bảng. Khi kiểm sau tách, hãy so từng cặp mã và các giá trị đi kèm. Hai bảng có cùng số dòng vẫn có thể nối nhầm người với lớp.

Dữ liệu tính lại được cần nguồn rõ ràng

Một số con số được tính từ nhiều dòng khác. Dữ liệu dẫn xuất (derived data) là kết quả như tổng thay đổi xu của một học viên. Nếu lưu thêm kết quả để đọc nhanh, người quản lý phải biết tính từ đâu và cập nhật khi nào.

Bảng ledger lưu từng thay đổi có dấu. Câu sau cộng riêng các thay đổi đang có trong bảng ấy:

SELECT student_id, SUM(delta) AS net_change
FROM ledger
GROUP BY student_id
ORDER BY student_id;

Kết quả là tổng các thay đổi, không phải số dư hiện tại. Muốn tính số dư còn cần biết số dư ban đầu và lịch sử đã đủ hay chưa. Gán tổng này vào coins sẽ làm mất ý nghĩa số xu đang lưu.

Lỗi thường gặp

Tóm tắt

• Mỗi quan hệ cần quản lý độc lập nên có dòng và mã liên kết riêng.
• Tên học viên thuộc học viên, tên lớp thuộc lớp, trạng thái đăng ký thuộc cả cặp.
• Thông tin hiện tại của giáo viên nên lấy từ dòng giáo viên qua mã đã phân công.
• Dựng lại báo cáo giúp kiểm việc tách bảng có giữ nguyên ý nghĩa dữ liệu hay không.
• Dữ liệu tính sẵn chỉ đáng tin khi có nguồn và cách duy trì rõ ràng.

Kiểm tra nhanh

Đọc thêm

• CS50 SQL: thiết kế và tổ chức dữ liệu thành các bảng.
• SQLite: ghép bảng để dựng kết quả truy vấn.