Praktiky s prepojenými tabuľkami v databáze

V predošlých kapitolách sme rozdelili základné dáta o používateľovi a jeho telefónnych číslach do dvoch tabuliek. Taktiež sme si vysvetlili, ako v týchto prípadoch vieme takéto dáta naraz vytiahnuť (ak sa k jednému riadku v jednej tabuľke dá priradiť iba jeden riadok z druhej tabuľky) alebo ako ich vieme vytiahnuť na viackrát (v druhej tabuľke pripadá viacej záznamov k jednému riadku z prvej tabuľky). Na tejto prednáške si ukážeme ešte dva prípady, ako sa s takouto štruktúrou zvykne v praxi pracovať, resp. ako sa takáto štruktúra využíva.

V prvom prípade si ukážeme využitie príkazu GROUP BY v súvislosti s príkazom SELECT – JOIN. Vráťme sa k jednej vete z minulej prednášky, kde sme si vysvetlili, ako by sme načítali všetky dáta na viackrát – pomocou viacerých selectov. Povedali sme si, že by sme si prechádzali dáta z tabuľky users a pri každom by sme volali select na tabuľku user_phone_numbers podľa id usera. Pozastavme sa nad slovom každom. Je to naozaj nutné pri každom? Keď momentálne má len zopár používateľov v tabuľke priradené číslo? Ako by sme dosiahli, aby sa volal SELECT len v tom prípade, kedy sa naozaj nachádza aspoň jedno číslo pre USERA v druhej tabuľke?

Tento problém by sme mohli vyriešiť práve príkazom GROUP BY. Ak si pamätáte, tento príkaz združuje riadky dokopy podľa nejakého kľúča – podľa rôznej hodnoty konkrétneho stĺpca. Povedali sme si, že tento príkaz sa veľmi často využíva v kombinácii s príkazom COUNT (pri použití s GROUP BY vracia počet záznamov v danej združenej skupine). Ako to využijeme v našom prípade? Veľmi jednoducho. Pri prvom SELECTe na tabuľku users spravíme join aj na druhú tabuľku s tým, že z nej budeme vyťahovať aj telefónne číslo (stĺpec phone_number) a počet záznamov v druhej tabuľke, resp. vo výsledku SELECT JOINu.

V stĺpci phone_number sa budú zobrazovať tri rôzne hodnoty (prípady). Prvým je, že ak sa v tabuek napravo nenachádza žiadny záznam (vo výsledku je NULL), zobrazí sa NULL ako predtým. To isté platí v prípade, že sa v druhej tabuľke nachádza práve jeden záznam, hodnota tohto záznamu bude zobrazená. V treťom prípade, kedy je záznamov v druhej tabuľke viac, sa po príkaze GROUP BY zobrazí náhodne jedna z týchto hodnôt, čiže iba jedno číslo z viacerých. V druhom stĺpci nám zobrazí počet záznamov vo výsledku SELECTu (s joinom) pri danom id používateľa. Toto všetko docielime zavolaním príkazu GROUP BY na stĺpec id v tabuľke users, čo nám v podstate akokeby zlepí riadky podľa usera dokopy. Príkaz count nám ale vráti číslo, koľko riadkov sa tam zlepilo, takže koľko sa tam v skutočnosti nachádza záznamov v danej tabuľke:

SELECT users.id,users.user_name,users.user_surname,users.age,users.role,user_phone_numbers.phone_number,COUNT(*) FROM users
LEFT JOIN user_phone_numbers ON users.id=user_phone_numbers.id_user
GROUP BY users.id

Ako vidíte vo výsledku, vidíme presne to, čo sme si popísali – ak sa nejaké číslo našlo, tak to vidíme v stĺpci phone_number a počet riadkov, ktoré sa zlepilo dokopy v stĺpci COUNT(*). Tu si ale musíme uvedomiť, že číslo 1 znamená, že tam bol jeden riadok, čiže aj NULL. Ako teda využijeme tento výsledok? Veľmi jednoducho. Kombinácia týchto stĺpcov NULL a 1 hovorí, že pri danom id_usera sa nachádza iba jedno číslo (jeden záznam) a je to NULL. Pri kombinácii 0911 444 999 – 1 (id usera číslo 4) nám výsledok hovorí, že user mal iba jeden záznam a ten obsahuje to dané číslo. Pri týchto prípadoch nemusíme volať žiadny iný SELECT, všetko čo potrebujeme z druhe tabuľky máme už vo výsledku tohto SELECTu. Iba v treťom prípade, napríklad 0918 222 123 a 2 nám to hovorí, že daný používateľ má v druhej tabuľke až 2 záznamy, ale my máme hodnotu iba jedného. V tomto prípade je nám táto hodnota nanič a musíme tu zavolať osobitný select, ktorý nám vytiahne všetky telefónne čísla usera.

Tento príkaz je akokeby to isté ako predtým (bez GROUP), len nám riadky, ktoré sa nachádzali viackrát zlepilo do jedného a COUNT nám vraví, koľko sme ich zlepili. Takto by sme teda v našom systéme zavolali tento SELECT a následne pri prechádzaní všetkých userov by sme osobitný SELECT na vytiahnutie všetkých čísel volali iba tam, kde to naozaj treba. Týmto krokom by sme ušetrili v kóde volanie viac ako polovici krát príkazu SELECT na databázu. Toto je štandardné využívanie príkazov SQL, resp. optimalizovanie (zefektívnenie a šetrenie času – vykonávania zbytočných príkazov veľakrát) kódu programu.

V súvislosti s takouto štruktúrou dát (v dvoch tabuľkách, ktoré spolu súvisia) sa zvykne robiť ešte jedna praktika, ktorá niekedy zefektívni prácu. Zrekapitulujme si náš prípad - máme používateľov, ktorí môžu mať 1 až N telefónnych čísel v druhej tabuľke. Predstavme si situáciu, že by sme v zozname používateľov na našej domovskej stránke chceli zobrazovať aspoň jedno (ak existuje) telefónne číslo, ktoré by sa dalo označiť ako prioritné pre daného usera (aby sa používateľ, ktorý potrebuje rýchlo vidieť aspoň jeden kontakt na usera a nechce sa preklikávať niekam, kde uvidí všetky čísla).

V takýchto prípadoch, kedy v iných tabuľkách máme pod id uložené nejaké iné údaje a následne chceme pre rýchlu potrebu do hlavnej tabuľky nejako aspoň jeden údaj z druhej tabuľky priradiť/zobraziť, zvykne sa vytvoriť stĺpec, ktorý tento údaj obsahuje. V našom prípade by sa ten stĺpec mohol volať napríklad main_phone_number_id (id hlavného telefónneho čísla) a obsahoval by ID poradové číslo z druhej tabuľky. Všimnite si, že do tohto stĺpca nechceme zobrazovať priamo dané číslo. Je dobré ukladať práve ID číslo, pretože ak by prišla požiadavka na zmenu daného čísla, museli by sme ho meniť aj v jednej aj v druhej tabuľke. V takýchto prípadoch sa zvykne za alebo pred názov stĺpca (main_phone_number) pridať info id, aby bolo jasné, že tam ukladáme id číslo a nie konkrétnu hodnotu. Takto ho stačí zmeniť iba v druhej a Id uložené v tabuľke Users bude naň stále odkazovať. Pridajme si teda stĺpec do tabuľky users a následne si zmeníme nejake dáta:

ALTER TABLE users ADD COLUMN main_phone_number_id BIGINT DEFAULT NULL;
UPDATE users SET main_phone_number_id=8 WHERE id=1;
UPDATE users SET main_phone_number_id=10 WHERE id=2;
UPDATE users SET main_phone_number_id=6 WHERE id=4;

Týmito príkazmi sme pridali stĺpec do tabuľky users, ktorý bude obsahovať informáciu, ktoré z čísiel v tabuľke user_phone_numbers je pre daného používateľa prioritné. Takto sa v praxi zvyknú ukladať dáta, resp. prepájať dáta, ktoré sú uložené v nejakej inej tabuľke. Ak by sme mali tabuľky napríklad na mestá (miesto narodenia) alebo na programovacie jazyky (HTML, CSS, PHP...) a chceli by sme pri userovi napríklad zadefinovať miesto narodenia alebo preferovaný programovací jazyk, vyzeralo by to rovnako. Mali by sme stĺpce napríklad birth_city_id a favourite_language_id, ktoré by obsahovali číselnú hodnotu, ktorá by predstavovala id konkrétneho mesta alebo jazyka v daných tabuľkách.

Skrátka je dobrým zvykom, že ak už máme niekde v databáze uložený nejaký údaj (napr. Banská Bystrica), ktorý je pre danú tabuľku kľúčový (tabuľka miest), tak aby sa tento údaj nevyskytoval nikde inde (konkrétne hodnota varchar), ale aby sme sa naň odkazovali cez Idčka.

Posobná situácia by mohla vyzerať takto. Predstavte si, že by sme mali tabuľku users a následne tabuľku languages, v ktorej by pod svojim ID boli uložené programovacie jazyky (HTML, CSS, PHP...). Následne by sme chceli v databáze uložiť informáciu, ktoré jazyky daný používateľ ovláda. Ako vieme, musíme sa najprv spýtať, koľko záznamov môže jeden user obsahovať a odpoveď je 1 až N, pretože môže ovládať jeden, dva, ale aj desať jazykov. Tým pádom nemôžeme túto info ukladať do tabuľky users ani languages, pretože by sme tam museli mať niekoľko stĺpcov typu language_1, language_2, language_3 (v prípade tabuľky users) alebo id_user_1, id_user_2, id_user_3...

V tomto prípade by sme si vytvorili tretiu tabuľku s názvom napríklad user_languages, v ktorej by sme iba ukladali, ktorý user vie aký jazyk. Mala by teda tri stĺpce, kde prvý by bol id tejto tabuľky (id prepojenia – vzťahu user-jazyk), druhý by bol id_user a tretí id_language. Záznamy v tejto tabuľke by mohli vyzerať ako 1-1-3, 2-1-6, 3-2-10 a podobne, kde prvé číslo je iba poradévo a druhé v prvom príklade by znamenalo, že používateľ s id 1 ovláda jazyk s id číslom 3. Druhý príklad by znamenal, že id user 1 ovláda aj jazyk 6 a podobne. Takto sa v praxi zvyknú viazať dáta medzi tabuľkami. V podstate je vždy dôležité, či chceme uchovať jeden údaj (ako main_phone_number) alebo viacero údajov (ako user_languages).