Quay lại tất cả hướng dẫn
Thiết kế cơ sở dữ liệu··Đọc trong 6 phút

Quan hệ một–nhiều và nhiều–nhiều: các mẫu quan hệ SQL

Tìm hiểu mối quan hệ một-nhiều so với nhiều-nhiều trong SQL. Xem cách triển khai từng loại với khóa ngoại, bảng trung gian và các ví dụ thực tế.

Khóa ngoại từ khách hàng đến đơn hàng và bảng trung gian từ sinh viên đến khóa học với một khóa chính phức hợp
Trong hướng dẫn này
  1. Tìm hiểu về các mẫu quan hệ SQL
  2. Mối quan hệ một-nhiều
  3. Các mối quan hệ nhiều-nhiều
  4. Mối quan hệ một-một
  5. Cách các mối quan hệ xuất hiện trong ERD
  6. Chọn Mẫu mối quan hệ phù hợp
  7. Tài liệu tham khảo và đọc thêm

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ơ đồ.

SQL khóa ngoại được ánh xạ tới mối quan hệ ERD giữa khách hàng và đơn hàng
Khóa ngoại không chỉ là cú pháp: trong ERD, nó trở thành đường mối quan hệ giải thích cách các bảng phụ thuộc vào nhau.

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ái employees bảng trong trường hợp này.
  • ON DELETE RESTRICT ngă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_id có 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ư grade hoặc enrolled_at, thuộc về không thực thể nào một mình.
  • ON DELETE CASCADE trê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 FootCá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.

Tài liệu tham khảo và đọc thêm

Các bài viết khác