Od ER modelu k databázovému schématu

Tohle není vymyšlený protivník - je to tvar, který má tabulkový list, a tabulkovým listem většina schémat začíná. Vyplatí se přesně pojmenovat, co je na něm špatně, protože "není normalizovaná" nevysvětluje nic:

To, co následuje, je modelování databázového schématu v úplně běžném smyslu: rozhodnout, jaké budou tabulky, který sloupec identifikuje řádek a kde smí fakt bydlet. Je to krok mezi ER diagramem a DDL, které databáze přijme, a vyplatí se udělat ho na papíře, protože každé z těchto rozhodnutí se draze vrací zpět, jakmile jsou v tabulce data.

1. Nemůžete uložit zákazníka, který si nic neobjednal. Zákazník existuje jen jako sloupce na objednávce.
2. Oprava e-mailu znamená aktualizovat každý řádek, na kterém zákazník vystupuje - a když jeden vynecháte, databáze drží dvě různé odpovědi.
3. Smazání poslední objednávky smaže zákazníka. Informace zmizí jako vedlejší účinek něčeho, co s ní nesouvisí.
4. product_names obsahuje seznam. Odteď každý dotaz, který potřebuje jeden produkt, parsuje řetězec a žádný index mu nepomůže.
5. total může nesouhlasit s položkami, jejichž má být součtem.

Ta pětice má jména - vkládací, aktualizační a mazací anomálie, opakující se skupina a odvozená hodnota - a normalizace je prostě postup, který je odstraní.

Všechno další na tom závisí, takže to přichází dřív než tabulky. Primární klíč musí být jedinečný, nikdy null a nikdy se neměnit. Třetí podmínka je ta, která vyřadí většinu kandidátů.

Normálních forem je šest a potřebujete tři. Každá je jediná otázka o tabulce a odpověď "ne" vám řekne, který sloupec kam přesunout.

Proveďte přes ně plochou tabulku. 1NF rozdělí product_names a quantities do tabulky order_lines, jeden řádek na produkt. 2NF si všimne, že název a cena produktu závisí jen na produktu, takže se objeví products. 3NF si všimne, že jméno a e-mail zákazníka závisí na zákazníkovi, ne na objednávce, takže se objeví customers. Čtyři tabulky, což je právě titulní diagram - a žádný krok nevyžadoval úsudek, jen tu otázku.

Sloupec total v ploché tabulce je jiný problém než ostatní čtyři: není špatně umístěný, je nadbytečný. Dá se spočítat z položek objednávky, takže s nimi může nesouhlasit - a jednou nesouhlasit bude.

Tři legitimní odpovědi v pořadí podle preference: počítat ho v dotazu; počítat ho v pohledu; uložit ho a nechat databázi, ať ho udržuje - generovaný sloupec, materializovaný pohled nebo trigger. Legitimní není uložit ho a spoléhat, že si aplikační kód vzpomene ho aktualizovat; to je verze, která produkuje faktury, které nesedí.

ER model umí vyjádřit nadtyp s podtypy; relační databáze takovou konstrukci nemá, takže fyzický model musí vybrat jedno ze tří rozložení. Všechna tři se široce používají a volba je skutečným kompromisem.

Hrubé pravidlo: málo podtypů s převážně společnými sloupci mluví pro jednu tabulku; hodně podtypů s rozcházejícími se sloupci a reálnými omezeními mluví pro tabulku na podtyp; a podtypy, na které se nikdy nedotazujete společně, mluví pro oddělené tabulky. Diagram tříd pro tutéž doménu si dědičnost obvykle vybral, aniž by cokoli z tohohle řešil, a právě proto tyhle dva modely tady smějí být odlišné.

Logický ER model se na DDL mapuje téměř mechanicky, což je odměna za to, že jste ho nakreslili pořádně:

Dvě věci, které vám diagram neřekne a DDL o nich musí rozhodnout: co se stane při mazání (CASCADE u identifikujícího vztahu, RESTRICT téměř všude jinde) a které sloupce dostanou indexy nad rámec klíčů. Obojí jsou rozhodnutí o chování a zátěži, ne o modelu, a obojí se vyplatí zapsat vedle schématu, ne je objevit později v logu pomalých dotazů.

1. Normalizování za hranici užitečnosti. 3NF je cíl transakčního schématu. Rozdělit tabulku proto, že se sloupec možná jednou zopakuje, nepřinese nic a bude stát spojení navždy.
2. Žádné cizí klíče, "kvůli výkonu". Cena je jedno vyhledání v indexu při zápisu; přínosem je, že osiřelé řádky jsou nemožné. Skoro nikdy to není výměna, která se vyplatí.
3. Nullovatelné sloupce zastupující chybějící tabulku. Šest sloupců, které jsou vyplněné jen pro jeden druh řádku, je podtyp, který si říká o vlastní tabulku.
4. status jako volný text. Omezte ho - kontrolním omezením, enumem nebo číselníkovou tabulkou - jinak bude do roka obsahovat shipped, Shipped i SHIPPED.
5. Časová razítka bez časové zóny. Správně přesně jednou, v jedné kanceláři, do prvního přesunu serveru.
6. Diagram opuštěný po první migraci. Diagram schématu, který nesouhlasí s databází, je horší než žádný, protože mu lidé věří.

Pokud symboly kardinality na diagramech výše potřebují překlad, jsou pokryté v notaci vraní nohy; tvar samotného modelu je v ER diagramech.