Şema Tasarım Kalıpları

Önceki dersler tek tek kuralları verdi: bağımlılıkları bul, bağıntıyı böl, tekrarı ölçerek geri koy. Uygulamada aynı modelleme durumları tekrar tekrar karşımıza çıkar ve her birinin yerleşik bir karşılığı vardır. Bu dersin sorusu şudur: hangi durum hangi şema yapısına çevrilir, ve hangi yaygın yapılar aslında sorunun kendisidir?

İlişki Kardinaliteleri

İki bağıntı arasındaki ilişkinin şemaya çevrilmesi kardinaliteye bağlıdır.

Bire çok ilişki, yabancı anahtarın “çok” tarafına konmasıyla kurulur. Bir üyenin birden çok ödünç kaydı vardır; uye_no sütunu ödünç bağıntısında durur. Ters yön — üye bağıntısında ödünç numarası tutmak — tek hücreye liste sıkıştırmak demektir ve birinci normal formu bozar.

Bire bir ilişki nadirdir ve genellikle iki gerekçeden birine dayanır: satırın seyrek doldurulan bölümünü ayırmak, ya da erişimi farklı yetkiye bağlı sütunları ayrı tutmak. Yabancı anahtar iki taraftan birine konur ve tekillik kısıtıyla desteklenir.

Çoka çok ilişki doğrudan ifade edilemez; araya bir bağlantı bağıntısı (junction table) konur. Anahtarı, iki tarafın anahtarlarından oluşan bileşik anahtardır:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
PRAGMA foreign_keys = ON;
CREATE TABLE kitap (isbn TEXT PRIMARY KEY NOT NULL, baslik TEXT NOT NULL);
CREATE TABLE yazar (yazar_no INTEGER PRIMARY KEY, ad TEXT NOT NULL);
CREATE TABLE kitap_yazar (
  isbn     TEXT    NOT NULL REFERENCES kitap (isbn),
  yazar_no INTEGER NOT NULL REFERENCES yazar (yazar_no),
  sira     INTEGER NOT NULL,
  PRIMARY KEY (isbn, yazar_no)
);
INSERT INTO kitap VALUES ('975-01', 'Kayip Zaman'), ('975-02', 'Deniz Fenerleri');
INSERT INTO yazar VALUES (7, 'Elif Toprak'), (8, 'Kemal Aras');
INSERT INTO kitap_yazar VALUES ('975-01', 7, 1), ('975-01', 8, 2), ('975-02', 8, 1);
SELECT k.baslik, COUNT(*) AS yazar_sayisi FROM kitap k
JOIN kitap_yazar ky ON ky.isbn = k.isbn GROUP BY k.isbn ORDER BY k.isbn;
SQL

┌─────────────────┬──────────────┐
│     baslik      │ yazar_sayisi │
├─────────────────┼──────────────┤
│ Kayip Zaman     │ 2            │
│ Deniz Fenerleri │ 1            │
└─────────────────┴──────────────┘

Bağlantı bağıntısı çoğu zaman yalın bir eşleştirmeden fazlasını taşır. Buradaki sira sütunu, yazarların kapaktaki sırasını tutar — ilişkinin kendisine ait bir özniteliktir ve başka hiçbir bağıntıya sığmaz. Ödünç işlemi de aslında böyledir: üye ile nüsha arasındaki ilişkiye ait alış ve iade tarihlerini taşır.

Hiyerarşik Veri

Kitap konuları ağaç oluşturur: edebiyat altında roman, roman altında tarihsel roman. En yalın karşılık, bağıntının kendi anahtarına başvurmasıdır — buna komşuluk listesi (adjacency list) denir. Ağaç üzerinde dolaşmak özyinelemeli sorgu gerektirir:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
PRAGMA foreign_keys = ON;
CREATE TABLE konu (
  konu_no  INTEGER PRIMARY KEY,
  ad       TEXT    NOT NULL,
  ust_konu INTEGER REFERENCES konu (konu_no)
);
INSERT INTO konu VALUES (1, 'Edebiyat', NULL), (2, 'Roman', 1), (3, 'Siir', 1),
                        (4, 'Tarihsel Roman', 2), (5, 'Bilim', NULL);

WITH RECURSIVE dal(konu_no, ad, derinlik, yol) AS (
  SELECT konu_no, ad, 0, ad FROM konu WHERE ust_konu IS NULL
  UNION ALL
  SELECT k.konu_no, k.ad, d.derinlik + 1, d.yol || ' / ' || k.ad
  FROM konu k JOIN dal d ON k.ust_konu = d.konu_no
)
SELECT derinlik, yol FROM dal ORDER BY yol;
SQL

┌──────────┬───────────────────────────────────┐
│ derinlik │                yol                │
├──────────┼───────────────────────────────────┤
│ 0        │ Bilim                             │
│ 0        │ Edebiyat                          │
│ 1        │ Edebiyat / Roman                  │
│ 2        │ Edebiyat / Roman / Tarihsel Roman │
│ 1        │ Edebiyat / Siir                   │
└──────────┴───────────────────────────────────┘

Sorgu, kökten başlayıp her adımda bir alt seviyeyi ekleyerek ağacı açar. Veri Yapıları kursundaki enine arama ile aynı fikirdir; fark, kuyruğun sorgu motorunda tutulmasıdır.

Komşuluk listesinin yazması ucuz, okuması pahalıdır: bir düğümün bütün alt ağacını almak derinlik kadar adım gerektirir. Üç yaygın karşılık vardır. Yol numaralandırma (path enumeration) her satırda kökten gelen yolu bir dizgide tutar; alt ağaç sorgusu önek eşlemesine döner, ama taşıma işlemleri bütün alt ağacı günceller. İç içe küme (nested set) her düğüme sol ve sağ sayı verir; okuma çok hızlıdır, ekleme neredeyse bütün tabloyu kaydırır. Kapanış tablosu (closure table) her ata–torun çiftini ayrı satırda tutar; okuma ve yazma dengelidir, karşılığında satır sayısı artar. Seçim, okuma ile yazma oranına göre yapılır.

Alt Tür ve Zaman

Alt tür (subtype) durumu, ortak bir üst kavramın farklı öznitelikler taşıyan türlerinden doğar: kütüphanedeki materyaller kitap, süreli yayın ve ses kaydı olabilir; ortak alanları vardır, ayrık alanları da. Üç karşılık kullanılır. Tek bağıntıda tutmak — bütün türlerin sütunlarını yan yana koymak — çok sayıda boş değer üretir. Tür başına ayrı bağıntı, ortak sorguları zorlaştırır. Ortak öznitelikleri bir üst bağıntıda, ayrık öznitelikleri tür başına bir alt bağıntıda tutmak dengeli karşılıktır; alt bağıntının birincil anahtarı, aynı zamanda üst bağıntıya yabancı anahtardır.

Zaman boyutu iki ayrı gereksinimi gizler. Birincisi, bir olgunun geçerli olduğu aralıktır: üyelik ücretinin hangi tarihler arasında hangi tutarda olduğu. Karşılığı, satıra başlangıç ve bitiş tarihi eklemek ve aralıkların örtüşmemesini kısıtla denetlemektir. İkincisi, bir satırın geçmiş hâllerini saklamaktır: her değişiklikte yeni bir sürüm satırı yazılır, güncel satır bir bayrakla ya da boş bitiş tarihiyle işaretlenir. İkisini karıştırmak, ne geçmişi doğru veren ne de güncel durumu ucuza veren bir şema üretir.

Karşı Kalıplar

Bazı yapılar sorunu çözer görünürken kısıtları ortadan kaldırır. En yaygını, sütunları satır olarak tutan anahtar-değer tablosudur:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
.nullvalue (bos)
CREATE TABLE kitap_ozellik (
  isbn    TEXT NOT NULL,
  ozellik TEXT NOT NULL,
  deger   TEXT NOT NULL,
  PRIMARY KEY (isbn, ozellik)
);
INSERT INTO kitap_ozellik VALUES
  ('975-01', 'baslik', 'Kayip Zaman'), ('975-01', 'yayin_yili', '2019'),
  ('975-02', 'baslik', 'Deniz Fenerleri'), ('975-02', 'yayin_yili', 'iki bin yirmi'),
  ('975-03', 'baslik', 'Sessiz Bahce');

SELECT b.deger AS baslik, y.deger AS yayin_yili
FROM kitap_ozellik b
LEFT JOIN kitap_ozellik y ON y.isbn = b.isbn AND y.ozellik = 'yayin_yili'
WHERE b.ozellik = 'baslik'
ORDER BY b.isbn;
SQL

┌─────────────────┬───────────────┐
│     baslik      │  yayin_yili   │
├─────────────────┼───────────────┤
│ Kayip Zaman     │ 2019          │
│ Deniz Fenerleri │ iki bin yirmi │
│ Sessiz Bahce    │ (bos)         │
└─────────────────┴───────────────┘

Üç kayıp aynı çıktıda görünür. Yayın yılına tip ve alan kısıtı yazılamaz, çünkü deger sütunu bütün öznitelikler için ortaktır — iki bin yirmi girmiştir. Zorunluluk uygulanamaz; üçüncü kitabın yılı hiç yoktur ve bunu engelleyecek bir NOT NULL yeri yoktur. Ve iki öznitelik okumak için tabloyu kendisiyle birleştirmek gerekir; öznitelik sayısı arttıkça birleştirme sayısı da artar.

Bu yapının meşru kullanımı dardır: gerçekten önceden bilinmeyen, kullanıcı tarafından tanımlanan ve üzerinde kısıt aranmayan öznitelikler. Şema değişikliğinden kaçınmak için seçildiğinde, veritabanının verdiği bütün güvenceler geri verilmiş olur.

Üç karşı kalıp daha sık görülür. Çok amaçlı yabancı anahtar, tek bir sütunla birden çok bağıntıya başvurmayı dener — hedef bağıntının adı ayrı bir sütunda tutulur ve başvuru bütünlüğü uygulanamaz hâle gelir. Sütun adında numara — telefon_1, telefon_2 — birinci normal formu yatay yazmaktır. Sihirli değer, boş değer yerine -1 ya da '1900-01-01' gibi bir yer tutucu koymaktır; her sorgunun o değeri elemesini gerektirir ve eleme unutulduğunda toplamlara karışır.

Özet

• Bire çok ilişki yabancı anahtarla, çoka çok ilişki bileşik anahtarlı bir bağlantı bağıntısıyla kurulur; bağlantı bağıntısı ilişkinin kendi özniteliklerini taşır.
• Hiyerarşi için komşuluk listesi yazmada ucuz okumada pahalıdır; yol numaralandırma, iç içe küme ve kapanış tablosu bu dengeyi farklı yönlere kaydırır.
• Alt türler ortak öznitelikleri üst bağıntıda, ayrık öznitelikleri tür başına alt bağıntıda tutularak modellenir.
• Geçerlilik aralığı ile sürüm geçmişi ayrı gereksinimlerdir; tek yapıyla ikisini birden karşılamaya çalışmak her ikisini de bozar.
• Anahtar-değer tablosu tip, alan ve zorunluluk kısıtlarını birden yitirir ve öznitelik başına bir birleştirme ekler; meşru kullanımı önceden bilinmeyen özniteliklerle sınırlıdır.

Sonraki Adım

Normalizasyon konusu burada tamamlanıyor: şemanın nasıl kurulacağı, nasıl bölüneceği ve hangi durumlarda bilinçli olarak birleştirileceği belirlendi. Kalan soru, bu şemanın hangi tür iş yükü altında çalışacağıdır. Tek tek ödünç işlemlerini kaydeden bir sistemle yıllık ödünç istatistiği çıkaran bir sistem aynı veriyi ister ama aynı erişim biçimini istemez. Sıradaki konu bu ayrımla başlar.