MANY-TO-MANY əlaqəsini aralıq cədvəl ilə modelləşdirmək

"Çoxdan-çoxa" əlaqəsi — yəni bir cədvəldəki bir yazı digər cədvəldə bir neçə yazı ilə bağlı ola bilər və əksinə. Məsələn: - Bir tələbə (students cədvəlindən) bir neçə kursa yazıla bilər (courses cədvəlindən). - Bir kursa bir neçə tələbə qatıla bilər.

Problem ondadır ki, belə əlaqəni birbaşa saxlamaq rahat deyil. Ona görə də köməyə aralıq cədvəl gəlir və bu əlaqələri saxlayır.

Həyatdan bir nümunə

Təsəvvür elə ki, tələbələr üçün bir cədvəl və kurslar üçün bir cədvəl yaratmısan. Hər iki cədvəldə əlaqəli məlumatları saxlamaq istəsən, xaos yaranacaq:

• students cədvəlində tələbənin yazıldığı bütün kursların siyahısını saxlamaq lazım olacaq. Bəs siyahını necə saxlayasan? Vergüllə? Massivdə? Sorğular üçün başağrısı olacaq.
• courses cədvəlində tələbələrin siyahısını saxlamaq isə ümumiyyətlə kabusdur.

Ona görə də düzgün yol — tələbələrlə kurslar arasındakı əlaqələri saxlayan üçüncü bir cədvəl yaratmaqdır.

Aralıq cədvəl: bizim xilaskarımız!

Aralıq cədvəl (bəzən buna əlaqə cədvəli də deyirlər) bütün problemləri həll edir. O, iki xarici açar saxlayır:

• students cədvəlinə xarici açar.
• courses cədvəlinə xarici açar.

Bu cədvəldəki hər bir yazı konkret tələbə ilə konkret kurs arasında əlaqə yaradır.

"Çoxdan-çoxa" əlaqəsi üçün cədvəllərin yaradılması

Gəlin praktika edək! Tələbələrlə kurslar arasında əlaqə üçün cədvəlləri belə yarada bilərik:

Addım 1: students cədvəlinin yaradılması

Bu bizim tələbələr üçün cədvəlimizdir. Burada tələbələrin unikal identifikatorlarını və adlarını saxlayırıq.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

• student_id — tələbənin unikal identifikatorudur (avtoinkrement, sağ ol SERIAL!).
• name — tələbənin adı.

Addım 2: courses cədvəlinin yaradılması

İndi kurslar üçün cədvəl yaradaq. Burada kursların unikal identifikatorlarını və adlarını saxlayırıq.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

• course_id — kursun unikal identifikatoru.
• title — kursun adı.

Addım 3: enrollments aralıq cədvəlinin yaradılması

İndi isə sehrli əlaqə cədvəlimizi yaradaq. Burada iki sütun var, hər biri uyğun cədvələ xarici açardır.

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

Struktura baxaq:

• student_id — students cədvəlindəki student_id-ə xarici açar.
• course_id — courses cədvəlindəki course_id-ə xarici açar.
• PRIMARY KEY (student_id, course_id) — əsas açar iki xarici açarın birləşməsindən ibarətdir. Bu, hər bir əlaqənin unikal olmasına zəmanət verir.

Məlumatların əlavə olunması

Gəlin bir az data əlavə edək və necə işlədiyinə baxaq.

Addım 1: Tələbələrin əlavə olunması

INSERT INTO students (name) VALUES
('Alice'),
('Bob'),
('Charlie');

Nəticə:

Addım 2: Kursların əlavə olunması

INSERT INTO courses (title) VALUES
('Mathematics'),
('History'),
('Biology');

Nəticə:

Addım 3: enrollments cədvəlinə yazıların əlavə olunması

İndi tələbələri kurslara yazdıraq. Məsələn:

• Alice Mathematics və History-yə yazılıb.
• Bob yalnız Biology-yə yazılıb.
• Charlie hər üç kursa yazılıb.

INSERT INTO enrollments (student_id, course_id) VALUES
(1, 1), -- Alice Mathematics-də
(1, 2), -- Alice History-də
(2, 3), -- Bob Biology-də
(3, 1), -- Charlie Mathematics-də
(3, 2), -- Charlie History-də
(3, 3); -- Charlie Biology-də

Nəticə:

"Çoxdan-çoxa" əlaqəsi üçün sorğular

İndi artıq məlumatımız var, gəlin ondan faydalanmağa başlayaq!

Bir tələbənin yazıldığı bütün kursları necə tapmaq olar?

Məsələn, Alice (ID = 1) hansı kurslara yazılıb bilmək üçün belə sorğu ver:

SELECT c.title
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.student_id = 1;

Nəticə:

Bir kursa yazılan bütün tələbələri necə tapmaq olar?

Tutaq ki, Mathematics (ID = 1) kursuna kimlərin yazıldığını bilmək istəyirsən:

SELECT s.name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_id = 1;

Nəticə:

Tələbələri və onların kurslarını necə tapmaq olar?

Kim hansı kursa yazılıb tam şəkildə görmək üçün belə sorğu ver:

SELECT s.name AS student, c.title AS course
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

Nəticə:

enrollments cədvəli sxemimizi elastik edir — tələbələrlə kurslar arasında əlaqə əlavə etmək və ya silmək asandır, əsas cədvəllərə toxunmadan. JOIN-sorğular sayəsində rahatlıqla lazım olan məlumatı tapmaq olur, məsələn, kim hansı kursa yazılıb. Xarici açarlar isə avtomatik olaraq səhvlərin qarşısını alır — məsələn, heç kim səhvən mövcud olmayan kursa tələbə yaza bilməz.

"Çoxdan-çoxa" əlaqəsində tipik səhvlər

Unikal məhdudiyyətin olmaması: Əgər PRIMARY KEY qoymasan, eyni əlaqəni təsadüfən bir neçə dəfə əlavə edə bilərsən.

Məlumatların bütövlüyünün pozulması: Mövcud olmayan student_id və ya course_id ilə yazı əlavə etməyə çalışsan, səhv çıxacaq.

Məlumatların silinməsində səhv ardıcıllıq: Əgər əvvəlcə courses cədvəlindən kursu silsən, enrollments cədvəlində "yetim" yazılar qalacaq. Bunun qarşısını almaq üçün xarici açar təyinində ON DELETE CASCADE istifadə et.