Panduan Relasi Many to Many

Relasi many-to-many menghubungkan dua tabel di mana satu baris di sisi mana pun bisa cocok dengan banyak baris di sisi lainnya: seorang mahasiswa mengambil banyak mata kuliah, dan satu mata kuliah punya banyak mahasiswa. Anda memodelkannya dengan junction table yang menyimpan satu baris per tautan. Contoh-contohnya berjalan di PostgreSQL, MySQL, SQLite, dan SQL Server.

Cara memodelkan relasi many-to-many

Buat tabel ketiga, yaitu junction table (disebut juga bridge, association, atau join table), dengan foreign key ke masing-masing sisi. Setiap baris adalah satu tautan: mahasiswa ini mengambil mata kuliah ini.

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

-- The junction table: one row per student per course
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Untuk berpindah dari satu sisi ke sisi lain, lakukan join melalui junction table:

-- Input: students
-- id | name
-- ---+------
-- 1  | Ana
-- 2  | Ben
-- 3  | Chloe

-- Input: courses
-- id | course_name
-- ---+------------
-- 1  | Biology
-- 2  | Chemistry
-- 3  | Physics

-- Input: enrollments
-- student_id | course_id
-- -----------+----------
-- 1          | 1
-- 1          | 2
-- 2          | 1
-- 3          | 1

SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
ORDER BY s.name, c.course_name;

-- Output:
-- name  | course_name
-- ------+------------
-- Ana   | Biology
-- Ana   | Chemistry
-- Ben   | Biology
-- Chloe | Biology

Ana mengambil dua mata kuliah dan Biologi punya tiga mahasiswa, jadi relasinya bersifat “many” di kedua arah.

Aturan yang perlu diketahui

• Jadikan pasangan kolom sebagai primary key. PRIMARY KEY (student_id, course_id) mencegah tautan yang sama tersimpan dua kali:

-- Input: the enrollments table above

INSERT INTO enrollments VALUES (1, 1);
-- ERROR: duplicate key value violates unique constraint "enrollments_pkey"

• Beri index pada kolom kedua. Index milik primary key diawali dengan student_id, jadi sudah bisa menemukan mata kuliah seorang mahasiswa dengan cepat. Untuk menemukan mahasiswa di suatu mata kuliah dengan cepat, Anda butuh index yang diawali dengan course_id (MySQL otomatis membuatnya bersama foreign key):

CREATE INDEX idx_enrollments_course_id ON enrollments(course_id);

• Simpan satu baris per tautan, jangan pernah berupa daftar. Kolom course_ids yang berisi '1,2' tidak bisa di-join, diberi index, atau dicek oleh foreign key.
• SQLite mengabaikan foreign key sampai Anda mengaktifkannya. Jalankan PRAGMA foreign_keys = ON; di setiap koneksi, atau SQLite akan menerima enrollment untuk mahasiswa yang tidak ada.

Sintaks alternatif

Beberapa framework memberi setiap tabel kolom id sendiri, termasuk junction table. Itu tidak masalah selama Anda menambahkan constraint UNIQUE pada pasangan kolomnya, karena id saja tidak mencegah tautan duplikat. Hanya kolom id yang berbeda antar database:

-- PostgreSQL
CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    UNIQUE (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- MySQL:      id INT AUTO_INCREMENT PRIMARY KEY
-- SQLite:     id INTEGER PRIMARY KEY
-- SQL Server: id INT IDENTITY PRIMARY KEY

Anda juga bisa mendeklarasikan foreign key secara inline: student_id INTEGER NOT NULL REFERENCES students(id). MySQL 8.0 menerima bentuk ini tapi diam-diam mengabaikannya, jadi di sana tetap gunakan klausa FOREIGN KEY terpisah.

Kapan sebaiknya memakai relasi many-to-many?

Gunakan setiap kali baris di kedua sisi bisa punya banyak pasangan: mahasiswa dan mata kuliah, postingan dan tag, pengguna dan role, produk dan kategori. Jika setiap baris di satu sisi hanya pernah punya satu pasangan, relasi one-to-many sudah cukup. Setelah junction table tersedia, masalah-masalah berikut bisa diselesaikan.

Cari apa saja yang terhubung dengan sebuah baris

Mata kuliah apa saja yang diambil seorang mahasiswa? Tag apa saja yang ada di sebuah postingan? Lakukan join dari baris ke junction table lalu ke sisi lainnya. Junction table yang sama menjawab pertanyaan di kedua arah.

Hitung tautan di setiap sisi

Mata kuliah terpenuh, tag yang paling sering dipakai, anggota yang ikut klub terbanyak: kelompokkan berdasarkan satu sisi dan hitung barisnya di junction table. Gunakan LEFT JOIN jika baris tanpa tautan harus dihitung sebagai 0, bukan hilang.

Simpan detail tentang setiap tautan

Nilai milik seorang mahasiswa di suatu mata kuliah, bukan milik mahasiswa atau mata kuliah saja, dan bagian royalti milik seorang penulis pada buku tertentu. Taruh kolom-kolom ini di junction table dan query seperti kolom lainnya.

Hubungkan baris dalam tabel yang sama

Follower, teman, dan mentor menghubungkan orang dengan orang lain. Kedua foreign key di junction table menunjuk ke tabel yang sama, jadi Anda melakukan join ke tabel itu dua kali, dengan alias berbeda setiap kalinya.

Contoh nyata relasi many-to-many

Cari postingan yang memiliki semua tag dalam daftar

Filter tag biasanya berarti “postingan dengan semua tag ini”. Cocokkan tag mana pun, lalu pertahankan postingan yang cocok dengan semuanya:

-- Input: posts
-- id | title
-- ---+----------------
-- 1  | Indexing basics
-- 2  | Weekend hiking
-- 3  | Joins explained

-- Input: tags
-- id | name
-- ---+---------
-- 1  | sql
-- 2  | outdoors
-- 3  | beginner

-- Input: post_tags
-- post_id | tag_id
-- --------+-------
-- 1       | 1
-- 1       | 3
-- 2       | 2
-- 2       | 3
-- 3       | 1

SELECT p.title
FROM posts p
JOIN post_tags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE t.name IN ('sql', 'beginner')
GROUP BY p.id, p.title
HAVING COUNT(*) = 2;

-- Output:
-- title
-- ---------------
-- Indexing basics

“Joins explained” hanya punya satu dari dua tag, jadi tersaring di HAVING. Ubah angka 2 sesuai jumlah tag dalam daftar.

Cari mata kuliah paling populer

Hitung enrollment tiap mata kuliah. LEFT JOIN mempertahankan Fisika, yang belum diambil siapa pun:

-- Input: courses
-- id | course_name
-- ---+------------
-- 1  | Biology
-- 2  | Chemistry
-- 3  | Physics

-- Input: enrollments
-- student_id | course_id
-- -----------+----------
-- 1          | 1
-- 1          | 2
-- 2          | 1
-- 3          | 1

SELECT c.course_name, COUNT(e.student_id) AS student_count
FROM courses c
LEFT JOIN enrollments e ON c.id = e.course_id
GROUP BY c.id, c.course_name
ORDER BY student_count DESC;

-- Output:
-- course_name | student_count
-- ------------+--------------
-- Biology     | 3
-- Chemistry   | 1
-- Physics     | 0

Lacak siapa bekerja di mana, dan kapan

Beri setiap tautan tanggal mulai dan tanggal selesai, maka junction table akan menyimpan riwayat. Seseorang bisa kembali ke departemen yang pernah ia tinggalkan, jadi pasangan kolomnya tidak lagi unik dan tanggal mulai ikut masuk ke primary key. Departemen saat ini adalah baris tanpa tanggal selesai:

CREATE TABLE employee_departments (
    employee_id INTEGER NOT NULL,
    department_id INTEGER NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,  -- NULL while it's their current department
    PRIMARY KEY (employee_id, department_id, start_date),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Input: employees
-- id | name
-- ---+------
-- 1  | Grace
-- 2  | Omar
-- 3  | Wei

-- Input: departments
-- id | name
-- ---+------------
-- 1  | Sales
-- 2  | Support
-- 3  | Engineering

-- Input: employee_departments
-- employee_id | department_id | start_date | end_date
-- ------------+---------------+------------+-----------
-- 1           | 1             | 2022-03-01 | 2024-06-30
-- 1           | 3             | 2024-07-01 | NULL
-- 2           | 2             | 2023-01-09 | NULL
-- 3           | 1             | 2021-05-17 | NULL

SELECT e.name, d.name AS department, ed.start_date
FROM employees e
JOIN employee_departments ed ON e.id = ed.employee_id
JOIN departments d ON ed.department_id = d.id
WHERE ed.end_date IS NULL
ORDER BY e.name;

-- Output:
-- name  | department  | start_date
-- ------+-------------+-----------
-- Grace | Engineering | 2024-07-01
-- Omar  | Support     | 2023-01-09
-- Wei   | Sales       | 2021-05-17

Untuk mencegah seseorang punya dua departemen saat ini, tambahkan partial unique index: CREATE UNIQUE INDEX idx_current_dept ON employee_departments(employee_id) WHERE end_date IS NULL;. MySQL tidak punya partial index, jadi cara ini tidak berfungsi di sana.

Cari pengguna yang saling follow

Tabel follows menghubungkan pengguna dengan pengguna. Constraint CHECK mencegah siapa pun mem-follow dirinya sendiri, dan join tabel ke dirinya sendiri menemukan pasangan yang saling follow:

CREATE TABLE follows (
    follower_id INTEGER NOT NULL,
    followed_id INTEGER NOT NULL,
    PRIMARY KEY (follower_id, followed_id),
    FOREIGN KEY (follower_id) REFERENCES users(id),
    FOREIGN KEY (followed_id) REFERENCES users(id),
    CONSTRAINT no_self_follow CHECK (follower_id <> followed_id)
);

-- INSERT INTO follows VALUES (2, 2);
-- ERROR: new row for relation "follows" violates check constraint "no_self_follow"

-- Input: users
-- id | name
-- ---+-----
-- 1  | Ava
-- 2  | Leo
-- 3  | Mia
-- 4  | Noah

-- Input: follows
-- follower_id | followed_id
-- ------------+------------
-- 1           | 2
-- 2           | 1
-- 1           | 3
-- 3           | 4
-- 4           | 3

SELECT u1.name AS user_a, u2.name AS user_b
FROM follows f
JOIN follows back ON back.follower_id = f.followed_id
                 AND back.followed_id = f.follower_id
JOIN users u1 ON u1.id = f.follower_id
JOIN users u2 ON u2.id = f.followed_id
WHERE f.follower_id < f.followed_id
ORDER BY user_a;

-- Output:
-- user_a | user_b
-- -------+-------
-- Ava    | Leo
-- Mia    | Noah

Ava mem-follow Mia, tapi Mia tidak mem-follow balik, jadi pasangan itu tidak ditampilkan. Kondisi f.follower_id < f.followed_id menampilkan setiap pasangan timbal balik sekali saja, bukan dua kali.

Bacaan lanjutan tentang relasi many-to-many

Di blog ini:

• Panduan relasi database: one-to-one, one-to-many, dan many-to-many berdampingan
• Panduan relasi one to many: foreign key di sisi “many”, tanpa junction table
• Relasi database one-to-one: saat setiap baris punya tepat satu pasangan
• Composite primary key di SQL: key dua kolom yang dipakai junction table
• Panduan diagram SQL JOIN: join yang melewati junction table
• Panduan SQL GROUP BY: menghitung dan menjumlahkan lintas tautan

Dokumentasi resmi:

• PostgreSQL: Foreign key
• MySQL: Constraint FOREIGN KEY
• SQLite: Dukungan foreign key
• SQL Server: Constraint primary key dan foreign key