ARCHIV

MySQL (23) - relace 1:N a N:N

Co znamenají zkratky 1:N a N:N? Jak souvisejí se psaním spojení a proč by nestačilo mít jen jeden typ spojení? To všechno se dozvíte v dnešním díle seriálu o MySQL.

3.6.2005 07:00 | Petr Zajíc | Články autora | přečteno 57007×

Zatím jsme v seriálu rozebírali taková spojení, při nichž šlo o dvě tabulky. Napsal jsem ale rovněž, že principielně lze spojit i více tabulek. Ačkoli to nepředstavuje žádný syntaktický zádrhel, pro začátečníky v oblasti databází bývá někdy problém pochopit logiku takových spojení. Proto se jim dnes budeme trochu věnovat.

Spojení 1:N

Toto spojení jsme vlastně již probrali. Tajemnou zkratkou "1:N" se v databázovém světě rozumí spojení, kde jeden záznam v "hlavní" tabulce obsahuje teoreticky libovolný počet odpovídajících záznamů v "podřízené" tabulce. (Teoreticky libovolný počet zahrnuje rovněž nulu). Připomeňme také, že k vyjádření takového spojení se používá:

• klauzule LEFT JOIN (nebo RIGHT JOIN) v případě, kdy chceme zobrazit všechny řádky z "hlavní" tabulky bez ohledu na to, zda existují nějaké řádky v "podřízené" tabulce
• klauzule INNER JOIN v případě, kdy chceme zobrazit pouze řádky v "hlavní" tabulce, které mají nějaké související záznamy v tabulce "podřízené"

V praxi existuje celá řada situací, na níž lze toto schéma uplatnit. Pro osvěžení si některá připomeňme:

• Jedna faktura může mít celou řadu položek ("hlavní" tabulka obsahuje záhlaví faktury, "podřízená" obsahuje položky)
• Jedna síť může obsahovat řadu počítačů ("hlavní" tabulka obsahuje seznam sítí, "podřízená" obsahuje seznam počítačů v těchto sítích)
• Jeden zaměstnanec se může starat o řadu zákazníků ("hlavní" tabulka obsahuje seznam zaměstnanců, "podřízená" obsahuje seznam zákazníků)

Celá situace se dá poměrně snadno znázornit graficky. Na obrázku níže můžete vidět, jak by vyladalo spojení mezi tabulkou faktur a jejich položek.

Kdybychom něco takového chtěli zapsat pomocí SQL, nebude to problém:

Spojení N:N

Co je špatného na modelu 1:N, že potřebujeme vymyšlet něco dalšího? Na modelu samotném není špatného nic. Problém spočívá v tom, že pro reálný svět tento model zkrátka nepostačuje. Vraťme se na chvíli k naší imaginární databázové aplikaci, která má mapovat provoz v knihovně. Jak byste zmapovali proces půjčování knih? Je pravda, že jeden čtenář si může půjčit více knih, ALE zároveň je pravda, že více čtenářů si může půjčit stejnou knihu (dejme tomu v různém období). Vztah mezi knihami a čtenáři tedy není 1:N, ale N:N.

Realizovat něco takového pomocí dvou tabulek v reálné databázi většinou nejde. Takový vztah se obyčejně programuje pomocí další, třetí tabulky, která je spojena s oběma původními tabulkami. S každou tabulkou má vztah 1:N a celé to dokáže simulovat spojení N:N. Protože se to asi dá jen těžko představit, posloužím zase malým obrázkem:

Je dobré si "prostřední" tabulku nějak smysluplně nazvat, i když to někdy nebude tak úplně jednoduché. My bychom si ji mohli nazvat například "výpůjčky". Z toho je krásně vidět, jak je relace N:N "rozbita" na dvě relace 1:N. Platí totiž:

• mnoho různých čtenářů si může půjčit mnoho různých knih (N:N)
• jedna kniha může být půjčena mnohokrát (1:N mezi knihami a výpůjčkami)
• jeden čtenář může přijít do knihovny vícekrát za život a něco si půjčit (1:N mezi čtenáři a výpůjčkami)

V praxi to často bývá tak, že "prostřední" tabulka neslouží pouze jako technický prostředek k realizaci spojení N:N, ale že obsahuje i nějaké smysluplné informace. Například si dokážu představit, že by tabulka výpůjček docela dobře mohla obsahovat datum půjčení a datum vrácení každé publikace. To totiž nepatří logicky ani do tabulky knih, ani do tabulky čtenářů.

Pozn.: Vidíte mimochodem, jak nesmírně je toto řešení pružné? Umožňuje například postihnout situaci, kdy čtenář nevrátí všechny knihy, které má půjčené ve stejném termínu. Protože pro každou kombinaci kniha-čtenář existuje řádek v tabulce výpůjček, lze to pohodlně zapsat.

V praxi bychom tedy mohli pomocí výše uvedené průpravy například sestavit seznam právě vypůjčených knih:

Poznámky ke spojením

Zejména spojení N:N vyžadují určitou představivost ze strany programátora. Během let, kdy jsem programoval databázové aplikace, jsem si sestavil několik tipů, které se vám možná také budou hodit:

• Změnit relaci 1:N na N:N v pozdější fázi vývoje může být docela problém. Je lepší pečlivě si promyslet návrh databáze, než ji začnete tvořit.
• Mnoho spojení N:N samozřejmě znamená zvýšení obemu dat v databázi a zároveň snížení výkonu. Umožňuje však ukládat data pružně a do určité míry reagovat na změny v logice uložení dat. Výhody a nevýhody jsou jak už to bývá protichůdné, takže je třeba pečlivě vybírat.
• Jestliže nevíte, zda bude mezi dvěma tabulkami vztah 1:N nebo N:N, stop! Je to důležité a obyčejně je to třeba hned promyslet. Leckdy se problém dá převést z počítačové řeči do jazyka, jimž mluví zákazník (třeba čeština ;-)))) a zeptat se.
• Hotová katastrofa vznikne, když se programátor snaží pomocí 1:N a N:N relací popsat procesy, jejichž toku dostatečně nerozumí (může se jednat například o aplikaci skladového hospodářství se složitým způsobem kompletace výrobků). Nezřídka je pak nutné celý návrh zahodit a začít znovu, pozor na to.
• Jestliže máte duplicitní údaje ve dvou tabulkách, které spolu jsou v relaci 1:N, možná že měly být v relaci N:N. Možná je čas to předělat, i když to bude složité. Do budoucna to může ušetřit čas, výkon a náklady.
  

Verze pro tisk