PostgreSQL’ de Database Dizaynı ve Temel SQL Kılavuzu-3

Published in

Fazla

7 min readSep 14, 2021

Merhabalar, bu yazı hazırlayacağım üç kısımlık postgreSQL’ de Database Dizaynı ve temel SQL kılavuzunun son kısmıdır.

Bu yazıda ele alınacak başlıklar şu şekildedir:

1-) Keyler(Anahtarlar).
2-) Keyler’ in Kullanımları İçin Tavsiyeler.
3-) Database Dizaynı — Gerçek Hayat Senaryosu ve Joinler.

4-) Many-to-Many Relationship(Çoktan-çoğa ilişki).

1-) Keyler(Anahtarlar)

Üç çeşit anahtar vardır: Primary Key, Logical Key ve Foreign Key.
Primary Key: Genellikle integer olup otomatik artacak şekilde tasarlanır. Çoğu programda son kullanıcı primary key’ in değerini göremez. Satırları birbirinden ayırmak için bir kimlik görevi görür. Primary key olarak tanımlanan özellik her satırda farklı bir değere sahiptir. Satırların T.C kimlik numaraları gibi düşünebiliriz.
Logical Key: Bir listeleme işleminde, bir nesneyi ararken kullanacağınız özellik logical key olarak adlandırılır. Örneğin bir sınıf tablosunda bir öğrenciyi ismine göre arayıp getirmek istiyorsanız isim bu tablonun logical keyidir.
Foreign Key: Tablolar arasında iletişimi sağlamakla görevli olan keydir. Bir veri tablosuna girilebilecek değerleri başka bir veri tablosundaki alanlarla ilişkilendirmeye yarar. Örnek vermek gerekirse sporcular tablosunda her sporcunun yarıştığı branşı tutmak isteyelim. Bunun için ilk olarak branşlar diye bir tablo oluşturup branş isimlerini gireriz. Daha sonrasında ise branş tablosunun primary key’ i ile sporcular tablosunda bulunan branş foreign key’ ini eşleştiririz. Böylelikle sporcular listelenirken branş değerleri branş tablosundan çekilir.

2-) Keyler’ in Kullanımları İçin Tavsiyeler

Logical key, asla primary key olarak kullanılmaz. Örnek verme gerekirse öğrenciler tablosunda oğrenci_email alanını logical key olarak kullanmak istiyorsunuz. Eğer bu alanı aynı zamanda primary key olarak da tanımlarsanız bütün tablo ilişkilerini ogrenci_email alanı ile kurmak zorunda kalıcaksınız ve yarın bir gün bir öğrencinin maili değişebilir. Bu durumda kötü bir tasarım izlediğiniz için siz veya kuruluşunuz zaman, para kaybedecektir.
Diğer tablolarla ilişki kurarken ilişkiyi string değerleri ile kurduğunuzda bu ilişki integer ile kurulan ilişkilere göre daha VERİMSİZ olacaktır. Yani ilişki kurarkan olabildiğinde integer değerler kullanmaya özen gösterin.
Bir foreign key baska bir tablodaki primary key ile ilişki kurar. Bütün primary keyleri integer değerde kullanmak ve dolayısıyla da tüm foreign keylerin integer olması güzel bir tasarımdır.

3-) Database Dizaynı — Gerçek Hayat Senaryosu ve Joinler

Yukarıdaki fotoğrafta bulunan bir müzik oynatıcısına ait olan bilgilendirme tablosunun ara tarafta nasıl şekillendiğini beraber anlamaya çalışalım.

3.1-) Tasarımın Yapılması

a) İlk objenin bulunması

İlk olarak karar vermemiz gereken şey ilk objemiz hangisi? Karar verdiğimiz obje bizim ilk tablomuz olacak. Genellikle bu objeyi bulmak için kendimize şu soruyu yöneltmemiz gerekir: Uygulamamızın düzenlediği şey nedir? Eğer bizim elimizde bir üniversite listesi olsaydı objemiz öğrenci olurdu. Eğer bir araba markasına ait arabaların listesi olsaydı araba olurdu ve bu örnekte bizim için ilk obje müzik parçalarıdır. Çünkü geri kalan tüm kolonlar bir müzik parçasına ait bilgileri içermektedir.

b) İlk objeye ait tablonun oluşturulması

Müzik parçaları tablomuzda bulunan kolonlar şu şekilde olacaktır: Müzik parçası, süre, yıldız sayısı ve kaç kez dinlendiği. Ana obje dışında tabloya eklediğimiz tüm kolonlar sayısal değerlerle ifade ediliyor. Her zaman için sayısal değerleri kullanmak bize yol su köprü olarak geri döner.

c) Geri kalan kolonlarla çalışmak

Geriye kalan kolonlar: Albüm, sanatçı ismi ve tür.

Müzik listeleri bir albüme aittir. Albümler ise bir sanatçıya aittir. Bundan dolayı ana tablomuz oluşturacağımız albüm tablosu ile, albüm tablosu ise oluşturacağımız sanatçı tablosu ile ilişkilendirilecektir. Böylelikle ana tabloda albümü değiştirdiğimiz zaman sanatçıda olması gerektiği gibi onunla birlikte değişecek. Tür direkt olarak müzik parçasına ait bir özellik olduğu için, oluşturacağımız tür tablosu ana tablo ile ilişkilendirilecektir.

3.2-) Çizdiğimiz şemanın koda aktarılması

a) Database oluşturma

Superuser olarak giriş yaptıktan sonra, kullanıcılarımızdan biri için “music” adında bir database oluşturuyoruz.

b) Tabloları Oluşturma

“artist” tablomuzda otomatik artacak şekilde tasarlanan ve primary key olan id kolonu ile birlikte hepsi birbirinden benzersiz olacak şekilde tasarlanan name alanı bulunmaktadır.
“album” tablomuzda yine otomatik artacak şekilde tasarlanan ve primary key olan id alanı, unique olan title alanı ve artist tablosu ile iletişimi sağlamak için foreign key olan artist_id alanı bulunmaktadır.
“artist_id” oluşturulurken kullanılan ON DELETE CASCADE şu görevi üstlenmektedir: herhangi bir artist silindiği zaman onun sahip olduğu albumlerde silinecektir. ON DELETE CASCADE kullanılmayan durumlarda artist silindiği zaman albumler duracağı için ya veri kirliliğine yol açacaktır ya da hepsini tek tek arayıp silmek gerekecektir.

Burada ise genre ve track tablolarımız oluşturulmuştur.
track tablomuzda unique olarak tanımladığımız alanımız title ve album id alanlarının kombinasyonundan oluşmaktadır. Bunu yapmamızın nedeni aynı şarkının farklı albümlerde yer alabiliyor olmasıdır. Eğer biz sadece title alanını unique olarak tanımlasaydık tüm track tablomuzda aynı isimden sadece bir şarkı olabilirdi ve farklı albümlerde aynı şarkıların yer alması söz konusu olamazdı. Fakat biz title ve album id alanlarını kombine ederek title alanının albüm içerisinde benzersiz olmasını sağladık. Böylelikle aynı title ve albüm id’ sine sahip birden fazla nesne olmasının önüne geçtik.

3.3-) Veri Girişi

2 adet artist tanımlıyoruz.

2 Adet album girişi yapıyoruz. İlk album AC/DC grubuna ait iken “IV” albumu Led Zeppelin grubuna aittir.

2 Adet tür ekliyoruz.

Track değerlerimizi giriyoruz.

Oluşan son durum şu şekildedir:

3.4-) Joinleri kullanarak listeleme

JOIN operasyonu foreign keylerin bağlı oldukları tablolara giderek oradaki değerlerin de bize gösterilmesini sağlar.
Aşağıdaki şemayı incelemek gerekirse, album ve artist tabloları arasında bir ilişki vardır. Bu ilişkilerden yararlanarak iki tablodaki verileri tek tabloda toplamak istersem JOIN operasyonu kullanmam gerekmektedir.
JOIN operasyonu 3 temel kısımdan oluşmaktadır.
Yeşil Kısım: Hangi verileri görmek istiyoruz? Cevap: albumun title alanını ve artistin name alanını.
Sarı Kısım: Görmek istediğimiz verileri tutan tablolar. Cevap: album ve artist.
Mor Kısım: Tablolar nasıl birbirine bağlanıp sıralanacaktırlar? Cevap: album tablosundaki artis id ile artist tablosundaki id alanının eşitliğine göre sıralanacaktırlar.
Burada dikkat etmemiz gereken unsur: JOIN operasyonu yapılırken hep aynı yönde hareket edilir. Yani yazdığımız bütün komutlar çocuk tablodan ana tabloya doğrudur(album->artist). Yeşil, Sarı ve Mor kısımların hepsinde album tablosu ile başlanmıştır.

Bir örnek daha vermek gerekirse. Track tablosunun title alanını ve genre tablosunun name alanını tek bir tablo gibi sunmak istiyoruz.
Tüm müzik parçaları bir türe aittir. Yani track tablosu çocuk tablo genre tablosu ana tablodur. Bu yüzden de foreign key track tablosunda yer almaktadır. Foreign keyin bulunduğu tablo bizim hareket yönümüzü belirleyen tablodur.
Hareket yönü: track->genre
SELECT [yan _yana _görmek _istediğimiz _veriler] FROM [bu_verileri_tutan_tablolar] ON [verileri_eşleme_kuralı]

Bu örnek de sizler için:

1-)Many-to-Many Relationship(Çoktan-çoğa ilişki)

Örnek üzerinden gitmek gerekirse. Bir üniversitede bir öğrenci birden fazla dersi seçebilir. Aynı şekilde bir derste birden fazla öğrenci tarafından alınabilir. Bu ve buna benzer durumlarda many-to-many bir ilişki kullanırız.
Many-to-many ilişkileri gerçekleştirebilmek için verileri birleştirebileceğimiz bir tablo oluşturmamız gerekiyor.
Aşağıda gördüğünüz member tablosu bizim verileri birleştirmek için kullanacağımız tablodur.
Bu tabloda verileri çekeceğimiz tablolardan foreign keyler yer almaktadır.
Şemada da görebileceğiniz gibi many-to-many ilişki iki adet one-to-many ilişkisinin birleşmesi sonucu one-to-many ilişkide many kısmındaki verilerin tek bir tabloda toplanması ile elde edilmektedir.
member tablosundaki course_id alanı course tablosunda bulunan birden fazla course ile ilişki kurabilir. Aynı şekilde member tablosundaki student_id alanı student tablosunda bulunan birden fazla öğrenci ile ilişki kurabilir. Böylelikle 2 adet one-to-many ilişkiyi ortak bir tabloda toplayarak many-to-many ilişki elde etmiş oluruz.

Aşağıdaki şemada ortak tablonun nasıl oluşturulacağı gösterilmiştir.
2 adet foreign key ve 1 adet rol alanından oluşturulmuştur. Eğer rol değeri 1 ise öğretmen 0 ise öğrenci anlamına gelmektedir.
Primary key ise iki kolonun kombinasyonu şeklinde oluşturulmuştur.