Správny návrh SQL databázy a vyhľadávanie

Shakal

Ahojte.
Mám dilemu. Pre vás to bude triviálnosť. Robím to prvý krát a neviem sa správne rozhodnúť. Mám databázu zo zoznamom kníh. Každá kniha spadá nie pod jednu ale viac kategórií. Kategórie majú svoju vlastnú tabuľku a jedinečné ID. Ako správne navrhnúť tabuľku pre knihy?
Premýšľal som nad:

Knihy:

Kód: Vybrať všetko

ID int,
Nazov varchar,
Kategorie varchar

Teda napríklad:

Kód: Vybrať všetko

1;Snehulienka;21,22,25

T.z., Snehulienka spadá do kategórií s ID 21,22,25.

A teraz tá dilema, ako v tom vyhľadávať? Čo ak si dajú vyhľadať podľa kategórie, ktorá ma ID "2".

Dotaz (na ktorý stačí moja inteligencia) by mohol vyzerať nejak takto:

Kód: Vybrať všetko

$kategoria="2";
select * from Knihy where Categories like '%$kategoria%';

Lenže výsledok budú všetky záznamy ktoré obsahujú v kategóriach číslo "2", čo v tomto prípade ale nie je TO ČO CHCEM, nakoľko Snehulienka pod kategóriu 2 nespadá.

Ospravedlňujem sa, netvorím často sql dotazy, určite je na to nejaký lepší vyhľadávací výraz, ktorý poznáte ale ja nie. Alebo celé to urobiť nejak inač? Prosím o pomoc.
Vďaka.

sharky-no

robi sa to cez spojovaciu tabulku books_category, kde budes mat id_book, id_category

teda ak kniha s id = 1 spada do kategorie 1,2 a 3 a kniha s id 2 do kategorie 3, tak potom budes mat v tabulke riadky

Kód: Vybrať všetko

id_book, id_category
1 1
1 2
1 3
2 3

potom uz ked chces vytiahnut vsetky knihy v kategorii 3 tak to spravis cez JOIN

Kód: Vybrať všetko

SELECT books.* FROM books_category JOIN books ON books.id = books_category.id_book WHERE books_category.id_category = 3

a dobrym zvykom/nutnostou je nastavit indexy/cudzie kluce na spojovacej tabulke

Shakal

sharky-no napísal:robi sa to cez spojovaciu tabulku books_category,

To je dobréééé, toto ma nenapadlo.
Ďakujem, moc mi to pomohlo.

Michaelo

Alebo ak pouzivas normalnu DB ako PostgreSQL a nie MySQL, tak mozes kludne vyuzit JSON, havne JSON-B stlpce a nepotrebujes ani vazbovu tabulku.
V praci pomaly prechadzame prave na JSON, kedze ho ma Postgres tak dobe optimalizovany, ze tam takmer nie je rozdiel medzi nim a vazbovou tabulkou.
Jedna nevyhoda jsonu je, ze tam nemozes pouzit FK a kaskadovanie.

jorg22

Povedal si jednu nevyhodu ale nepovedal si ziadnu vyhodu. A ziadnu v tom ani nevidim.

Michaelo

Tak vyhoda je cistejsia DB bez zbytocnych tabuliek, v SQL nepotrebujes zbytocne joiny, skalovatelnost toho jsonu atd.
Samozrejme nieje to nieco, ze musis hned prerobit celu DB na tento styl. Ale ked zacinas s novou DB, alebo robis nove tabulky, tak je IMO lepsie pouzit JSON-B.

jorg22

Pre boha len to nie. Na niektore veci je JSONB format velmi dobry ale standardne weby maju medzi datami rozne zavislosti a vtedy urcite treba pouzit relacny model databazy.
JSONB ti dava obrovsku volnost spolu so zachovanim rychlosti relacnych databaz. Ale co sa tyka integrity dat tak je to uplny nezmysel.

Michaelo potom sa po roku alebo dvoch ozvi ako sa vam dari ked sa vam tabulky naplnia a vymeni sa zopar programatorov. Ak je pravda, ze vsetko menite za JSONB tak budete mat taky zhluk nepouzitelnych dat ze ich mozete akurat tak vyhodit.

Ten cas co usetris deklarovanim novych poli, ich typov a zavislosti sa ti vypomsti vo forme nekonzistentnosti dat. Spravne navrhnuta relacna databaza vedie aj programatora, ktory s nou bude pracovat spravnym smerom. Ked das programatorovy JSONB tak nielenze zo struktury sa nic nedozvie o datovych typoch, ale bude si tam moct bezproblemovo pridavat svoj vlastny bordel. Vsak preco nie ked moze?
A o zavislostiach uz ani nehovorim.

Takze to ti vobec nezabezpeci cistejsiu DB. To ti praveze dava moznosti ako si zasvinit dabazu milion roznymi sposobmi.

Michaelo

Tak samozrejme nemozes tam davat nejake blbosti, ale to same plati i o dalsich tabulkach. Rovnako ako mozem pridat nejake data do toho jsonu, tak mozem blbo pridat i nove stlpce do nejakej tabulky kde nepatria.

U nas ten json prave pouzivame na specifickejsie veci ako su data gridy a podobne, kde ukladat ich nejako inak je extremny oser (robili sme to tak dlho, nez sme presli na JSON a tam ma fakt svoje vyhody) a inak vacsinou pouzivame vazobne tabulky, prave kvoli FK a kaskadovaniu, ktore nas system dokaze vycitat zo struktury a vdaka tomu vykonat aj safe delete na vsechny naviazane zaznamy.

Ako hovorim, ten JSON nie je to najlepsie a najdokonalejsie riesenie, ale ak pomenujes stlpec 'task_to_sys_user', tak ti tam programator nema co rvat nejake ine blbosti okrem IDciek sys userov ktori su na ten task naviazani. Mas to iste ako s pomenovanim tabulky, tiez ju pomenujes stylom 'task_to_sys_user' a tam budes mat stlpce 'taskid' a 'sys_userid'...

eMPiko

Sorry, ale json je na toto uplna konina. Co znamena DB bez zbytocnych tabuliek? Tie data tam stale mas, len namiesto toho aby si ich mal v nejakej strukture ich mas len tak hala bala nahadzane v jednom stlpci a dufas ze kazdy developer co sa do tej databazy dostane ti to cele nerozbije. To je akoby si mal v kniznici vsetky knihy nahadzane na jednej hromade a vytesoval si sa ze mas kniznicu bez zbytocnych polic... Json je pomalsi, zabera viac miesta a s jeho pouzitim stracas esencialnu funkcionalitu na ktorej su relacne databazy cele postavene. Predstav si napriklad, ze chces najst vsetky knihy s urcitou kategoriou. V relacnom modeli jednoducha uloha, mas nad vazobnou entitou index a urobis jeden JOIN. S json-mi to nedokazes, musis pri kazdom vyhladavani kazdy jeden json parsovat a pozriet sa co je v nom. To ani nehovorim o tom, ze zatial co prikazy na upravu dat (UPDATE, DELETE, INSERT) su v relacnom modeli uplne primitivne, v jsone to musis cele nejako rozbalovat a prepisovat a iba si tym pridavas robotu navyse. Json na toto jednoznacne NIE.

94jakub

eMPiko napísal:V relacnom modeli jednoducha uloha, mas nad vazobnou entitou index a urobis jeden JOIN. S json-mi to nedokazes, musis pri kazdom vyhladavani kazdy jeden json parsovat a pozriet sa co je v nom. To ani nehovorim o tom, ze zatial co prikazy na upravu dat (UPDATE, DELETE, INSERT) su v relacnom modeli uplne primitivne, v jsone to musis cele nejako rozbalovat a prepisovat a iba si tym pridavas robotu navyse.

V PostgreSQL je jsonb, ktorý práve rieši problémy, ktoré si napísal. Tam sú data ukladané binárne nie ako JSON string. Vtedy môžeš vytvárať normálne indexy aj na atribút z jsonu.
Samozrejme ale treba vedieť, kedy je výhodné to použiť. Nie začať migrovať všetky databázy len preto, že je to nové a cool. A v tomto prípade (možno dokonca školskom) to asi fakt nebude.

Michaelo

Ako pise jakub a ako som spominal ja, vsetky tieto problemy prave JSON-B (znamena JSON Binary) v PostreSQL riesi a nieje pomalsi ako ina tabulka (niekedy je praveze este rychlejsi).
Samozrejme, ako som spominal, nie je to nieco 'wow' ze musis hned prerobit celu DB. My to tiez nerobime. Prerobili sme par tabuliek, kde na to bol realny dovod a ostatne existujuce tabulky zatial ignorujeme, kedze to vacsinou prave nestoji za cas s tym straveny. Preto som spominal, ze u novych tabuliek nevidim velmi dovod preco ho nepouzit, ak ho pouzijes normalne

jorg22

Ja len dodam, ked si vravel o "normalnej DB ako je PostgreSQL" tak MySQL ma od urcitej verzie podporu JSON.
MySQL 8.0 je aktualna verzia a prinasa podporu indexov a roznej dalsej funkcionality pre JSON. Takze uz aj v MySQL vies vyuzit plny potencial ukladania dat v JSON formate.

eMPiko

94jakub napísal: V PostgreSQL je jsonb, ktorý práve rieši problémy, ktoré si napísal. Tam sú data ukladané binárne nie ako JSON string. Vtedy môžeš vytvárať normálne indexy aj na atribút z jsonu.
Samozrejme ale treba vedieť, kedy je výhodné to použiť. Nie začať migrovať všetky databázy len preto, že je to nové a cool. A v tomto prípade (možno dokonca školskom) to asi fakt nebude.

Zle som sa vyjadril, nemusis to robit ty, ale databaza to robit stale musi a trva jej to dlhsie ako jej nativne operacie. Nie som si stale isty, ci sa da v sucasnych SQL databazach zindexovat pole, t.j. aby si mal pre dany riadok (v tomto pripade jeden json s polom hodnot) viac zaznamom vo vyslednom indexe. Myslim si vsak ze nie a preto taky zakladny use case ako "Najdi vsetky knihy z danej kategorie" v tomto pripade napr. indexami urychlit nevies.

Michaelo

Hral si sa niekedy s PostgreSQL? Ten to dokaze a taktiez dokaze robit indexy aj na specificky kluc v jsone, takze mozes mat tych indexov kolko potrebujes.
By sme to nezacali pouzivat len preto, ze to znie cool, ale robili sme testy a vo vacsine pripadov tam ani nebol rozdiel pri porovnani s druhou tabulkou, pricom to znacne zjednodusilo SQL ktore musis v kode volat.

eMPiko

OK, mam takyto json v tabulke {kategorie: [1,2,3]}. Dokazes to zindexovat tak, aby som dokazal rychlo urobit toto: SELECT * FROM tabulke WHERE kategeoria = 1?

Michaelo

Pride mi to hlavne ako celkom blby json. Ked tam mas kategorie, tak ti staci nazvat stlpec kategorie a typ mu dat int[]...
My napriklad ako json ukladame clanky co sa vypisuju na frontende, ale to ma tiez specificky dovod, ktory sa ale netyka DB, ale toho ako ich spracovame na backende pre jednoduchsiu spravu...

Inak, pre indexy na specificke hodnoty klucov: https://stackoverflow.com/questions/360 ... ex-on-json

*****HERO*****

ja len nieco doplnim ku shakalovej otazke, lebo isiel na to zo zaciatku dobre, len to nedomyslel.

Shakal napísal: Dotaz (na ktorý stačí moja inteligencia) by mohol vyzerať nejak takto:
Kód: Vybrať všetko
$kategoria="2";
select * from Knihy where Categories like '%$kategoria%';
Lenže výsledok budú všetky záznamy ktoré obsahujú v kategóriach číslo "2", čo v tomto prípade ale nie je TO ČO CHCEM, nakoľko Snehulienka pod kategóriu 2 nespadá.

keby sa to chcelo riesit takymto stylom, nad akym si zacal rozmyslat (odhliadnuc od performance), uplne by stacilo tie jednotlive kategorie v stlpci Categories vkladat medzi ciarky a podla toho aj vyhladavat.
tabulka by teda vyzerala

Kód: Vybrať všetko

1;Snehulienka;,21,22,25,
2;Trpajzlík;,2,

select:

Kód: Vybrať všetko

$kategoria="2";
select * from Knihy where Categories like '%,$kategoria,%';

pochopitelne vrati iba Trpajzlika

jorg22

Hero to mu prosim ani nerad

Asi najhorsie riesenie ake si mohol zvolit. Tak sa to nerobi a nikto by nemal ani len uvazovat nad takymto riesenim.

*****HERO*****

a myslis si, ze shakal programuje vypocet studenej fuzie? jeho prva tema tykajuca sa SQL je spred 10 rokov, ked sa odvtedy neposunul dalej, lepsie to uz nebude. evidentne je tu uplne bezpredmetne riesit "ako sa to robi", chce riesenie na jeho problem a ci je to obycajne vyhladanie ajdicka v stringu alebo pouzitie neuronovych sieti nakodovanych v JSON-B vyjoinovanych cez MySQL pouzitim indexov z Google bota az do Oracla, je mu podla mna uplne jedno.

okrem toho vsetko zavisi od kontextu, vedel by som ti z praxe vymenovat X prikladov, kedy sa mi toto najhorsie riesenie osvedcilo ovela lepsie ako tie "ako sa to robi"

jorg22

Pisal som to preto, lebo ak sa niekto uci ako sa navrhuje databaza tak asi ziskava vela informacii z okolia a ked sa mu do toho budu miesat aj tie "zle" informacie tak sa to nauci zle.

Ale ked uz si napisal, ze vies vymenovat vela prikladov kde sa to hodi tak ma to zaujima. Nehovorim, ze to nemoze mat prakticke vyuzitie ale ja si ho neviem predstavit tak budem rad ak mi rozsiris obzory. Tak mi skus popisat nejaky kontext v praxi kde sa to hodi?

Ale ako pisem v 99% pripadoch je toto spravne riesenie a musis najprv nad nim uvazovat pokial chces pouzit nejake nestandardne. Bezne sa robi danormalizacia databazy koli ziskaniu vykonu ale to je uz na extremne velkych databazach, s ktorymi sa zaciatocnik nestretne. No a konkretne tento priklad na vykone ani nepridava.

Správny návrh SQL databázy a vyhľadávanie

Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie