PHP và MySQL Bài 21: Thiết kế Cơ sở dữ liệu chuẩn - Khóa chính (Primary Key), Khóa ngoại (Foreign Key) và Các mối quan hệ

Khi mới học SQL, chúng ta thường có xu hướng "nhét" tất cả mọi thông tin vào một bảng duy nhất (như một file Excel khổng lồ). Ví dụ: một bảng chứa thông tin khách hàng, số dư thẻ, vị trí trạm kiểm soát, mã số máy bán vé tự động (TVM), v.v.

Cách làm này dẫn đến sự trùng lặp dữ liệu khủng khiếp. Khi một trạm đổi tên, bạn sẽ phải dùng lệnh UPDATE hàng chục ngàn dòng giao dịch có chứa tên trạm đó. Giải pháp cho vấn đề này là tách nhỏ dữ liệu ra thành nhiều bảng độc lập và dùng "Khóa" để liên kết chúng lại với nhau.

1. Khóa chính (Primary Key - PK) là gì?

Khóa chính là một cột (hoặc một nhóm cột) dùng để nhận diện độc nhất một bản ghi trong một bảng. Không bao giờ có hai dòng trong cùng một bảng có chung một Khóa chính, và Khóa chính không bao giờ được phép rỗng (NULL).

Thông thường, người ta sử dụng một cột số nguyên tự động tăng (AUTO_INCREMENT) hoặc một chuỗi định danh duy nhất (UUID) làm Khóa chính.

Ví dụ: Bảng stations (Các trạm metro)

CREATE TABLE stations (
    id INT AUTO_INCREMENT PRIMARY KEY, -- Đây là Khóa chính
    station_code VARCHAR(10) UNIQUE NOT NULL,
    station_name VARCHAR(100) NOT NULL
);

2. Khóa ngoại (Foreign Key - FK) là gì?

Khóa ngoại là một cột trong bảng này, nhưng lại trỏ (liên kết) trực tiếp đến Khóa chính của một bảng khác.

Khóa ngoại đóng vai trò như một "cây cầu" nối hai bảng lại với nhau, đảm bảo Tính toàn vẹn dữ liệu. Khi bạn khai báo Khóa ngoại, MySQL sẽ không cho phép bạn chèn một dữ liệu "ma" (một ID trạm không tồn tại) vào bảng chứa thiết bị.

Ví dụ: Bảng tvm_machines (Máy bán vé tự động) được đặt tại các trạm.

CREATE TABLE tvm_machines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    machine_code VARCHAR(20) NOT NULL,
    status TINYINT(1) DEFAULT 1,
    station_id INT, -- Cột này sẽ dùng làm Khóa ngoại
    
    -- Khai báo station_id liên kết với cột id của bảng stations
    FOREIGN KEY (station_id) REFERENCES stations(id)
);

3. Các mối quan hệ (Relationships) trong Cơ sở dữ liệu

Khi đã có Khóa chính và Khóa ngoại, các bảng sẽ tương tác với nhau theo 3 mô hình quan hệ cơ bản sau:

3.1. Quan hệ Một - Một (1 - 1)

• Định nghĩa: Một bản ghi ở Bảng A chỉ liên kết với duy nhất một bản ghi ở Bảng B, và ngược lại.
• Khi nào sử dụng: Thường dùng để tách một bảng quá lớn thành hai bảng nhỏ hơn nhằm tối ưu tốc độ đọc, hoặc để bảo vệ các dữ liệu cực kỳ nhạy cảm.
• Ví dụ thực tế:
  • Bảng users chứa thông tin đăng nhập (username, password).
  • Bảng user_profiles chứa thông tin cá nhân (ảnh đại diện, địa chỉ nhà, căn cước công dân).
  • Một người dùng chỉ có một bộ hồ sơ. Khóa ngoại user_id ở bảng user_profiles cũng đồng thời là Khóa chính của bảng đó (đảm bảo tính độc nhất).

3.2. Quan hệ Một - Nhiều (1 - N)

• Định nghĩa: Một bản ghi ở Bảng A có thể liên kết với nhiều bản ghi ở Bảng B. Nhưng một bản ghi ở Bảng B chỉ thuộc về duy nhất một bản ghi ở Bảng A.
• Đặc điểm: Đây là loại quan hệ phổ biến nhất, chiếm 80% cấu trúc của mọi hệ thống Backend.
• Cách thiết lập: Đặt Khóa ngoại ở bảng có chữ "Nhiều".
• Ví dụ thực tế (Trạm và Máy bán vé):
  • Một Trạm có thể lắp đặt nhiều Máy bán vé.
  • Một Máy bán vé chỉ được đặt tại một Trạm cố định.
  • Thiết kế: Bảng tvm_machines (Nhiều) sẽ chứa cột khóa ngoại station_id trỏ về bảng stations (Một) như đoạn code SQL ở phần 2.

3.3. Quan hệ Nhiều - Nhiều (N - N)

• Định nghĩa: Một bản ghi ở Bảng A liên kết với nhiều bản ghi ở Bảng B, và ngược lại, một bản ghi ở Bảng B cũng liên kết với nhiều bản ghi ở Bảng A.
• Cách giải quyết: MySQL (hay bất kỳ hệ quản trị CSDL quan hệ nào) không thể liên kết trực tiếp kiểu N-N. Chúng ta bắt buộc phải tạo ra một Bảng trung gian (Pivot Table) ở giữa. Bảng trung gian này sẽ chứa 2 Khóa ngoại trỏ về 2 bảng gốc, tách quan hệ N-N thành hai quan hệ 1-N.
• Ví dụ thực tế (Vai trò và Quyền hạn):
  • Một vai trò (Ví dụ: AFC_Operator) có nhiều quyền hạn (view_revenue, reboot_tvm).
  • Một quyền hạn (Ví dụ: reboot_tvm) có thể được cấp cho nhiều vai trò khác nhau (Admin, AFC_Operator).
• Thiết kế Bảng Trung Gian:

  -- Bảng 1: roles
  CREATE TABLE roles (
      id INT AUTO_INCREMENT PRIMARY KEY,
      role_name VARCHAR(50)
  );
  
  -- Bảng 2: permissions
  CREATE TABLE permissions (
      id INT AUTO_INCREMENT PRIMARY KEY,
      permission_name VARCHAR(50)
  );
  
  -- Bảng trung gian: role_permission (Giải quyết N-N)
  CREATE TABLE role_permission (
      role_id INT,
      permission_id INT,
      
      -- Hai khóa ngoại trỏ về hai bảng gốc
      FOREIGN KEY (role_id) REFERENCES roles(id),
      FOREIGN KEY (permission_id) REFERENCES permissions(id),
      
      -- Đảm bảo không cấp một quyền cho cùng một role 2 lần
      PRIMARY KEY (role_id, permission_id) 
  );
  

Tổng kết Bài 21

Việc phân tách dữ liệu thành các bảng rời rạc giúp dữ liệu gọn gàng và không bị lặp. Tuy nhiên, khi hệ thống cần hiển thị báo cáo (ví dụ: cần in ra một bảng gồm Tên Máy bán vé và Tên Trạm chứa nó), làm sao chúng ta có thể "gộp" dữ liệu từ hai bảng này lại với nhau trong một câu truy vấn?

Giải pháp nằm ở thao tác mạnh mẽ và phức tạp bậc nhất của SQL. Chúng ta sẽ cùng chinh phục nó trong bài học tiếp theo: Bài 22: Thao tác JOIN bảng trong MySQL (INNER JOIN, LEFT JOIN, RIGHT JOIN).