Cədvəllər arasında ONE-TO-MANY əlaqəsinin modelləşdirilməsi

İndi gəlin bacarıqlarımızı bir az da inkişaf etdirək və relasional verilənlər bazalarında ən çox rast gəlinən əlaqələrdən birini - ONE-TO-MANY əlaqəsini quraq.

Təsəvvür elə ki, balaca bir şirkət var. Bir işçi yalnız bir şöbədə işləyə bilər, amma bir şöbədə onlarla işçi işləyə bilər. Bizim iki real obyektimiz var: işçilər və şöbələr. Onların arasındakı əlaqəni belə təsvir etmək olar: "bir şöbə bir çox işçini əhatə edə bilər", ya da rəsmi dildə "bir çoxa" (ONE-TO-MANY).

Elə bu cür ONE-TO-MANY münasibətləri hər yerdə var:

• bir müştəri bir çox sifariş edə bilər;
• bir müəllif bir çox kitab yaza bilər;
• bir müəllim bir neçə tələbəyə dərs deyə bilər.

Realasional verilənlər bazasında ONE-TO-MANY əlaqəsi xarici açar (FOREIGN KEY) vasitəsilə qurulur. "Çox" (MANY) cədvəlinin sütunlarından biri "bir" (ONE) cədvəlinin əsas açarına işarə edir.

ONE-TO-MANY əlaqəsini necə qurmaq olar

Gəlin klassik nümunəyə baxaq: müştərilər və sifarişlər arasındakı əlaqə. Bir müştəri bir çox sifariş edə bilər, amma hər sifariş yalnız bir müştəriyə bağlıdır. İki cədvəl yaradacağıq: customers (müştərilər) və orders (sifarişlər).

customers cədvəli

Bu bizim "bir" cədvəlimizdir. Burada müştərilər haqqında məlumat saxlanacaq.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- Müştərinin unikal identifikatoru
    name TEXT NOT NULL              -- Müştərinin adı
);

orders cədvəli

Bu "çox" cədvəldir. Burada sifarişlər saxlanılır və hər sifarişdə customer_id xarici açarı var, hansı ki, customers cədvəlindəki customer_id-ə işarə edir.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,           -- Sifarişin unikal identifikatoru
    order_date DATE NOT NULL,              -- Sifariş tarixi
    customer_id INT REFERENCES customers(customer_id) -- Xarici açar
);

Praktiki tətbiq

Məlumatların daxil edilməsi

İndi cədvəllərimizə bir az məlumat əlavə edək və əlaqənin işlədiyini yoxlayaq.

customers cədvəlinə müştərilər əlavə edirik:

INSERT INTO customers (name)
VALUES
    ('Ada Lovelace'),
    ('Grace Hopper'),
    ('Linus Torvalds');

Nəticə:

orders cədvəlinə sifarişlər əlavə edirik:

INSERT INTO orders (order_date, customer_id)
VALUES
    ('2023-10-01', 1),  -- Ada-nın sifarişi
    ('2023-10-02', 2),  -- Grace-in sifarişi
    ('2023-10-03', 1);  -- Ada-nın daha bir sifarişi

orders cədvəli:

Diqqət et: sifariş əlavə edəndə mütləq mövcud customer_id göstərməlisən. Əgər mövcud olmayan ID yazsan, baza error atacaq. Bu, məlumatların bütövlüyünü qorumaq üçündür.

Əlaqənin yoxlanması

İndi baxaq, cədvəllərimiz necə əlaqəlidir. Məsələn, soruşaq: Ada Lovelace hansı sifarişləri edib?

SELECT orders.order_id, orders.order_date, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.name = 'Ada Lovelace';

Nəticə:

Burada JOIN əmri ilə iki cədvəli xarici açar əsasında birləşdirdik. Rahat, səliqəli və heç bir təkrarlanan məlumat yoxdur!

Niyə bu lazımdır

ONE-TO-MANY əlaqəsi real həyatda super yayğındır və çox faydalıdır. Təsəvvür elə, onlayn mağazadır, minlərlə müştəri və milyonlarla sifariş var. Hər sifarişdə müştəri məlumatını təkrarlamaq əvəzinə, unikal müştəriləri bir cədvəldə, sifarişləri isə başqa cədvəldə saxlayırıq. Bu, məlumatların həcmini azaldır və bazanı daha səliqəli edir.

Bundan əlavə, məlumatları əlaqələndirmək imkanımız olduqda, güclü analitik sorğular yaza bilərik. Məsələn, soruşa bilərik: "Hər müştəri neçə sifariş edib?" və ya "Son ay kimlər sifariş verib?".

Çətinliklər və tələlər

Yeni başlayanlar adətən bunlarda "yıxılır":

Xarici açarın olmaması. Əgər "çox" cədvəldə xarici açarı unutmusansa, əlaqə sadəcə başında olacaq, amma baza onu tətbiq edə bilməyəcək. Bu o deməkdir ki, "qırıq" baza ala bilərsən, yəni sifarişlər mövcud olmayan müştərilərə aid ola bilər.

"Bir" cədvəldən qeyd silmək cəhdi. Məsələn, customers cədvəlindən müştərini silsən, onun sifarişləri orders cədvəlində "asılı" qalacaq. Bunun qarşısını almaq üçün ON DELETE CASCADE istifadə edə bilərsən ki, müştəri silinəndə onun sifarişləri də avtomatik silinsin.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE
);

İndi, əgər müştərini silsən:

DELETE FROM customers WHERE customer_id = 1;

Onun bütün sifarişləri də silinəcək. Baza təzə kofe kimi təmiz olacaq.

Məlumat daxil edərkən səhvlər. Əgər mövcud olmayan customer_id ilə sifariş əlavə etməyə çalışsan, belə bir error alacaqsan:

ERROR:  insert or update on table "orders" violates foreign key constraint