Cədvəllər arasında əlaqələrin modelləşdirilməsi və normallaşdırma

Bu gün çox vacib bir mövzuya baş vuracağıq: cədvəllər arasında əlaqələrin modelləşdirilməsi. Axı normallaşdırma təkcə atomik məlumatlar və artıq məlumatların silinməsi deyil, həm də cədvəllər arasında düzgün əlaqələrin qurulmasıdır.

Əgər verilənlər bazası məlumatların səliqəli saxlanma sistemidirsə, cədvəllər arasındakı əlaqələr isə məntiqi körpülərdir, hansı ki, məlumatların bir-biri ilə necə əlaqədə olduğunu göstərir. Təsəvvür elə bir kitabxana var, kitablar haqqında məlumatlar müəlliflərdən ayrı saxlanılır, amma hər bir kitab öz müəllifini xüsusi bir əlaqə vasitəsilə "tanıyır". Və ya götür internet mağazasını: məhsullar haqqında məlumatlar alıcılar haqqında məlumatlardan asılı olmayaraq mövcuddur, amma kimsə sifariş verəndə, sistem konkret alıcını konkret məhsullarla sifarişlər cədvəli vasitəsilə birləşdirir.

Tibb klinikasında pasiyentlər öz tibbi kartları ilə, həkimlər qəbul cədvəli ilə, dərmanlar isə təyinatlarla əlaqələndirilir. Bu əlaqələr sistemə başa düşməyə kömək edir ki, hansı məlumat nəyə aiddir, artıq məlumatları təkrarlamadan.

Əsas əlaqə tipləri real həyatda münasibətlər kimidir: pasport yalnız bir nəfərə məxsusdur (bir-birə), bir müəllim bir neçə kurs apara bilər (bir-çox), tələbələr müxtəlif fənlərə yazıla bilər, hər fənni isə müxtəlif tələbələr seçə bilər (çox-çoxa).

Bir-birə (1:1)

Bu elə bir əlaqədir ki, "A" cədvəlində bir yazı "B" cədvəlində yalnız bir yazıya uyğun gəlir. Məsələn, bizdə "İşçilər" və "Pasport məlumatları" cədvəlləri var. Bir işçinin yalnız bir pasportu ola bilər və hər bir pasport yalnız bir işçiyə məxsusdur.

Nümunə:

İşçilər

Pasport məlumatları

Burada əlaqə işçi_id adlı xarici açar vasitəsilə qurulur, hansı ki, "İşçilər" cədvəlindəki id-yə işarə edir.

Bir-çoxa (1:N)

Bu ən çox rast gəlinən əlaqə tipidir. Burada "A" cədvəlində hər bir yazı "B" cədvəlində bir neçə yazı ilə əlaqələndirilə bilər, amma "B" cədvəlində hər bir yazı yalnız bir "A" yazısı ilə bağlıdır. Məsələn, bizdə "Müəllimlər" və "Kurslar" cədvəlləri var. Bir müəllim bir neçə kurs apara bilər.

Nümunə:

Müəllimlər

Kurslar

Əlaqə "Kurslar" cədvəlindəki müəllim_id adlı xarici açar vasitəsilə qurulur.

Çox-çoxa (M:N)

Çox şey olanda maraqlıdır, amma bir az da çətindir. Burada "A" cədvəlində hər bir yazı "B" cədvəlində bir neçə yazı ilə əlaqələndirilə bilər və əksinə. Məsələn, tələbələr bir neçə kursa yazıla bilər, hər kursu isə bir neçə tələbə seçə bilər.

Nümunə:

Tələbələr

Kurslar

Əlaqə üçün bizə aralıq cədvəl lazımdır, hansı ki, tələbələrlə kurslar arasında uyğunluqları saxlayacaq:

Qeydiyyatlar

Xarici açarlarla əlaqələrin modelləşdirilməsi

Xarici açar — başqa cədvəldəki əsas açara işarə edən sütundur (və ya sütunlar toplusu). Bu, cədvəllər arasında əlaqə qurmağın əsas yoludur.

Xarici açar nümunəsi:

CREATE TABLE Kurslar (
    id SERIAL PRIMARY KEY,
    ad VARCHAR(255)
);

CREATE TABLE Qeydiyyatlar (
    id SERIAL PRIMARY KEY,
    tələbə_id INT,
    kurs_id INT,
    FOREIGN KEY (kurs_id) REFERENCES Kurslar(id)
);

Xarici açarları dizayn edəndə səhvlərdən necə qaçmaq olar? Əvvəlcə, xarici və əsas açar sütunlarının məlumat tiplərinin uyğun olmasına baxmaq lazımdır — əks halda baza əlaqəni yaratmağa imkan verməyəcək. Həmçinin, əvvəlcədən düşünmək lazımdır ki, yazılar silinəndə nə baş verməlidir. Məsələn, əsas cədvəldən bir sətri siləndə, asılı yazılarla nə etmək lazımdır? Ən məşhur variantlardan biri — ON DELETE CASCADE istifadə etməkdir, beləliklə əlaqəli məlumatlar əsas yazı ilə birlikdə avtomatik silinir. Bu, nizamı qorumağa və "asılı" linklərin qarşısını almağa kömək edir.

"Çox-çoxa" əlaqəsinin reallaşdırılması

Gəlin bir nümunəyə baxaq: bizdə tələbələr və kurslar var. Bir tələbə bir neçə kursa yazıla bilər, bir kursa isə bir neçə tələbə yazıla bilər. M:N əlaqəsini qurmaq üçün üç cədvəl yaradacağıq: Tələbələr, Kurslar və Qeydiyyatlar.

CREATE TABLE Tələbələr (
    id SERIAL PRIMARY KEY,
    ad VARCHAR(255)
);

CREATE TABLE Kurslar (
    id SERIAL PRIMARY KEY,
    ad VARCHAR(255)
);

CREATE TABLE Qeydiyyatlar (
    id SERIAL PRIMARY KEY,
    tələbə_id INT,
    kurs_id INT,
    FOREIGN KEY (tələbə_id) REFERENCES Tələbələr(id),
    FOREIGN KEY (kurs_id) REFERENCES Kurslar(id)
);

İndi Qeydiyyatlar cədvəlinə yazılar əlavə edib tələbələrlə kursları birləşdirə bilərik.

Praktik tapşırıq

Kurs idarəetmə sistemi üçün verilənlər bazası strukturu qur. Səndə Tələbələr, Kurslar və Qeydiyyatlar cədvəlləri olmalıdır. Bütün əlaqələri reallaşdır. Sonra tələbələr, kurslar və onların qeydiyyatları barədə nümunə məlumatlar əlavə et. Gəlin baxaq, bunu necə etmək lazımdır.

1. Cədvəlləri yaradırıq:

CREATE TABLE Tələbələr (
    id SERIAL PRIMARY KEY,
    ad VARCHAR(255)
);

CREATE TABLE Kurslar (
    id SERIAL PRIMARY KEY,
    ad VARCHAR(255)
);

CREATE TABLE Qeydiyyatlar (
    id SERIAL PRIMARY KEY,
    tələbə_id INT,
    kurs_id INT,
    FOREIGN KEY (tələbə_id) REFERENCES Tələbələr(id),
    FOREIGN KEY (kurs_id) REFERENCES Kurslar(id)
);

2. Məlumatları əlavə edirik:

INSERT INTO Tələbələr (ad) VALUES ('Otto Lin'), ('Mariya Çi');
INSERT INTO Kurslar (ad) VALUES ('SQL əsasları'), ('VB idarəetməsi');
INSERT INTO Qeydiyyatlar (tələbə_id, kurs_id) VALUES (1, 1), (1, 2), (2, 1);

3. Məlumatları yoxlayırıq:

SELECT
    Tələbələr.ad AS tələbə, 
    Kurslar.ad AS kurs
FROM Qeydiyyatlar
JOIN Tələbələr ON Qeydiyyatlar.tələbə_id = Tələbələr.id
JOIN Kurslar ON Qeydiyyatlar.kurs_id = Kurslar.id;

Nəticə:

Əlaqələrin modelləşdirilməsində çətinliklər və xüsusiyyətlər

Cədvəllər arasında əlaqələri modelləşdirəndə belə çətinliklər ola bilər:

• Məlumat silinəndə səhvlər (məsələn, əsas cədvəldəki yazı silinir, amma asılı cədvəldə ona bağlı yazılar qalır).
• Çoxlu məlumat olanda sorğuların performansı. M:N əlaqələri xüsusilə "acgöz" olur, çünki əlavə birləşmələr tələb edir.

Bunları həll etmək üçün kömək edir:

• Xarici açarlara indekslər əlavə etmək
• Yaxşı düşünülmüş verilənlər bazası strukturu.
• Normallaşdırma və performans arasında balans.

Biz cədvəllər arasında əlaqələrin modelləşdirilməsini çox sadə səviyyədə nəzərdən keçirdik və bunu praktikada tətbiq etdik, kurs idarəetmə sistemi üçün verilənlər bazası strukturu yaratdıq. Əlbəttə, böyük bir nümunə də baxmaq istərdim, amma necə edəcəyimi bilmədim. Böyük nümunə həm çətin, həm də darıxdırıcı olur. Ondan çox fayda olmaz. Kursun sonuna yaxın bu məsələyə qayıtmağa çalışaram.