Veri Tabanı Şeması Tasarımı

Giriş
Veri tabanı şeması (DB Schema) hazırlamak için aldığım notlar aşağıda.

Referential Integrity
Bazı projelerde hız için referential integrity 'den var geçiliyor ve hiç foreign key kullanılmıyor. Bu durumda ilişkileri uygulamanın yönetmesi gerekiyor.

Surrogate Key
Açıklaması şöyle. Yani doğal birincil anahtardan (primary key) emin değilsek veya bu değer çok uzunsa, doğal birincil anahtar yerine kullanılabilir

A surrogate key is a form of primary key. There are two competing ideas among database administrators about how primary keys should be structured.

One philosophy is that of "natural keys". This philosophy says that when your data already has an unique identifier, use it.

The other philosophy is that of "surrogate keys". Adherents to this philosophy believe that natural keys are often either not as unique and immutable as you assume them to be, or much longer than required. So you should use an additional ID column as primary key for each table which contains auto-generated values which are guaranteed to be unique, also known as a surrogate key.

Which of those philosophies is correct is besides the point. But fact is that a column which isn't the primary key isn't a surrogate key...

Oracle'da sequence ile yapılır. Örneğin tüm alanlar primary key ise kullanılabilir.

Örnek

Kötü bir surrogate key örneği şöyle

My workplace's database has a pattern that I've not seen before. Every column that is intended to be a key, whether primary or foreign, ends in _SK. This is shorthand for "surrogate key". It appears to be an informal way to tell the developer that said column is safe to use in joins and won't have any type mismatches or unexpected behavior. For example, our table of dates has many columns that represent the date. DATE_PLAIN is the typical SQL DATE variable that shows data ISO style (e.g. 2022-10-30), DATE_VENDOR puts the date in the style that our vendor uses (e.g. 44300... their epoch is weird), and DATE_SK is always an INT that uses the familiar 20221030 format. By reading these column names, the pattern immediately tells the developer that DATE_SK is the one that you want for joins. He who uses either of the other two options in joins will run in to type mismatches and trouble (I learned that the hard way, e.g. our vendor inconsistently stored their dates as INT and DECIMAL).

This strikes me as a remarkably good idea, which raises the question of why I've not seen it before. Is it a known anti-pattern?

Weak Entity
Varlığı bir başka nesneye bağlı olan satırdır. Tanımı şöyledir.

1.  It is existence-dependent on another entity, i.e., 
    it cannot exist without the entity with which it has a relationship.

2.  It inherits at least part of it's primary key from the entity to which 
    it is related. 


    i.e. -> A weak entity's primary key must be a composite key that includes 
       the primary key of the entity on which it is existence-dependent.

Örneğin OrderItem , Order olmadan mevcut olamaz.

OLAP

OLTP vs OLAP yazısına taşıdım