SQL Çoktan Çoğa İlişki Nasıl Modellenir? Ara Tablo Örneği

SQL çoktan çoğa ilişki, iki tabloyu doğrudan birbirine bağlayarak değil, ikisinin anahtarlarını taşıyan üçüncü bir tabloyla kurulur. Bu tabloya ara tablo denir. Bu yazıdaki örnekte ogrenci ve ders tabloları ayrı durur, hangi öğrencinin hangi derse yazıldığı ise ogrenci_ders ara tablosunda tutulur. Bu yaklaşım standart SQL ile kurulur ve belirli bir veri tabanı ürününe bağlı değildir.

Önce Üç Kavram: Birincil Anahtar, Yabancı Anahtar ve Ara Tablo

Birincil anahtar, bir satırı tek başına tanımlayan sütundur. Aynı değer tabloda ikinci kez bulunamaz ve boş olamaz. Öğrenci numarası buna iyi bir örnektir, çünkü iki öğrencinin adı aynı olsa bile numaraları farklıdır.

Yabancı anahtar, başka bir tablonun birincil anahtarına başvuran sütundur. Veri tabanı bu sütuna yalnızca başvurduğu tabloda gerçekten var olan değerlerin yazılmasına izin verir. Böylece var olmayan bir derse kayıt eklenemez.

Ara tablo, iki tablonun anahtarlarını taşıyarak aralarındaki ilişkiyi tutan tablodur. İngilizce kaynaklarda junction table ya da bridge table adıyla da geçer. Her satırı "şu öğrenci, şu derse yazıldı" bilgisini temsil eder.

Bire çok ilişkide yabancı anahtar tek başına yeter. Bir sınıfta birçok öğrenci olabilir ama her öğrencinin tek bir sınıfı varsa, öğrenci tablosuna sinif_id sütunu eklemek işi çözer. Çoktan çoğa ilişkide ise iki taraf da çoğul olur: bir öğrenci birçok derse yazılabilir, bir derste de birçok öğrenci bulunabilir. ogrenci tablosuna ders sütunu eklersen bir öğrencinin tek dersi olabilir. ders tablosuna öğrenci sütunu eklersen bu kez bir dersin tek öğrencisi olabilir. İki tablodan birine yabancı anahtar eklemek bu yüzden yetmez. Çoktan çoğa ilişkide ilişkiyi temsil eden kayıtlar ara tabloda tutulur.

Örnekte iki temel tablo var. ogrenci tablosunda her öğrenciyi tanımlayan ogrenci_id ile öğrencinin adını taşıyan ad sütunları bulunur. ders tablosunda da her dersi tanımlayan ders_id ile dersin adını taşıyan ders_adi sütunları yer alır. İkisi de kendi konusunu tek başına anlatır. İlişki bilgisi bu tablolara değil, ara tabloya girer.

Tek Tabloda Ders Listesi Tutmak Neden Sorun Çıkarır?

Ara tablo fikri, ondan kaçınınca ortaya çıkan sorunlar görülünce daha iyi anlaşılır. Yeni başlayanların akla gelen ilk yolu, dersleri öğrencinin satırına metin olarak yazmaktır:

CREATE TABLE ogrenci_hatali (
  ogrenci_id INTEGER PRIMARY KEY,
  ad VARCHAR(50),
  dersler VARCHAR(200)
);

INSERT INTO ogrenci_hatali (ogrenci_id, ad, dersler) VALUES
  (1, 'Ayşe', 'Matematik, Programlama'),
  (2, 'Mehmet', 'Fizik Laboratuvarı'),
  (3, 'Elif', 'Fizik, Matematik');

Bu tablo çalışır ve veriler yerine girer. Ancak dersler sütunu tek bir değer gibi görünen ama içinde birden çok değer taşıyan bir metindir. Veri tabanı bu virgülleri tanımaz, yalnızca uzun bir karakter dizisi görür.

İkinci yanlış yol, her ders için öğrenci satırını çoğaltmaktır:

CREATE TABLE ogrenci_ders_tek (
  ad VARCHAR(50),
  ders_adi VARCHAR(50)
);

INSERT INTO ogrenci_ders_tek (ad, ders_adi) VALUES
  ('Ayşe', 'Matematik'),
  ('Ayşe', 'Programlama'),
  ('Elif', 'Fizik'),
  ('Elif', 'Matematik');

Bu tasarım metin birleştirmeyi ortadan kaldırır ama başka sorunlar getirir. Üç somut sonucu var.

1. Tekrar eden veri

Ayşe iki derse yazıldığı için adı iki kez saklanır. Beş derse yazılırsa beş kez saklanır. Aynı şekilde "Matematik" adı da her öğrencinin satırında yeniden yazılır. Dersin adı tek bir yerde durmadığı için ders de bağımsız bir kayıt olarak var olamaz.

2. Güncelleme tutarsızlığı

Ayşe'nin adı "Ayşe Kaya" olarak değişsin ve güncelleme yalnızca bir satıra uygulansın:

UPDATE ogrenci_ders_tek
SET ad = 'Ayşe Kaya'
WHERE ad = 'Ayşe' AND ders_adi = 'Matematik';

SELECT ad, ders_adi FROM ogrenci_ders_tek WHERE ders_adi IN ('Matematik', 'Programlama');

ad          ders_adi
Ayşe Kaya   Matematik
Ayşe        Programlama
Elif        Matematik

Aynı öğrenci artık tabloda iki farklı adla duruyor. Hangisinin doğru olduğunu tablo söylemez. Ad tek bir yerde, ogrenci tablosunda tutulsaydı bu tutarsızlık oluşamazdı.

3. Sorgu zorluğu

Birinci tasarımda "Fizik alan öğrencileri listele" sorusu metin aramasına dönüşür:

SELECT ad FROM ogrenci_hatali WHERE dersler LIKE '%Fizik%';

ad
Mehmet
Elif

Mehmet yalnızca "Fizik Laboratuvarı" dersine yazılı olduğu hâlde sonuçta yer alır, çünkü bu ders adı "Fizik" sözcüğünü içerir. Sorgu ders adını değil, metnin bir parçasını eşleştirdiği için yanlış sonuç döner. Bir dersi listeden çıkarmak da metni baştan yazmayı gerektirir: Ayşe'nin Matematik dersini silmek için dersler değerini elle 'Programlama' olarak yeniden girmen gerekir. Ders sayısı ve öğrenci sayısı arttıkça bu tür düzenlemeler hata üretmeye açık hâle gelir.

Üç sorunun ortak nedeni, ders adlarının öğrenci tablosunun içinde yaşamasıdır. Ders, öğrenciden bağımsız bir varlıktır ve kendi tablosunda, kendi kimliğiyle durmalıdır. İlişki ise bu iki varlığın dışında, ayrı bir tabloda tutulmalıdır.

Doğru Tasarım: ogrenci, ders ve ogrenci_ders Tabloları

Çözüm üç tablodur: iki varlık tablosu ve aralarında köprü görevi gören bir ara tablo. Çoktan çoğa ilişkide ilişkiyi temsil eden kayıtlar ara tabloda tutulur. Ara tablodaki her satır "şu öğrenci şu derse kayıtlı" bilgisini taşır.

CREATE TABLE ogrenci (
  ogrenci_id INTEGER PRIMARY KEY,
  ad VARCHAR(100) NOT NULL
);

CREATE TABLE ders (
  ders_id INTEGER PRIMARY KEY,
  ders_adi VARCHAR(100) NOT NULL
);

CREATE TABLE ogrenci_ders (
  ogrenci_id INTEGER NOT NULL,
  ders_id INTEGER NOT NULL,
  PRIMARY KEY (ogrenci_id, ders_id),
  FOREIGN KEY (ogrenci_id) REFERENCES ogrenci (ogrenci_id),
  FOREIGN KEY (ders_id) REFERENCES ders (ders_id)
);

İlk iki tablo sıradan: her satırın benzersiz bir kimliği ve bir adı var. Asıl iş üçüncü tabloda gerçekleşiyor.

Birleşik birincil anahtar ne işe yarar?

PRIMARY KEY (ogrenci_id, ders_id) tek bir sütunu değil, iki sütunun birleşimini benzersiz kılar. Yani (1, 101) çifti tabloda yalnızca bir kez bulunabilir; aynı öğrenci aynı derse iki kez kaydedilemez. Buna karşılık kural çifte uygulandığı için aynı öğrenci farklı derslerle, aynı ders de farklı öğrencilerle eşleşebilir. (1, 101) ile (1, 103) ya da (1, 101) ile (2, 101) birlikte var olabilir. İki sütun da NOT NULL olduğu için yarım kayıt da girmez.

Yabancı anahtarlar neyi korur?

FOREIGN KEY tanımları, ara tabloya yalnızca gerçekten var olan kimliklerin girmesini sağlar. ogrenci tablosunda olmayan bir ogrenci_id ya da ders tablosunda olmayan bir ders_id kaydedilmeye çalışılırsa veri tabanı bunu reddeder. Böylece hiçbir öğrenciye ya da derse bağlı olmayan "yetim" kayıtlar oluşmaz.

İlişkinin kendi bilgisi nereye gider?

Bazen ilişkinin kendisine ait bilgi tutmak gerekir. Örneğin kayit_tarihi sütunu ara tabloya eklenebilir: Tarih öğrenciye de derse de değil, öğrencinin o derse kaydolma anına aittir. Böyle bir bilgi iki varlık tablosundan birine konursa anlamını yitirir, bu yüzden doğru yeri ara tablodur.

Verileri Ekle ve JOIN ile Oku: Beklenen Çıktıyla Birlikte

Şemayı çalıştırdıktan sonra aşağıdaki kayıtları ekleyebilirsin. Önce varlık tablolarını doldur, ara tabloyu en sona bırak; çünkü yabancı anahtarlar, başvurduğu kimliklerin önceden var olmasını bekler.

INSERT INTO ogrenci (ogrenci_id, ad) VALUES (1, 'Ayşe Demir');
INSERT INTO ogrenci (ogrenci_id, ad) VALUES (2, 'Mehmet Kaya');
INSERT INTO ogrenci (ogrenci_id, ad) VALUES (3, 'Zeynep Aras');

INSERT INTO ders (ders_id, ders_adi) VALUES (101, 'Matematik');
INSERT INTO ders (ders_id, ders_adi) VALUES (102, 'Fizik');
INSERT INTO ders (ders_id, ders_adi) VALUES (103, 'Programlama');

INSERT INTO ogrenci_ders (ogrenci_id, ders_id) VALUES (1, 101);
INSERT INTO ogrenci_ders (ogrenci_id, ders_id) VALUES (1, 103);
INSERT INTO ogrenci_ders (ogrenci_id, ders_id) VALUES (2, 101);
INSERT INTO ogrenci_ders (ogrenci_id, ders_id) VALUES (2, 102);
INSERT INTO ogrenci_ders (ogrenci_id, ders_id) VALUES (3, 103);

Şimdi her öğrencinin hangi derslere kayıtlı olduğunu listeleyelim:

SELECT o.ad, d.ders_adi
FROM ogrenci o
INNER JOIN ogrenci_ders od ON od.ogrenci_id = o.ogrenci_id
INNER JOIN ders d ON d.ders_id = od.ders_id
ORDER BY o.ad, d.ders_adi;

Beklenen çıktı, bu sırayla 5 satırdır:

Ayşe Demir  | Matematik
Ayşe Demir  | Programlama
Mehmet Kaya | Fizik
Mehmet Kaya | Matematik
Zeynep Aras | Programlama

Sorguyu şöyle okuyabilirsin: ara tablo iki tabloyu köprüler. İlk JOIN her öğrenciyi ogrenci_ders içindeki kayıtlarıyla eşleştirir, ikinci JOIN bu kayıtlardaki ders_id değerini ders tablosundaki ada bağlar. Sıralamayı yalnızca ORDER BY belirler: önce öğrenci adına, aynı öğrencide ders adına göre dizilir. ORDER BY olmadan satır sırası garanti edilmez. Alfabetik sıralama veri tabanının harf karşılaştırma ayarına bağlı olabilir; bu örnekteki adlarla çıktı yukarıdaki gibidir. (1, 101) çiftini bir kez daha eklemeye çalışırsan birleşik birincil anahtar kuralına takılır ve kayıt reddedilir; mesajın metni kullandığın veri tabanına göre değişir. Veri tabanı konulu diğer yazılara göz atmak istersen Berk Akademi blog sayfasında yeni konular bulabilirsin.

Kendi Çoktan Çoğa İlişkini Kurarken Kontrol Listesi

Aynı adımlar öğrenci ve ders dışındaki her ilişkiye uygulanabilir. Yeni bir şema tasarlarken bu sırayı izleyebilirsin:

1. İki varlığı belirle ve ilişkiyi cümleyle sına. "Bir X birçok Y ile, bir Y de birçok X ile eşleşir mi?" sorusuna iki yönde de "evet" diyorsan ilişki çoktan çoğadır.
2. Her varlığa kendi tablosunu ve birincil anahtarını ver. Örneğimizde ogrenci ve ders ayrı tablolardır, her birinin kendi id sütunu vardır.
3. Ara tablo aç ve iki yabancı anahtar ekle. Her yabancı anahtar, ilgili tablonun birincil anahtarına başvurmalıdır.
4. İki sütunu birleşik birincil anahtar yap. Böylece aynı öğrenci aynı derse ikinci kez kaydedilemez.
5. İlişkiye ait bilgiyi ara tabloya koy. kayit_tarihi gibi bir bilgi ne öğrenciye ne dersin kendisine aittir, eşleşmeye aittir.
6. Birkaç satırlık örnek veriyle JOIN sorgusunu dene. Beklediğin satırlar geliyor mu, hiçbir eşleşme kaybolmuyor veya tekrarlanmıyor mu diye bak.

Aynı kalıp kitap ile yazar, film ile oyuncu ya da yazı ile etiket arasındaki ilişkilere de uyar.

Şema tasarlamak da kod yazmadan önce düşünmeyi gerektiren bir alıştırmadır: varlıkları, eşleşme yönlerini ve anahtarları kâğıt üstünde netleştirmeden tablo oluşturmak çoğu zaman sonradan düzeltme demektir. Tasarıma geçmeden önce adım adım düşünme becerini ölçmek istersen dilden bağımsız çalışan ücretsiz Algoritmik Düşünme Testini deneyebilirsin.