8 min read

SQL ile sıfırdan veritabanı tasarlama

Bu yazıda sıfırdan bir mekan veritabanı tasarlıyoruz. UML diyagramı sayesinde veritabanını önce soyut olarak resmedip, ardından sql ifadeleri ile gerçeğe dönüştürüyoruz.
SQL ile sıfırdan veritabanı tasarlama

Daha önceki yazılarda sql dili ile Northwind örnek veritabanında çeşitli sorgular yapmıştık. Bu yazıda sıfırdan bir veritabanı oluşturarak kendi tablolarımızı yazıp bunlar arasında ilişkiler tayin edeceğiz. Sonuçta ortaya PlacesDB adında bir mekan veritabanı ortaya çıkacak. Bu veritabanını ileride diğer yazılarımda da örnekleme amacıyla kullandığımı görebilirsiniz.

Sql bilgilerini tazelemek isteyenler için önceki yazılar:

Yeni başlayanlar için SQL
Bu yazıda yeni başlayanlar veya tekrar yapmak isteyenler için SQL ile çeşitli sorgulama örnekleri yazıyoruz.
Yeni başlayanlar için SQL - 2
Bu yazıda veritabanına yeni kayıt ekleme, kayıt güncelleme ve kayıt silme işlemleri yapıyoruz.
Yeni başlayanlar için SQL - 3
Bu yazıda karmaşık sorgular yazmaya yarayan count, sum, avg, max, group by, exists gibi ifadeleri örneklerle uyguluyoruz.
Yeni başlayanlar için SQL - 4
Bu yazıda sql sorgularında kayıt birleştirme amacıyla kullanılan JOIN ifadesinin çeşitli kullanım şekillerini inceliyoruz.

Daha önceki yazılarımda olduğu gibi ben Mysql Workspace arayüzü kullanarak çalışıyor olacağım. Fakat siz phpmysql gibi farklı bir arayüzden veya sql kodlarını direk terminalden girerek de çalışabilirsiniz.

Hazırsanız başlıyoruz.

1.Planlama

Veritabanını oluşturmaya başlamadan önce ilk adım olarak nasıl bir sistem istediğimizi ortaya dökmemiz gerekiyor. Bunun için çeşitli görselleştirme araçları vb. mevcut. Ama siz dilerseniz kağıt-kalem kullanarak veya beyaz tahtada da aklınızdaki yapıyı ifade edebilirsiniz. Sonuçta istediğimiz hayalimizdeki veritabanını genel hatlarıyla yazıya dökmek. Nereye yazarsanız yazın, UML diyagramı ile net bir şekilde ifade ettikten sonra bunun pek bir önemi yok.

Sorgulama yapmak için sql dili kullandığımız gibi, modelleme için de  UML dediğimiz bir modelleme dili kullanabiliyoruz. UML ile yazdığınız bir veritabanı modelini kime gösterirseniz gösterin, detaylı bir şekilde anlayacaktır. Bu yüzden özellikle veritabanı modelinizi çok sayıda kişiyle paylaşmak, üzerinde beraber çalışmak istiyorsanız UML kullanmanızda fayda var.

UML'e geçmeden önce PlacesDB'yi genel hatlarıyla tanımlayalım:

  • Mekanlar saklanacak
  • Mekanlar kategorilere ayrılacak
  • Mekanların bulunduğu şehirler de saklanacak
  • Kategori ve il kriterlerine göre sorgulamak çok kolay olacak

Buradan yola çıkarak hemen şu modeller göze çarpıyor:

  • Mekan
  • Kategori
  • Şehir

1a.UML Diyagramı ile tabloları oluşturma

Mysql workspace'de File->New Model->Add Diagram seçerek bir boş sayfa açıyoruz. Bu sayfaya üç adet tablo koyup aklımızdaki özellikleri ilave edelim.

mekan, kategori ve şehir tablolarını oluşturarak her birinin alanlarını modelliyoruz

Sarı renkli olan kutucukta gördüğünüz gibi, çeşitli bilgi notları da ilave edebiliyoruz.

1b.Foreign key ile tabloları bağlama

Şimdi sıra geldi tablolar arasındaki ilişkileri belirlemeye. Tablo ilişkileri genelde 1-to-1, 1-to-many, many-to-1, many-to-many şeklinde tanımlanır. Bunlarla aslında bir tablodaki kaydın karşılığında eşleşeceği birden fazla kayıt olup olamayacağını anlatmak isteriz.

Bu örnekte bir mekan sadece bir kategoriye ait olabilir ve bir şehirde bulunabilir. Fakat karşı tarafa baktığımızda, bir kategoride bir çok mekan ve yine bir şehirde birçok mekan olabilir. Buna many-to-1 ilişkisi diyoruz.

Bunları foreign key'lerle, yani hedef tablo adı ve o tablodaki ilgili alanın adıyla referans anahtarı vererek netleştirelim.

Peki, foreign key'i istediğim alana verebilir miyim?  Hayır. Foreign key ile referans verdiğim alanın o tablonun primary key'i olması veya unique constraint ile başka kayıtlarda tekrarını engellenmiş olmam gerekir.
Foreign key ile şehir ve kategori tablolarına birer referans verilmesi

Bu tür diyagramlarınızı resim olarak kaydedip saklayabilirsiniz. Özellikle dokümantasyon için çok kullanışlı olacaktır. File->Export->Export as PNG seçerek diyagramınızı PNG formatında bir resim olarak kaydedebilirsiniz.

Model tabına geri dönerek, Schema kısmından veritabanına isim verip Collation yani yazım dili için uygun karakterleri tanımlayalım. Ben burada veritabanı adı olarak placesdb, karakterler için de da utf8mb4 ve utf8mb4_unicode_ci seçiyorum. Comments kısmında veritabanı ile ilgili bilgi notu da ilave edebilirsiniz.

Model tabında Schema yani veritabanının genel tanımının yapılması

1c.UML diyagramından CREATE SCRIPT oluşturma

Artık veritabanını oluşturabiliriz. File->Export->"Forward engineer CREATE script" seçerek bu veritabanını oluşturacak olan sql scriptini otomatik olarak hazırlatıyoruz.

Veritabanı oluşturma scriptinin modelden otomatik olarak oluşturulması

Scriptin tümünü aşağıda görebilirsiniz:

-- MySQL Script generated by MySQL Workbench
-- Wed Jan 13 13:36:33 2021
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema placesdb
-- -----------------------------------------------------
-- Places DB stores popular places with appropriate categories and the cities they're in.

-- -----------------------------------------------------
-- Schema placesdb
--
-- Places DB stores popular places with appropriate categories and the cities they're in.
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `placesdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
USE `placesdb` ;

-- -----------------------------------------------------
-- Table `placesdb`.`city`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `placesdb`.`city` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `placesdb`.`category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `placesdb`.`category` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `placesdb`.`place`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `placesdb`.`place` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NULL,
  `categoryId` INT NULL,
  `cityId` INT NULL,
  `details` LONGTEXT NULL,
  `dateCreated` DATETIME NULL DEFAULT NOW(),
  `dateModified` DATETIME NULL DEFAULT NOW(),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_city_id`
    FOREIGN KEY (`id`)
    REFERENCES `placesdb`.`city` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_category_id`
    FOREIGN KEY (`id`)
    REFERENCES `placesdb`.`category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
PlacesDB veritabanı oluşturma SQL scripti

2.Uygulama : CREATE SCRIPT'i çalıştırma

Veritabanı oluşturma scriptinizi bir .sql dosyasına kaydedebilir, veya şimdilik sadece kopyalayabilirsiniz. File->New Query Tab ile yeni bir sorgu tabı açıp az önce kopyaladığımız scripti buraya yapıştıralım. Execute ile çalıştırdığımızda modellediğimiz veritabanı artık gerçeğe dönüşecek.

Veritabanı oluşturuldu

Eğer benim yaptığım şekilde bir arayüz kullanmayacaksanız, direk sql CREATE ifadeleri yazarak de veritabanını oluşturmanız mümkün. Ben bu yazıda yazım hatası yapmamak, bazı önemli detayları unutmamak adına bu şekilde UML diyagramından script oluşturarak ilerlemeyi tercih ettim.

3.Analiz: Bu CREATE SCRIPT'de neler var?

Bu noktaya kadar geldiğimize göre, biraz yavaşlayalım ve bizim için otomatik olarak oluşturulan CREATE scriptinin içeriğini detaylı bir şekilde inceleyelim.

3a.Create Schema yani veritabanı oluşturma

Öncelikle veritabanı oluşturmak için CREATE SCHEMA IF EXISTS diyerek isim ve karakter seti tanımlıyoruz.

CREATE SCHEMA IF NOT EXISTS `placesdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

Sonra USE ifadesi ile bu script'te hep bu veritabanı üzerinde işlem yapacağımızı belirtiyoruz. Böylece tekrar tekrar veritabanı ismi belirtmeye gerek kalmıyor.

USE `placesdb` ;

3b.Create Table yani tablo oluşturma

Ardından CREATE TABLE ifadeleri ile her tablonun adı ve alanlarını sıralıyoruz. Foreign key ile referans verilecek tabloları önceden tanımlayalım.

Şehir tablosunda sıra numarası ve isim bulunacak. Ayrıca:

  • Tablodaki "id" alanı PRIMARY KEY yani esas anahtar olarak kullanılacak.
  • NOT NULL ile id alanına boş değer verilmesini baştan engelliyoruz.
  • AUTO_INCREMENT ile veritabanının buraya kendi otomatik olarak artan numaralar vermesini istiyoruz.
CREATE TABLE IF NOT EXISTS `placesdb`.`city` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;
Şehir tablosu

Kategori tablosunda da yine sıra numarası ve isim bulunacak.

CREATE TABLE IF NOT EXISTS `placesdb`.`category` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;
Kategori tablosu

Mekan tablosunda sıra numarası, isim, kategori numarası, şehir numarası, detay bilgisi, oluşturma ve değiştirme tarihleri bulunacak.

Burada DEFAULT NOW() ile veritabanından eğer veri girilmezse o anki tarihi atmasını istiyoruz.

CONSTRAINT ifadeleri şehir ve kategori anahtarlarını açıklıyor. Hangi tablonun hangi alanına referans verildiği, bu alanda silme veya güncelleme yapıldığı taktirde bir şey yapılmasını isteyip istemediğimizi burada netliğe kavuşturuyoruz.

CREATE TABLE IF NOT EXISTS `placesdb`.`place` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NULL,
  `categoryId` INT NULL,
  `cityId` INT NULL,
  `details` LONGTEXT NULL,
  `dateCreated` DATETIME NULL DEFAULT NOW(),
  `dateModified` DATETIME NULL DEFAULT NOW(),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_city_id`
    FOREIGN KEY (`id`)
    REFERENCES `placesdb`.`city` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_category_id`
    FOREIGN KEY (`id`)
    REFERENCES `placesdb`.`category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
Mekan tablosu

Buraya kadar yaptığımız işlemleri takip edebildiyseniz PlacesDB'yi oluşturdunuz demektir. Şimdi veri ekleyerek, güncelleyerek veritabanını doldurabilirsiniz.

4.Veritabanı tablolarına veri ekleme

INSERT ifadesi kullanarak PlacesDB veritabanına şehir, kategori ve mekan örnekleri ekleyelim.

İlk olarak 3 adet şehir kaydı ekleyelim.

INSERT INTO `placesdb`.`city` (`name`) VALUES ('İstanbul');
INSERT INTO `placesdb`.`city` (`name`) VALUES ('Ankara');
INSERT INTO `placesdb`.`city` (`name`) VALUES ('İzmir');

Devam ederek 4 adet kategori kaydı girelim.

INSERT INTO `placesdb`.`category` (`name`) VALUES ('Cafe / Restoran');
INSERT INTO `placesdb`.`category` (`name`) VALUES ('Doğal alanlar');
INSERT INTO `placesdb`.`category` (`name`) VALUES ('Bar / Gece kulübü');
INSERT INTO `placesdb`.`category` (`name`) VALUES ('Oyun / Eğlence parkları');

Son olarak da bu şehirler ve kategorilerden yararlanarak iki adet mekan kaydı girelim.

Not: Ankara'nın kelimesinde bulunan '  karakteri yani tırnak işaretinin burada escape edilerek \' şeklinde yazılmış olduğuna dikkat ediniz.
INSERT INTO `placesdb`.`place` (`name`, `categoryId`, `cityId`, `details`) VALUES ('Cafe Salvador', '1', '2', 'Ankara\'nın en cool mekanlarından biri. Aromalı kahveleri ile hem göze, hem damağa hitap ediyor.');
INSERT INTO `placesdb`.`place` (`name`, `categoryId`, `cityId`, `details`) VALUES ('Bamboo Cafe', '1', '1', 'Her daim taze ve nefis cup cake çeşitleri ile dikkat çekiyor.');

Bu şekilde şehir, kategori ve mekanlar ekleyerek veritabanını daha da zenginleştirebiliriz.

Son söz.

Bu yazıda sql ile sıfırdan hayalimizdeki bir veritabanını modelleyerek hayata geçirdik. Buradaki örnekleri değişik versiyonları ile uygulayarak pratik yapabilir, sql bilginizi ilerletebilirsiniz.

Bir sonraki yazıda bir veritabanında yapısal değişiklikler nasıl yapılır bunları inceleyeceğiz.

Veritabanında yapısal değişiklikler yapma
Bu yazıda bir veritabanında yapısal değişikliklere neden olan yeni tablo oluşturma, tablo yapısı değiştirme ve silme işlemlerini inceliyor ve örneklerle pekiştiriyoruz.