Thiết kế cơ sở dữ liệu quan hệ (RDD) mô hình hóa thông tin và dữ liệu thành một tập hợp các bảng với hàng và cột. Mỗi hàng của một quan hệ/bảng đại diện cho một bản ghi, và mỗi cột đại diện cho một thuộc tính dữ liệu. Ngôn ngữ Truy vấn Cấu trúc (SQL) được sử dụng để thao tác cơ sở dữ liệu quan hệ. Thiết kế cơ sở dữ liệu quan hệ bao gồm bốn giai đoạn, nơi dữ liệu được mô hình hóa thành một tập hợp các bảng liên quan. Các giai đoạn là:
- Định nghĩa quan hệ/thuộc tính
- Định nghĩa khóa chính
- Định nghĩa quan hệ
- Chuẩn hóa (Normalization)
Cơ sở dữ liệu quan hệ khác với các cơ sở dữ liệu khác về cách tổ chức dữ liệu và thực hiện giao dịch. Trong RDD, dữ liệu được tổ chức thành bảng và mọi loại truy cập dữ liệu đều được thực hiện qua các giao dịch được kiểm soát. Thiết kế cơ sở dữ liệu quan hệ thỏa mãn các thuộc tính ACID (tính nguyên tử, tính nhất quán, tính toàn vẹn, tính bền vững) yêu cầu từ thiết kế cơ sở dữ liệu. Thiết kế RDBMS bắt buộc sử dụng máy chủ cơ sở dữ liệu trong ứng dụng để giải quyết các vấn đề quản lý dữ liệu.
Quy trình thiết kế cơ sở dữ liệu quan hệ
Thiết kế cơ sở dữ liệu là nghệ thuật hơn là khoa học, vì bạn phải đưa ra nhiều quyết định. Cơ sở dữ liệu thường được tùy chỉnh cho một ứng dụng cụ thể. Không có hai ứng dụng tùy chỉnh giống hệt nhau, do đó không có hai cơ sở dữ liệu giống nhau. Các hướng dẫn (thường là những gì không nên làm thay vì những gì nên làm) được cung cấp để hỗ trợ quyết định thiết kế, nhưng lựa chọn cuối cùng thuộc về người thiết kế.
Bước 1 - Xác định mục đích cơ sở dữ liệu (Phân tích yêu cầu)
- Thu thập yêu cầu và xác định mục tiêu cơ sở dữ liệu.
- Vẽ sơ đồ các form nhập liệu mẫu, truy vấn và báo cáo thường rất hữu ích.
Bước 2 - Thu thập dữ liệu, tổ chức vào bảng và chỉ định khóa chính
- Sau khi xác định mục đích, thu thập dữ liệu cần lưu trữ. Chia dữ liệu thành các bảng theo chủ đề.
- Chọn một cột (hoặc vài cột) làm khóa chính (primary key) để xác định duy nhất mỗi hàng.
Bước 3 - Tạo quan hệ giữa các bảng
Một cơ sở dữ liệu gồm các bảng độc lập, không liên quan nhau phục vụ ít mục đích (có thể dùng bảng tính thay thế). Sức mạnh của cơ sở dữ liệu quan hệ nằm ở mối quan hệ có thể định nghĩa giữa các bảng. Khía cạnh quan trọng nhất trong thiết kế RDBMS là xác định quan hệ giữa các bảng. Các loại quan hệ bao gồm:
- một-nhiều (one-to-many)
- nhiều-nhiều (many-to-many)
- một-một (one-to-one)
Một-nhiều (One-to-Many)
Trong cơ sở dữ liệu "danh sách lớp", một giáo viên có thể dạy không hoặc nhiều lớp, trong khi một lớp chỉ được dạy bởi một (và chỉ một) giáo viên. Trong cơ sở dữ liệu "công ty", một quản lý quản lý không hoặc nhiều nhân viên, trong khi một nhân viên được quản lý bởi một (và chỉ một) quản lý. Trong cơ sở dữ liệu "bán hàng sản phẩm", một khách hàng có thể đặt nhiều đơn hàng; trong khi một đơn hàng được đặt bởi một khách hàng cụ thể. Loại quan hệ này gọi là một-nhiều.
Quan hệ một-nhiều không thể biểu diễn trong một bảng đơn. Ví dụ, trong cơ sở dữ liệu "danh sách lớp", chúng ta có thể bắt đầu với bảng Teachers lưu thông tin giáo viên (tên, phòng, điện thoại, email). Để lưu các lớp mà mỗi giáo viên dạy, ta có thể tạo cột class1, class2, class3, nhưng ngay lập tức gặp vấn đề: tạo bao nhiêu cột? Ngược lại, nếu bắt đầu với bảng Classes lưu thông tin lớp, ta có thể thêm cột lưu thông tin giáo viên (tên, phòng, điện thoại, email). Tuy nhiên, vì một giáo viên dạy nhiều lớp, dữ liệu của giáo viên sẽ bị lặp lại ở nhiều hàng trong bảng Classes.
Để hỗ trợ quan hệ một-nhiều, cần thiết kế hai bảng: ví dụ bảng Classes lưu thông tin lớp với classID là khóa chính; và bảng Teachers lưu thông tin giáo viên với teacherID là khóa chính. Sau đó tạo quan hệ một-nhiều bằng cách lưu khóa chính của bảng Teacher (teacherID) (đầu "một" hoặc bảng cha) vào bảng Classes (đầu "nhiều" hoặc bảng con).
Cột teacherID trong bảng con Classes gọi là khóa ngoại (foreign key). Khóa ngoại của bảng con là khóa chính của bảng cha, dùng để tham chiếu bảng cha.
Nhiều-nhiều (Many-to-Many)
Trong cơ sở dữ liệu "bán hàng sản phẩm", đơn hàng của khách hàng có thể chứa một hoặc nhiều sản phẩm; và một sản phẩm có thể xuất hiện trong nhiều đơn hàng. Trong cơ sở dữ liệu "nhà sách", một sách được viết bởi một hoặc nhiều tác giả; trong khi một tác giả có thể viết không hoặc nhiều sách. Loại quan hệ này gọi là nhiều-nhiều.
Minh họa với cơ sở dữ liệu "bán hàng sản phẩm". Bắt đầu với hai bảng: Products và Orders. Bảng products chứa thông tin sản phẩm (tên, mô tả, quantityInStock) với productID là khóa chính. Bảng orders chứa đơn hàng khách hàng (customerID, dateOrdered, dateRequired, status). Lại không thể lưu mặt hàng đặt trong bảng Orders vì không biết cần bao nhiêu cột. Cũng không thể lưu thông tin đơn hàng trong bảng Products.
Để hỗ trợ quan hệ nhiều-nhiều, cần tạo bảng thứ ba (bảng nối - junction table), ví dụ OrderDetails (hoặc OrderLines), nơi mỗi hàng đại diện cho một mặt hàng của một đơn hàng cụ thể. Đối với bảng OrderDetails, khóa chính gồm hai cột: orderID và productID, xác định duy nhất mỗi hàng. Các cột orderID và productID trong OrderDetails dùng để tham chiếu Orders và Products, do đó chúng cũng là khóa ngoại trong OrderDetails.
Quan hệ nhiều-nhiều thực chất được triển khai thành hai quan hệ một-nhiều, với sự xuất hiện của bảng nối.
Một đơn hàng có nhiều mục trong OrderDetails. Một mục OrderDetails thuộc về một đơn hàng cụ thể.
Một sản phẩm có thể xuất hiện trong nhiều OrderDetails. Mỗi mục OrderDetails chỉ định một sản phẩm.
Một-một (One-to-One)
Trong cơ sở dữ liệu "bán hàng sản phẩm", một sản phẩm có thể có thông tin bổ sung tùy chọn như hình ảnh, mô tả chi tiết, bình luận. Giữ chúng trong bảng Products dẫn đến nhiều ô trống (ở các bản ghi không có dữ liệu tùy chọn). Hơn nữa, dữ liệu lớn này có thể làm giảm hiệu suất cơ sở dữ liệu.
Thay vào đó, ta có thể tạo bảng khác (ví dụ ProductDetails, ProductLines hoặc ProductExtras) để lưu dữ liệu tùy chọn. Bản ghi chỉ được tạo cho những sản phẩm có dữ liệu tùy chọn. Hai bảng Products và ProductDetails thể hiện quan hệ một-một. Đó là, với mỗi hàng trong bảng cha, có tối đa một hàng (có thể không) trong bảng con. Cùng cột productID nên dùng làm khóa chính cho cả hai bảng.
Một số cơ sở dữ liệu giới hạn số cột trong bảng. Bạn có thể dùng quan hệ một-một để tách dữ liệu thành hai bảng. Quan hệ một-một cũng hữu ích để lưu dữ liệu nhạy cảm trong bảng an toàn, dữ liệu không nhạy cảm trong bảng chính.
Kiểu dữ liệu cột (Column Data Types)
Bạn cần chọn kiểu dữ liệu phù hợp cho mỗi cột. Các kiểu phổ biến: số nguyên, số thực, chuỗi (văn bản), ngày/giờ, nhị phân, tập hợp (liệt kê, tập hợp).
Bước 4 - Hoàn thiện & Chuẩn hóa thiết kế
Ví dụ:
- Thêm cột,
- Tạo bảng mới cho dữ liệu tùy chọn dùng quan hệ một-một,
- Tách bảng lớn thành hai bảng nhỏ hơn,
- Các phương pháp khác.
Chuẩn hóa (Normalization)
Áp dụng các quy tắc chuẩn hóa để kiểm tra xem cơ sở dữ liệu có đúng cấu trúc và tối ưu không.
Dạng chuẩn thứ nhất (1NF): Một bảng là 1NF nếu mọi ô chứa một giá trị đơn lẻ, không phải danh sách giá trị. Tính chất này gọi là nguyên tử (atomic). 1NF cũng cấm nhóm cột lặp lại như item1, item2, itemN. Thay vào đó, hãy tạo bảng khác dùng quan hệ một-nhiều.
Dạng chuẩn thứ hai (2NF): Một bảng là 2NF nếu nó là 1NF và mọi cột không phải khóa phụ thuộc hoàn toàn vào khóa chính. Hơn nữa, nếu khóa chính gồm nhiều cột, mọi cột không phải khóa phải phụ thuộc vào toàn bộ tập chứ không phải một phần.
Ví dụ: khóa chính của bảng OrderDetails gồm orderID và productID. Nếu unitPrice chỉ phụ thuộc vào productID, nó không nên nằm trong OrderDetails (mà ở bảng Products). Ngược lại, nếu giá đơn vị phụ thuộc vào cả sản phẩm và đơn hàng cụ thể, thì nó nên ở OrderDetails.
Dạng chuẩn thứ ba (3NF): Một bảng là 3NF nếu nó là 2NF và các cột không phải khóa độc lập với nhau. Nói cách khác, cột không khóa chỉ phụ thuộc vào khóa chính, chỉ khóa chính và không có gì khác. Ví dụ: bảng Products có cột productID (khóa chính), name, unitPrice. Cột discountRate không nên thuộc Products nếu nó cũng phụ thuộc vào unitPrice (không phải khóa chính).
Dạng chuẩn cao hơn: 3NF có những bất cập, dẫn đến dạng chuẩn cao hơn như Boyce/Codd Normal Form, Dạng chuẩn thứ tư (4NF) và Dạng chuẩn thứ năm (5NF), vượt ra ngoài phạm vi hướng dẫn này.
Đôi khi bạn có thể quyết định vi phạm một số quy tắc chuẩn hóa vì lý do hiệu năng (ví dụ: tạo cột totalPrice trong bảng Orders có thể dẫn xuất từ orderDetails); hoặc vì người dùng yêu cầu. Hãy chắc chắn bạn hiểu rõ, phát triển logic xử lý và ghi chép quyết định.
Quy tắc toàn vẹn (Integrity Rules)
Bạn cũng nên áp dụng quy tắc toàn vẹn để kiểm tra tính toàn vẹn thiết kế:
1. Quy tắc toàn vẹn thực thể (Entity Integrity Rule): Khóa chính không được chứa NULL. Nếu không, nó không thể xác định duy nhất hàng. Với khóa tổ hợp gồm nhiều cột, không cột nào được chứa NULL. Hầu hết RDBMS kiểm tra và thực thi quy tắc này.
2. Quy tắc toàn vẹn tham chiếu (Referential Integrity Rule): Mỗi giá trị khóa ngoại phải khớp với một giá trị khóa chính trong bảng được tham chiếu (bảng cha).
Bạn chỉ có thể chèn hàng với khóa ngoại trong bảng con nếu giá trị tồn tại trong bảng cha.
Nếu giá trị khóa thay đổi trong bảng cha (ví dụ: hàng được cập nhật hoặc xóa), tất cả hàng với khóa ngoại đó trong bảng con phải được xử lý tương ứng. Bạn có thể (a) cấm thay đổi; (b) lan truyền thay đổi (hoặc xóa bản ghi) trong bảng con; (c) đặt giá trị khóa trong bảng con thành NULL.
Hầu hết RDBMS có thể thiết lập để thực hiện kiểm tra và đảm bảo toàn vẹn tham chiếu theo cách chỉ định.
3. Toàn vẹn logic nghiệp vụ (Business logic Integrity): Ngoài hai quy tắc chung trên, có thể có quy tắc toàn vẹn (xác thực) liên quan đến logic nghiệp vụ, ví dụ: mã bưu chính phải 5 chữ số trong khoảng nhất định, ngày giao hàng phải nằm trong giờ làm việc; số lượng đặt hàng phải bằng hoặc nhỏ hơn số lượng tồn kho, v.v. Chúng có thể thực hiện qua quy tắc xác thực (cho cột cụ thể) hoặc logic lập trình.
Chỉ mục cột (Column Indexing)
Bạn có thể tạo chỉ mục trên cột được chọn để hỗ trợ tìm kiếm và truy xuất dữ liệu. Chỉ mục là tệp có cấu trúc giúp tăng tốc truy cập dữ liệu cho SELECT nhưng có thể làm chậm INSERT, UPDATE, DELETE. Không có cấu trúc chỉ mục, để xử lý truy vấn SELECT với tiêu chí khớp (ví dụ: SELECT * FROM Customers WHERE name='Tan Ah Teck'), công cụ cơ sở dữ liệu cần so sánh mọi bản ghi trong bảng. Chỉ mục chuyên biệt (ví dụ cấu trúc BTREE) có thể truy cập bản ghi mà không cần so sánh mọi bản ghi. Tuy nhiên, chỉ mục cần được xây dựng lại mỗi khi bản ghi thay đổi, gây chi phí overhead.
Chỉ mục có thể định nghĩa trên một cột, tập hợp cột (chỉ mục nối - concatenated index), hoặc một phần cột (ví dụ: 10 ký tự đầu của VARCHAR(100)) (chỉ mục một phần - partial index). Bạn có thể xây dựng nhiều chỉ mục trong một bảng. Ví dụ, nếu thường tìm kiếm khách hàng bằng tên hoặc số điện thoại, bạn có thể tăng tốc bằng cách xây chỉ mục trên cột customerName và phoneNumber. Hầu hết RDBMS tự động xây chỉ mục trên khóa chính.