Database table design

1. Smart table design

Chúng ta có 2 table như sau:

Hai table trên có một số cột giống nhau, một số cột mà table kia không có, và có cột được tách ra như với location và state.

Theo bạn nghĩ thì table nào được thiết kế tốt hơn?

Câu trả lời là không có table nào cả. Cách bạn thiết kế table hoàn toàn dựa vào mục đích sử dụng của bạn. Giả sử table fish_info được dùng trong công việc của một nhà thủy sinh học, còn table fish_records dành cho một nhà buôn thủy sản. Vì nhà buôn thủy sản cần biết thông tin của người bắt được loài cá đó còn nhà thủy sinh học thì không. Vì nhà buôn thủy sản nhiều lúc cần search thông tin của state nơi bắt được loài cá đó, nên cần phải tách cột đó ra để query được nhanh hơn…

first_name, last_name ở table fish_records được tách ra vậy cột date có cần tách ra các cột day, month, year không? Điều đó còn tùy, nếu nhà buôn cảm thấy thông tin được chia nhỏ đến mức như vậy là đủ rồi thì không cần thiết nữa. Dữ liệu lúc đó là đã atomic - nghĩa là nó không thể hoặc không nên chia nhỏ nữa.

Dữ liệu atomic có 2 nguyên tắc cơ bản:

1. Một cột chứa dữ liệu atomic không thể có các giá trị cùng kiểu

1. Một bảng có dữ liệu atomic không thể có nhiều cột với cùng kiểu dữ liệu

Ở đây nên có một lưu ý các cột học sinh lặp lại không phải thuộc tính của giáo viên, mà là các đối tượng khác nhau.

Một ví dụ khác, chúng ta có bảng toys:

Các color ở đây là loại màu của đồ chơi, wiffleball có 3 màu khác nhau, tương tự yoyo chỉ có 2 loại màu, chứ không có nghĩa là 1 quả wiffleball có cả 3 màu trên nó.

Mình đã gặp một table như sau:

Thì color1, color2 và color3 ở đây là thuộc tính của tag nên thiết kế table như vậy không hề sai, vấn đề ở cách đặt tên, đúng hơn có thể là background_color, foreground_color và border_color.

2. Normal forms

Thiết kế database sao cho normal nghĩa là phải tuân theo những nguyên tắc chuẩn, và ai cũng có thể hiểu được.

i. First normal form (1NF)

1. Các cột chỉ chứa các giá trị atomic
2. Dữ liệu cùng kiểu không được lặp lại ở nhiều cột

1NF thực thi các tiêu chí sau:

• Không gặp lại các nhóm dữ liệu trong bảng.
• Tạo các bảng riêng lẻ cho các dữ liệu liên quan.
• Mỗi bảng ghi của dữ liệu phải có một khóa chính.

Chúng ta có thể thiết kế lại bảng classes như sau, với điều kiện mỗi giáo viên chỉ dạy một lớp và mỗi học sinh chỉ được tham gia một lớp.

Functional dependency

Ở bảng students trên, chúng ta có thể nhận ra nếu year_of_birth của học sinh đó thay đổi thì cột age cũng sẽ phải thay đổi theo. Vì vậy ta gọi age phụ thuộc hàm vào year_of_birth.

students.year_of_birth -> students.age

Partial functional dependency

Để hiểu được phụ thuộc hàm một phần, chúng ta phải hiểu được composite key (khóa tổng hợp).

Ở bảng classes, nếu ta không cần cột id làm khóa chính, thì sự kết hợp duy nhất (unique) của 2 cột còn lại là subject và teacher_id tạo thành khóa tổng hợp. Nghĩa là cứ mỗi giáo viên và một môn học, chúng ta sẽ có một lớp.

Với bảng trên, ta thấy teacher_address (non-key column) phụ thuộc vào teacher_id - một trong cột tạo nên khóa tổng hợp (key column) vì nếu teacher_id thay đổi buộc address phải thay đổi theo, do vậy ta nói teacher_address phụ thuộc hàm một phần vào teacher_id.

Một bảng có phụ thuộc hàm một phần khi có cột không phải thuộc khóa tổng hợp (non-key column) phụ thuộc vào một hoặc nhiều nhưng không phải tất cả các cột tạo nên khóa tổng hợp.

Hãy xem thêm một ví dụ nữa.

Ở bảng superheroes, 2 cột name và power tạo thành khóa tổng hợp, mỗi siêu anh hùng đều có tên và siêu năng lực của riêng mình. Tuy nhiên cột initials có thể nói là viết tắt của tên của siêu anh hùng đó. Và nếu cột name (key column) thay đổi, initials cũng phải thay đổi theo, ta gọi đó là phụ thuộc hàm một phần.

Transitive functional dependency

Một bảng có phụ thuộc hàm bắc cầu nếu một cột không phải thuộc khóa tổng hợp thay đổi dẫn đến các cột cũng không phải thuộc khóa tổng hợp bất kì trong bảng thay đổi.

Phụ thuộc hàm của bảng students ở ví dụ trên được gọi là phụ thuộc hàm bắc cầu.

ii. Second normal form (2NF)

1. Bảng đã ở 1NF
2. Bảng không có phụ thuộc hàm một phần

Chúng ta có phiên bản mở rộng của bảng toys dưới đây:

store_address phụ thuộc vào store_id nên chúng ta sẽ đưa nó sang bảng stores riêng biệt.

toy_id, store_id, color tạo thành khóa tổng hợp.

Nếu chúng ta thêm vào 1 loại đồ chơi ở 1 cửa hàng, chúng ta phải cập nhật cùng lúc 2 bảng toys và inventories.

Nếu các loại đồ chơi được định nghĩa sẵn, hay còn gọi là toy_types. Như vậy, nếu ta thêm 1 loại đồ chơi mới vào bảng toys, thì color tương tự cũng phải được định nghĩa sẵn chứ không thể đợi đến lúc cập nhật vào bảng inventories chúng ta mới có thể query để biết được loại đồ chơi này có những màu nào.

Và rõ ràng color là một thuộc tính thuộc về toys thay vì của inventories. Điều này dẫn đến vi phạm 1NF.

iii. Third normal form (3NF)

1. Bảng đã ở 2NF
2. Bảng không có phụ thuộc hàm bắc cầu

Bảng dưới đây thỏa mãn 2NF nhưng vi phạm 3NF:

Khóa tổng hợp ở đây từ tournamemt và year. Tuy nhiên bảng này vi phạm 3NF vì date_of_birth (non-key column) phụ thuộc vào winner (non-key column).

Tham khảo

1. Head first SQL by Lynn Beighley
2. https://en.wikipedia.org/wiki/First_normal_form