Các ví dụ SQL sử dụng PostgreSQL trừ khi một phương ngữ khác được chỉ định. Các đoạn mã ngắn giả định rằng các bảng được tham chiếu đã tồn tại. Hãy xác thực DDL trong cơ sở dữ liệu mục tiêu của bạn trước khi áp dụng nó.
Tìm hiểu về các mẫu quan hệ SQL
Các cơ sở dữ liệu quan hệ có được sức mạnh từ các kết nối giữa các bảng. Cho dù bạn đang thiết kế một ứng dụng nhỏ hay một kho dữ liệu doanh nghiệp, việc chọn đúng mô hình quan hệ sẽ quyết định mức độ hiệu quả mà bạn có thể lưu trữ, truy vấn và duy trì dữ liệu của mình. Ba mô hình cơ bản—một-một, một-nhiều, và nhiều-nhiều— bao gồm nhiều mối liên kết phổ biến; các mối quan hệ đệ quy và bậc cao hơn cần mô hình hóa bổ sung.
Trong hướng dẫn này, chúng tôi phân tích từng mẫu với các ví dụ về PostgreSQL, khám phá vai trò của các bảng trung gian (junction tables) và chỉ ra cách các mối quan hệ này xuất hiện trên Sơ đồ thực thể-mối quan hệ (ERD). Nếu bạn muốn trực quan hóa lược đồ của riêng mình ngay bây giờ, hãy mở Trình chỉnh sửa Erd Go và dán các nội dung được hỗ trợ CREATE TABLE các định nghĩa để tạo sơ đồ.
Mối quan hệ một-nhiều
A một-nhiều Mối quan hệ (1:N) là mô hình phổ biến nhất trong thiết kế cơ sở dữ liệu. Nó có nghĩa là một hàng đơn lẻ trong cha bảng có thể được tham chiếu bởi nhiều hàng trong con bảng và một khóa ngoại con không null trỏ đến một cha. Một khóa ngoại có thể để trống (nullable) cho phép một con không cần cha.
Ví dụ cổ điển: Phòng ban và Nhân viên
Một công ty có nhiều phòng ban, và mỗi phòng ban chứa nhiều nhân viên. Tuy nhiên, mỗi nhân viên thuộc về chính xác một phòng ban tại bất kỳ thời điểm nào.
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
location VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
department_id INT NOT NULL,
hire_date DATE DEFAULT CURRENT_DATE,
CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments (id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);Chi tiết triển khai chính
- Khóa ngoại (
department_id) luôn nằm trên nhiều phía—cáiemployeesbảng trong trường hợp này. ON DELETE RESTRICTngăn chặn việc vô tình xóa một phòng ban vẫn còn nhân viên được chỉ định.ON UPDATE CASCADEđảm bảo rằng nếu khóa chính của một phòng ban thay đổi, tất cả các hàng nhân viên sẽ tự động cập nhật.- Đang thêm một chỉ mục trên
department_idcó thể giúp ích cho các phép nối và bộ lọc phòng ban; xác nhận lợi ích bằng dữ liệu đại diện và kế hoạch thực thi.
Truy vấn dữ liệu Một-Nhiều
-- All employees in the Engineering department
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';
-- Count employees per department
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY headcount DESC;Các ví dụ khác về mối quan hệ một-nhiều trong đời sống bao gồm tác giả → bài viết blog, khách hàng → đơn hàng, và danh mục → sản phẩm.
Các mối quan hệ nhiều-nhiều
A nhiều-nhiều Mối quan hệ (M:N) tồn tại khi các hàng trên cả hai các phía có thể được liên kết với nhiều hàng ở phía bên kia. Cơ sở dữ liệu quan hệ không thể biểu diễn trực tiếp điều này bằng một khóa ngoại duy nhất, vì vậy chúng tôi giới thiệu một bảng trung gian (còn được gọi là cầu nối, kết nối (join), hoặc kết hợp bảng).
Ví dụ cổ điển: Sinh viên và Khóa học
Một sinh viên có thể đăng ký nhiều khóa học, và mỗi khóa học có thể có nhiều sinh viên. Bảng trung gian enrollments nằm giữa chúng và giữ các khóa ngoại cho cả hai phía.
CREATE TABLE students (
id SERIAL PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
enrolled_on DATE DEFAULT CURRENT_DATE
);
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
credits SMALLINT NOT NULL DEFAULT 3,
department VARCHAR(100)
);
CREATE TABLE enrollments (
student_id INT NOT NULL REFERENCES students (id) ON DELETE CASCADE,
course_id INT NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
grade CHAR(2),
PRIMARY KEY (student_id, course_id)
);Tại sao cần bảng trung gian (Junction Table)?
- Các khóa chính tổng hợp (
student_id, course_id) cho phép một hàng cho mỗi cặp sinh viên-khóa học. Nếu cần đăng ký lặp lại qua các học kỳ, hãy bao gồm một học kỳ trong khóa nghiệp vụ hoặc mô hình hóa từng đăng ký riêng biệt. - Bạn có thể đính kèm các thuộc tính bổ sung vào chính mối quan hệ đó, chẳng hạn như
gradehoặcenrolled_at, thuộc về không thực thể nào một mình. ON DELETE CASCADEtrên cả hai khóa ngoại có nghĩa là việc xóa một sinh viên hoặc khóa học sẽ tự động dọn dẹp các hàng đăng ký.
Truy vấn dữ liệu Nhiều-Nhiều
-- All courses for a specific student
SELECT c.title, c.credits, e.grade
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 42;
-- Students enrolled in 'Database Systems'
SELECT s.full_name, s.email
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE c.title = 'Database Systems';Các ví dụ thực tế về nhiều-nhiều bao gồm người dùng ↔ vai trò, sản phẩm ↔ thẻ, diễn viên ↔ phim, và đơn hàng ↔ sản phẩm (thông qua một order_items bảng trung gian cũng lưu trữ số lượng và giá).
Mối quan hệ một-một
A một-một Mối quan hệ (1:1) có nghĩa là mỗi hàng trong Bảng A ánh xạ tới tối đa một hàng trong Bảng B, và ngược lại. Mô hình này ít phổ biến hơn nhưng hữu ích cho việc tách các cột ít được truy cập hoặc nhạy cảm vào một bảng riêng biệt.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(60) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE user_profiles (
user_id INT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
bio TEXT,
avatar_url VARCHAR(500),
birthdate DATE,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Lưu ý rằng user_profiles.user_id vừa là khóa chính và khóa ngoại. Ràng buộc này thực thi ánh xạ một-một ở cấp cơ sở dữ liệu—mỗi người dùng có thể có tối đa một hàng hồ sơ.
Cách các mối quan hệ xuất hiện trong ERD
Sơ đồ ERD sử dụng các đường kẻ và ký hiệu giữa các bảng để truyền đạt lực lượng (cardinality) trong nháy mắt. Hai ký hiệu phổ biến nhất là Crow’s Foot và Chen ký hiệu.
| Quan hệ | Biểu tượng Crow’s Foot | Cách đọc |
|---|---|---|
| Một-một | ||—|| | Chính xác một ở mỗi bên |
| Một-nhiều | ||—< | Một ở bên trái, nhiều ở bên phải (đầu chân quạ) |
| Nhiều-nhiều | >—< | Nhiều-nhiều ở cả hai phía; được triển khai thông qua bảng trung gian |
Các ký hiệu văn bản này là viết tắt cho số lượng tối đa. Tính tùy chọn cần thêm một dấu không hoặc một; một khóa ngoại đơn thuần không yêu cầu cha phải có bất kỳ con nào.
Khi bạn mô hình hóa mối quan hệ nhiều-nhiều trong một công cụ ERD, bạn thường thấy ba hộp—hai bảng thực thể và bảng trung gian giữa chúng, mỗi hộp được kết nối bằng một đường một-nhiều.
Dán bất kỳ ví dụ SQL nào từ bài viết này vào Trình chỉnh sửa Erd Go để xem sơ đồ được tạo tự động, hoàn chỉnh với các mũi tên khóa ngoại và các chỉ báo bản số (cardinality).
Chọn Mẫu mối quan hệ phù hợp
Việc chọn sai bản số (cardinality) dẫn đến trùng lặp dữ liệu, các truy vấn phức tạp và lỗi toàn vẹn. Hãy sử dụng khung quyết định này:
- Một hàng ở phía A có thể liên quan đến nhiều hàng ở phía B, nhưng không ngược lại không? → Một-Nhiều. Đặt khóa ngoại ở phía B.
- Các hàng ở cả hai phía có thể liên quan đến nhiều hàng ở phía kia không? → Nhiều-Nhiều. Tạo một bảng trung gian với khóa phức hợp.
- Ánh xạ có phải là 1:1 nghiêm ngặt và bạn có cần tách bảng không? → Một-Một. Sử dụng cùng một khóa chính làm khóa ngoại.
Khi nghi ngờ, hãy phác thảo lược đồ của bạn trước. Một ERD nhanh sẽ tiết lộ các vấn đề về mối quan hệ sớm hơn nhiều so với việc gặp lỗi ràng buộc trong môi trường sản xuất. Hãy thử xây dựng sơ đồ của bạn trong Trình chỉnh sửa Erd Go— nó chỉ mất vài giây và có thể tiết kiệm hàng giờ gỡ lỗi.



