Od ER modelu k databázovej schéme

Toto nie je vymyslený protivník - je to tvar, ktorý má tabuľkový hárok, a tabuľkovým hárkom väčšina schém začína. Oplatí sa presne pomenovať, čo je na ňom zle, lebo "nie je normalizovaná" nevysvetľuje nič:

To, čo nasleduje, je modelovanie databázovej schémy v úplne bežnom zmysle: rozhodnúť, aké budú tabuľky, ktorý stĺpec identifikuje riadok a kde smie fakt bývať. Je to krok medzi ER diagramom a DDL, ktoré databáza prijme, a oplatí sa spraviť ho na papieri, lebo každé z týchto rozhodnutí sa draho vracia späť, keď už v tabuľke sú dáta.

1. Nemôžete uložiť zákazníka, ktorý si nič neobjednal. Zákazník existuje len ako stĺpce na objednávke.
2. Oprava e-mailu znamená aktualizovať každý riadok, na ktorom zákazník vystupuje - a keď jeden vynecháte, databáza drží dve rôzne odpovede.
3. Zmazanie poslednej objednávky zmaže zákazníka. Informácia zmizne ako vedľajší účinok niečoho, čo s ňou nesúvisí.
4. product_names obsahuje zoznam. Odteraz každý dopyt, ktorý potrebuje jeden produkt, parsuje reťazec a žiadny index mu nepomôže.
5. total môže nesúhlasiť s položkami, ktorých má byť súčtom.

Tá pätica má mená - vkladacia, aktualizačná a mazacia anomália, opakujúca sa skupina a odvodená hodnota - a normalizácia je jednoducho postup, ktorý ich odstráni.

Všetko ďalšie od toho závisí, takže to prichádza skôr než tabuľky. Primárny kľúč musí byť jedinečný, nikdy null a nikdy sa nemeniť. Tretia podmienka je tá, ktorá vyradí väčšinu kandidátov.

Normálnych foriem je šesť a potrebujete tri. Každá je jediná otázka o tabuľke a odpoveď "nie" vám povie, ktorý stĺpec kam presunúť.

Preveďte cez ne plochú tabuľku. 1NF rozdelí product_names a quantities do tabuľky order_lines, jeden riadok na produkt. 2NF si všimne, že názov a cena produktu závisia len od produktu, takže sa objaví products. 3NF si všimne, že meno a e-mail zákazníka závisia od zákazníka, nie od objednávky, takže sa objaví customers. Štyri tabuľky, čo je práve hlavičkový diagram - a žiadny krok nevyžadoval úsudok, len tú otázku.

Stĺpec total v plochej tabuľke je iný problém než ostatné štyri: nie je zle umiestnený, je nadbytočný. Dá sa vypočítať z položiek objednávky, takže s nimi môže nesúhlasiť - a raz nesúhlasiť bude.

Tri legitímne odpovede v poradí podľa preferencie: počítať ho v dopyte; počítať ho v pohľade; uložiť ho a nechať databázu, nech ho udržiava - generovaný stĺpec, materializovaný pohľad alebo trigger. Legitímne nie je uložiť ho a spoliehať sa, že si aplikačný kód spomenie ho aktualizovať; to je verzia, ktorá produkuje faktúry, ktoré nesedia.

ER model vie vyjadriť nadtyp s podtypmi; relačná databáza takú konštrukciu nemá, takže fyzický model musí vybrať jedno z troch rozložení. Všetky tri sa široko používajú a voľba je skutočným kompromisom.

Hrubé pravidlo: málo podtypov s prevažne spoločnými stĺpcami hovorí pre jednu tabuľku; veľa podtypov s rozchádzajúcimi sa stĺpcami a reálnymi obmedzeniami hovorí pre tabuľku na podtyp; a podtypy, na ktoré sa nikdy nedopytujete spolu, hovoria pre oddelené tabuľky. Diagram tried pre tú istú doménu si dedičnosť obvykle vybral bez toho, aby čokoľvek z tohto riešil, a práve preto tie dva modely tu smú byť odlišné.

Logický ER model sa na DDL mapuje takmer mechanicky, čo je odmena za to, že ste ho nakreslili poriadne:

Dve veci, ktoré vám diagram nepovie a DDL o nich musí rozhodnúť: čo sa stane pri mazaní (CASCADE pri identifikujúcom vzťahu, RESTRICT takmer všade inde) a ktoré stĺpce dostanú indexy nad rámec kľúčov. Oboje sú rozhodnutia o správaní a záťaži, nie o modeli, a oboje sa oplatí zapísať vedľa schémy, nie ich objaviť neskôr v logu pomalých dopytov.

1. Normalizovanie za hranicu užitočnosti. 3NF je cieľ transakčnej schémy. Rozdeliť tabuľku preto, že sa stĺpec možno raz zopakuje, neprinesie nič a bude stáť spojenie navždy.
2. Žiadne cudzie kľúče, "kvôli výkonu". Cena je jedno vyhľadanie v indexe pri zápise; prínosom je, že osirotené riadky sú nemožné. Takmer nikdy nie je to výmena, ktorá sa oplatí.
3. Nullovateľné stĺpce zastupujúce chýbajúcu tabuľku. Šesť stĺpcov, ktoré sú vyplnené len pre jeden druh riadku, je podtyp, ktorý si pýta vlastnú tabuľku.
4. status ako voľný text. Obmedzte ho - kontrolným obmedzením, enumom alebo číselníkovou tabuľkou - inak bude do roka obsahovať shipped, Shipped aj SHIPPED.
5. Časové pečiatky bez časového pásma. Správne presne raz, v jednej kancelárii, do prvého presunu servera.
6. Diagram opustený po prvej migrácii. Diagram schémy, ktorý nesúhlasí s databázou, je horší než žiadny, lebo mu ľudia veria.

Ak symboly kardinality na diagramoch vyššie potrebujú preklad, sú pokryté v notácii vranej nohy; tvar samotného modelu je v ER diagramoch.