Lidsky řečeno
Chceš evidovat kroužky na škole. Napadne tě jedna velká tabulka: žák, jeho třída, název kroužku, jméno vedoucího, jeho e-mail… Jenže když vedoucí změní e-mail, musíš ho opravit v padesáti řádcích, a pokud na jednom zapomeneš, databáze si bude odporovat. Dobrý návrh znamená, že každý údaj je uložený na jednom místě a ostatní tabulky se na něj jen odkazují.
Jak z reality udělat tabulky
Postup, který funguje skoro vždy:
- Najdi věci, o kterých chceš uchovávat údaje (žák, kroužek, vedoucí). Každá se stane tabulkou.
- U každé věci vypiš vlastnosti (jméno, kapacita, e-mail). Z nich jsou sloupce.
- Každé tabulce dej primární klíč (umělé
id). - Urči vztahy mezi tabulkami a vyjádři je cizími klíči.
Tři druhy vztahů
| Vztah | Příklad | Jak se řeší |
|---|---|---|
| 1 : 1 | žák a jeho školní průkaz | cizí klíč s UNIQUE, nebo rovnou jedna tabulka |
| 1 : N | jedna třída, mnoho žáků | cizí klíč na straně „mnoho“ |
| M : N | žák chodí do víc kroužků, kroužek má víc žáků | spojovací tabulka |
Nejčastější je 1 : N. Cizí klíč je vždy na straně N: v tabulce zaci je sloupec trida_id, ne naopak.
Mnoho k mnoha
Cizí klíč do jednoho sloupce nestačí, protože žák patří do víc kroužků. Řešením je třetí tabulka, která obsahuje dvojice:
zaci(id, jmeno) krouzky(id, nazev)
\ /
prihlasky(zak_id, krouzek_id)Každý řádek tabulky prihlasky říká „tenhle žák chodí do tohohle kroužku“. Primární klíč bývá složený ze dvou sloupců, aby se jedna dvojice neopakovala.
Spojovací tabulka v praxi
Tabulky v databázi
Spusť to a uvidíš, že Anna chodí do dvou kroužků a do Programování chodí oba. Zkus přidat INSERT INTO prihlasky VALUES (1, 1); ještě jednou: složený primární klíč duplicitu nepustí.
Normalizace
Normalizace je pojmenování toho, co jsme dělali v úvodu: rozdělit tabulky tak, aby se údaje neopakovaly. Používají se tři pravidla:
- První normální forma: v jedné buňce je jedna hodnota. Žádné „Šachy, Programování“ v jednom sloupci, žádné sloupce
krouzek1,krouzek2,krouzek3. - Druhá normální forma: každý údaj závisí na celém klíči. U spojovací tabulky tedy nepatří jméno žáka k dvojici, ale jen k žákovi.
- Třetí normální forma: údaje nezávisí jeden na druhém mimo klíč. Když znáš
psc, znáš imesto, takže město patří do vlastní tabulky a v adrese stačípsc.
Stručně se to říká: každý údaj závisí na klíči, na celém klíči a na ničem jiném než na klíči.
Student může chodit do víc kroužků a kroužek má víc studentů. Jak to v databázi správně uložíš?
Vztah M : N se vždy rozbíjí na dva vztahy 1 : N přes spojovací tabulku. Čárky v jedné buňce porušují první normální formu.
Tabulka objednavky obsahuje u každé objednávky i jméno, adresu a e-mail zákazníka. Co je na tom špatně?
Opakování údajů vede k nekonzistenci. Do objednávky patří jen zakaznik_id.
V tabulce zaci je sloupec trida_id. Kde je v tomto vztahu cizí klíč?
Jedna třída má mnoho žáků, proto je cizí klíč u žáků.
Úkoly
Spojovací tabulka
Vytvoř tabulku ucastnici se sloupci akce_id a osoba_id (obě celé číslo, povinné) a složeným primárním klíčem z obou sloupců.
Tabulky v databázi
Tabulka na straně „mnoho“
Třída má mnoho žáků. Vytvoř tabulku zaci se sloupci id (celé číslo, primární klíč), jmeno (text, povinný) a trida_id (celé číslo, povinný, cizí klíč na tridy(id)).
Tabulky v databázi
Pod kapotou · Kompozitní klíče, UUID a kdy se vyplatí denormalizace
Složený primární klíč je přirozený u spojovacích tabulek, ale u ostatních se běžně používá jediné umělé id, protože se na něj snadněji odkazuje cizími klíči. Místo číselné řady se v rozsáhlých systémech používají UUID: identifikátor lze vytvořit na klientu bez dotazu do databáze, nehrozí kolize mezi servery a nejde jej odhadnout. Za to platí větší velikostí a pomalejším řazením. Denormalizace (vědomé opakování údajů) se vyplatí v datových skladech a v místech, kde se údaj po uložení nemění: například cena zboží v době objednávky se do položky objednávky kopíruje, aby zpětná změna ceníku nezměnila minulé účty. Opakování tedy není vždy chyba, jen musí být záměr.