Bài 20: Bài thiết kế: bảng ghi nhận phần thưởng

Sau buổi sinh hoạt, cô Mai muốn ghi lại ai được thưởng và vì sao. Một học viên có thể được thưởng vì hoàn thành bài, rồi nhận thêm một lần vì giúp bạn. Nếu bảng chỉ giữ một con số tổng, cô không xem lại được từng lý do. Nếu mỗi người chỉ được có một dòng, lần thưởng thứ hai lại không có chỗ lưu.

Một dòng là một lần thưởng, không phải một người

Sự kiện (event) là một việc đã xảy ra mà bạn cần ghi lại riêng. Trong bảng phần thưởng, sự kiện gồm người nhận, số xu và lý do. Người nhận có thể lặp vì một người có nhiều sự kiện. Mã của từng sự kiện cần khác nhau để tra cứu đúng lần.

Hãy phân biệt bảng hồ sơ với bảng lịch sử. Bảng students trả lời người này là ai và hiện có bao nhiêu xu. Bảng phần thưởng trả lời lần nào đã thưởng, thưởng bao nhiêu và ghi lý do gì. Ghi thêm lịch sử không có nghĩa tự thay đổi số dư ở bảng hồ sơ.

Ví dụ dùng bảng riêng reward_examples, chỉ phục vụ bài học. Hai dòng đều thuộc học viên mã 1, nhưng có mã lần thưởng khác nhau:

SELECT id, student_id, amount, reason
FROM reward_examples
ORDER BY id;

Lần 1 thưởng 20 xu, còn lần 2 thưởng 10 xu. Cả hai có cùng student_id = 1, nghĩa là cùng người nhận. Nếu lấy student_id làm khóa chính duy nhất, lần thứ hai sẽ bị chặn dù hoàn toàn hợp lệ. Khóa phải theo điều mà một dòng đại diện, không theo cột nhìn có vẻ quan trọng nhất.

Chọn cột và khai báo bảng từ yêu cầu

Thiết kế bảng (table design) là chuyển yêu cầu cần lưu thành các cột và quy tắc. Hãy bắt đầu bằng những câu đầy đủ: mỗi lần thưởng có mã riêng, phải có người nhận, số thưởng phải nguyên dương. Lý do phải có giá trị. Từ mỗi câu, bạn chọn một phần khai báo để bảo vệ.

Tên bảng mới là rewards. Cột id nhận diện lần thưởng, còn student_id tham chiếu students.id. Cột amount lưu số xu được thưởng, nên dùng số nguyên. Cột reason lưu lý do dạng chữ.

Chuyển các yêu cầu thành cấu trúc rỗng và đọc lại danh sách cột:

CREATE TABLE rewards (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id),
  amount INTEGER NOT NULL CHECK(amount > 0),
  reason TEXT NOT NULL
) STRICT;
SELECT name, type, "notnull" AS required, pk
FROM pragma_table_info('rewards')
ORDER BY cid;

Kết quả cho thấy id là khóa chính qua cột pk. Hai cột số student_id, amount và cột chữ reason đều bắt buộc theo khai báo. Bảng vừa tạo chưa có dòng nào. Truy vấn đọc thông tin cấu trúc không tự thêm phần thưởng.

Mỗi quy tắc có một nhiệm vụ riêng:

Bạn không cần thêm cột tên người nhận vào bảng này. Khi hiển thị, truy vấn có thể nối tới hồ sơ bằng mã. Nếu chép tên vào mọi dòng thưởng, đổi tên người sẽ kéo theo nhiều bản sao cần sửa. Mã người giữ quan hệ rõ ràng mà không lặp lại thông tin hồ sơ.

Các giá trị biên cho biết quy tắc đã đúng chưa

Giá trị biên (boundary value) là giá trị nằm sát giới hạn đang kiểm. Với thưởng nguyên dương, 0 phân biệt điều kiện amount > 0 và amount >= 0. Số âm rõ ràng không phải khoản thưởng theo yêu cầu này. Giá trị thiếu cần một phép kiểm khác vì NULL không phải số không.

Để thử riêng từng trường hợp, mục này dùng cấu trúc rewards rỗng giống mục trước. Mỗi ví dụ tạo lại bảng từ khối chuẩn bị rewards-empty, nên các lần thử không ghi chồng lên nhau.

Thử thêm số thưởng dương cho người có thật:

INSERT INTO rewards(id, student_id, amount, reason)
VALUES (1, 1, 20, 'Hoàn thành bài');

Bảng sau lệnh có đúng một lần thưởng. Mã người 1 tồn tại trong students, số thưởng là số nguyên dương, lý do không thiếu. Lệnh chỉ thêm vào rewards. Không có thao tác nào sửa students.coins trong ví dụ này.

Tiếp theo, thử mức bằng không mà thiết kế phải từ chối:

INSERT INTO rewards(id, student_id, amount, reason)
VALUES (1, 1, 0, 'Hoàn thành bài');

Thông báo lỗi chỉ ra điều kiện kiểm số thưởng. Nếu dùng >= 0, lệnh này sẽ thành công và bảng không còn đúng yêu cầu nguyên dương. Nếu gửi NULL, NOT NULL mới là phần chặn. Nếu gửi 20.5, bảng STRICT từ chối vì không chuyển nguyên vẹn sang số nguyên.

Còn một khác biệt nhỏ nhưng cần nói rõ: reason TEXT NOT NULL vẫn cho chuỗi rỗng ''. Muốn lý do chứa ký tự sau khi bỏ khoảng trắng, bạn cần quy tắc kiểm thêm. Thiết kế đang xét chỉ yêu cầu không thiếu giá trị, nên vẫn cho phép chuỗi rỗng. Mô tả thiết kế phải đúng với điều thực sự đã khai báo.

Khóa ngoại kiểm người nhận tồn tại

Khóa ngoại bảo vệ quan hệ giữa lần thưởng với người nhận. Nó ngăn ghi một khoản cho mã học viên không tồn tại.

Thử mã 999, không có trong bảng học viên mẫu:

INSERT INTO rewards(id, student_id, amount, reason)
VALUES (1, 999, 10, 'Giúp bạn');

Số thưởng và lý do đều hợp lệ, nhưng mã người nhận không tồn tại nên câu lệnh thất bại. Khóa ngoại chỉ kiểm tham chiếu, không kiểm quyền tặng thưởng. Phần phân quyền được trình bày ở bài 39.

Bạn nên kiểm thiết kế bằng cả những việc được phép và những việc phải bị chặn:

Chỉ thử thêm một dòng đúng chưa đủ phát hiện ràng buộc thừa. Chẳng hạn UNIQUE(student_id) vẫn cho dòng đầu thành công nhưng chặn lần thưởng tiếp theo. Hai sự kiện cùng người là phép thử cần có. Thiết kế tốt vừa ngăn dữ liệu sai vừa không cản nhu cầu hợp lệ.

Tổng khoản cộng khác thay đổi ròng và số dư

Tổng khoản cộng là tổng các thay đổi dương. Thay đổi ròng là tổng mọi thay đổi, gồm cả khoản trừ. Số dư hiện tại còn phụ thuộc số dư ban đầu và toàn bộ lịch sử có liên quan. Ba con số này trả lời ba câu hỏi khác nhau.

Bảng chung ledger có delta âm lẫn dương, nên nó rộng hơn bảng thưởng chỉ nhận số dương. Hãy lọc khoản cộng trước khi tổng hợp theo người:

SELECT student_id, SUM(delta) AS earned
FROM ledger
WHERE delta > 0
GROUP BY student_id
ORDER BY student_id;

An có earned = 20, vì khoản trừ -10 không được đưa vào tổng khoản cộng. Dung chỉ có delta = -5, nên không có nhóm sau khi lọc. Đây là kết quả đúng cho yêu cầu chỉ liệt kê người có khoản cộng. Nếu cần đủ mọi người với 0, bạn sẽ phải kết hợp phép nối giữ học viên đã học ở bài 15.

Khi đọc báo cáo, hãy ghi rõ nó đang tính điều nào. Cộng cả khoản âm sẽ trả thay đổi ròng, không còn là tổng được cộng. Lấy tổng lịch sử mẫu làm số dư cũng không đúng vì chưa có số dư mở đầu. Bài thiết kế kết thúc ở cấu trúc và truy vấn, chưa xây quy trình cập nhật ví thật.

Lỗi thường gặp

Tóm tắt

• Chọn khóa theo sự kiện mà một dòng cần ghi lại.
• Bảng phần thưởng cần kiểm người nhận, số xu, lý do và kiểu dữ liệu.
• Thử hai lần cùng người để phát hiện quy tắc duy nhất đặt nhầm chỗ.
• Khóa ngoại kiểm người tồn tại, không kiểm quyền tặng thưởng.
• Đặt tên báo cáo theo đúng đại lượng đang tính.

Kiểm tra nhanh

Đọc thêm

• SQLite: tạo bảng và ràng buộc.
• SQLite: khóa ngoại bảo vệ liên kết.
• SQLite: chuyển đổi kiểu trong bảng STRICT.