0 dní v řadě · 0 bodů

SQL · 20 min

Návrh databáze a vztahy

Jak z reality udělat tabulky. Vztahy 1 : N a M : N, spojovací tabulka, normalizace a běžné chyby při návrhu.

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:

  1. Najdi věci, o kterých chceš uchovávat údaje (žák, kroužek, vedoucí). Každá se stane tabulkou.
  2. U každé věci vypiš vlastnosti (jméno, kapacita, e-mail). Z nich jsou sloupce.
  3. Každé tabulce dej primární klíč (umělé id).
  4. Urči vztahy mezi tabulkami a vyjádři je cizími klíči.

Tři druhy vztahů

VztahPříkladJak se řeší
1 : 1žák a jeho školní průkazcizí klíč s UNIQUE, nebo rovnou jedna tabulka
1 : Njedna 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:

text
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.

Vyzkoušej

Spojovací tabulka v praxi

Tabulky v databázi
Načítám…

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:

  1. První normální forma: v jedné buňce je jedna hodnota. Žádné „Šachy, Programování“ v jednom sloupci, žádné sloupce krouzek1, krouzek2, krouzek3.
  2. 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.
  3. Třetí normální forma: údaje nezávisí jeden na druhém mimo klíč. Když znáš psc, znáš i mesto, 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.

Kvíz

Student může chodit do víc kroužků a kroužek má víc studentů. Jak to v databázi správně uložíš?

Kvíz

Tabulka objednavky obsahuje u každé objednávky i jméno, adresu a e-mail zákazníka. Co je na tom špatně?

Kvíz

V tabulce zaci je sloupec trida_id. Kde je v tomto vztahu cizí klíč?

Úkoly

Úkol

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
Načítám…
Úkol

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
Načítám…
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.