Jak psát kód: Upřednostňujte normalizovanou strukturu databáze
Školení, která pořádám 
Všechny sloupce v tabulce by měly záviset pouze na primárním klíči. Např. následující struktura tabulek to porušuje:
| category |
|---|
| id |
| … |
| article |
|---|
| id |
| category_id |
| … |
| comment |
|---|
| id |
| article_id |
| category_id |
| created |
| … |
Na vině je sloupec comment.category_id, který nezávisí na comment.id ale na comment.article_id. Sloupec jste mohli přidat třeba proto, že vás zajímá nejnovější komentář v každé kategorii. To se v normalizované databázové struktuře zjišťuje dost pomalu. Lepším řešením je v tomto případě podle mě přidat sloupec category.newest_comment_id a aktualizovat ho po vložení komentáře. I když tento sloupec je pochopitelně také denormalizovaný.
Hlavním problémem denormalizované databázové struktury je to, že se stejná data mnohokrát opakují, takže zbytečně zabírají místo. Navíc se můžou stát nekonzistentní. Co když kvůli chybě v aplikaci nebude $comment->article['category_id'] odpovídat $comment['category_id'] (v syntaxi NotORM)? Za autoritativní lze v tomto případě nejspíš považovat article.category_id, ale nemusí to být vždy tak jasné. U sloupce category.newest_comment_id je jasné, že jde pouze o keš, kterou lze kdykoliv přepočítat.
Jak problém vyřešit bez denormalizace? Jde to mazaným návrhem primárních klíčů (znázorňuje je kurzíva):
| category |
|---|
| n |
| … |
| article |
|---|
| category_n |
| n |
| … |
| comment |
|---|
| category_n |
| article_n |
| n |
| created |
| … |
Primárním klíčem v tabulce article je dvojice (category_n, n), takže tuto dvojici můžeme beztrestně použít v tabulce comment pro odkaz na článek, ke kterému komentář patří. Zároveň ale můžeme využít samotný sloupec category_n pro zjištění nejnovějšího komentáře v kategorii. Nejde o úplně běžný návrh, ale má i další přednosti. Kupříkladu si nemůžeme někam poslat samotné article.id a omylem ho zobrazit v nesprávné kategorii (tady by se hodil spíš příklad, kde bychom místo kategorie měli uživatele). Další drobnou výhodou je, že čísla často viditelná třeba v URL jsou příjemně malá. Podle všeho používá tento přístup např. GitHub, kde třeba první bug v každém projektu má číslo 1.
To jsem ale trochu odbočil. Zkrátka se zkuste vyhnout denormalizovaným sloupcům, protože zabírají místo a můžou vést k nekonzistenci. Když už je použijte, tak ať slouží jako zjevná keš.
Přijďte si o tomto tématu popovídat na školení Konfigurace a výkonnost MySQL.
Diskuse
Jóža:
To pak při vkládání nemůžeš využít Auto_increment. Řešíš to poddotazem na nejmenší použité N v dané kategorii nebo jinak?insert into article values (cat_id,(select min(n) where category_n=cat_id)+1,...)
Jakub Vrána 
:
auto_increment jde použít u posledního sloupce primárního klíče: http://dev.mysql.com/doc/mysql/en/example-auto-increment.html.
Jakub Vrána : 
Ondřej Švec :
Dočetl jsem se, že autoincrement na složeném klíči funguje pouze na MyIsam tabulkách. Lze to nějak nasimulovat u InnoDB tabulek?
Ondřej Švec : Jakub Vrána :
Fakt že jo! To jsem si tedy mohl ověřit. Nevím o snadném způsobu, jak to nasimulovat.
Jakub Vrána : 
Pepa:
Normalizace je bezva.
Někdy je ale lepší buď kvúli samotné aplikaci a dolovaní dat, nebo i z důvodů výkonové optimalizace, tabulky různě pokrucovat.
Platí ale, že by to měl člověk vědět s vědomím čeho a jak chce dosáhnout a také měl o databázích nějakou konkrétní a lepší znalost.
Co se týče místa pro data, pak i tohle je asi téma na VELIKOU diskusi.
Nicméně samozřejmě souhlasím s Jakubem. Jen že svět není černobílý :-)
Pepa: Jakub Podhorský:
Jediné s čím nemohu souhlasit, že v prvním případě by mohlo dojít k nekonzistenci dat. Předpokládám, že v dnešní době snad už nikdo nenavrhuje struktury v DB bez cizích klíčů.Jinak s normalizovanou databází je zábava, zvlášť, když máte třeba 7 a více sloupců v primárním klíči :-) to pak ty URL adresy vypadají.
Jakub Vrána :
Bohužel jsem se s databázemi bez cizích klíčů setkal obzvlášť v Americe celkem běžně.
Jakub Vrána : Antonín Faltýnek:
Moje zvědavost zvítězila, mohl by jste prosím nastínit popisovanou situaci se 7 sloupci v primárním klíči? Podobnou věc jsem potkal jen párkrát a vždy se to podařilo zoptimalizovat. Díky.Jakub Podhorský:
PRIMARY KEY (uchazec, semestr, studijni_obor, forma_studia, kolo, predmet, otazka, odpoved)jde o tabulku, kde uchazeč o studium vyplňuje odpovědi na otázky k přijímacímu řízení
Antonín Faltýnek:
Nechci za každou cenu toto řešení rozporovat, ale připadá mi, že na to primární klíč není vhodný. Je nějaký důvod proč neudělat nějaký kratší primární klíč? Jaký engine je použitý?Jakub Podhorský:
Proč by na to PK nebyl vhodný? DB je v tomto případě perfektně normalizovaná a rychlá na jakýkoliv dotaz (nemusíme se totiž dotazovat přes X tabulek, protože řada hodnot je součástí PK).Jedinou nevýhodu to má v nutnosti vložit všechny položky při INSERT (obnáší to trochu více práce programátora), ale na druhou stranu je DB sama o sobě chráněná, protože tam nikdo nemůže vložit blbost (ani při administrativních zákrocích, které bývají dost časté) a to je pro nás velmi důležité.
Upřímně...neznám horší věc, než když jsou data nekonzistentní.
David Grudl:
Mnohasloupcový primární klíč se obvykle nazývá „natural key“, zatímco generovaný identifikátor „surrogate key“. Obojí má své skalní zástupce i odpůrce. Obvykle používám surrogate key, protože záznam se snadno identifikuje jednou proměnnou v aplikaci nebo jedním polem v odkazující tabulce a je neměnný. Pokud se nabízí jednosloupcový přirozený klíč u kterého mám značnou jistotu, že se nebude měnit (např. dvou či třípísmenný kód státu), použiji ten.
Docela pěkně je to rozebrané tady http://www.techrepublic.com/blog/10things/…-primary-key/2362
David Grudl: Jakub Vrána :
Tohle je něco jiného. Máme tady vícesloupcový, ale pořád generovaný primární klíč. Jednoduchý pŕiklad je třeba tohle:
CREATE TABLE comment (
article_id int NOT NULL,
id int NOT NULL auto_increment,
-- ...
PRIMARY KEY (article_id, id)
)
Jakub Vrána : Jakub Podhorský:
Omlouvám se hned za druhý příspěvek, ale zapomněl jsem odpovědět na poslední otázku.Nechápu váš dotaz ohledně použitého enginu? Myslíte engine v MySQL? Používáme PostgreSQL kde se takové "kraviny" nemusí řešit.
Pavel Stehule:
PRIMARY KEY (uchazec, semestr, studijni_obor, forma_studia, kolo, predmet, otazka, odpoved)Tohle je normalizovana tabulka? V kterepak NF?
Jakub Vrána :
Může být. Jde o to, že primárním klíčem třeba v tabulce otazky není (id), ale (predmet, id). Takže samotné (otazka) není cizí klíč, protože tím je (predmet, otazka). Jinými slovy – hodnota sloupce predmet se nedá odvodit z hodnoty sloupce otazka, protože stejnou hodnotu otazka může mít víc otázek u různých předmětů.
Jakub Vrána : Pavel Stehule:
za předpokladu, že by uchazeč, studijní_obor, forma_studia byly cizí klíče do odpovídajících číselníků - tak ano. Minimálně je to ale hodně nešikovně navržené schéma, kde chybí minimálně jedna relace. Jakub Vrána :
Jaká relace chybí?
Jakub Vrána : Pavel Stehule:
Relaci(uchazec, semestr, studijni_obor, forma_studia, kolo, predmet, otazka, odpoved) bych rozdělil na dvěTEST(uchazec, semestr, studijni_obor, forma_studia, kolo) a odpověď(predmet, otazka, odpoved)
Jakub Vrána :
Tyhle relace klidně existovat můžou, ale primárním klíčem v tabulce test je pořád těch pět sloupců, takže když se na ni chci odkázat, tak musím sloupce vyjmenovat všechny.
Jakub Vrána : Jakub Podhorský:
ano tyto relace (dokonce se shodnými názvy :-)) samozřejmě také existují...upřímně odvozovat dobrý/špatný návrh na základě vyjmenování primárních klíčů jedné tabulky není minimálně úplně šťastnéAntonín Faltýnek:
Ano myslem engine MySQL, protoze tam volba engine muze mimo jine byt celkem dulezita pro volbu vhodneho indexu.Jak jsem psal, nehodlam dane reseni za kazdou cenu rozporovat, neznam jeho kontext. Jen podotykam, ze to neni zrovna sikovne na dalsi praci.
Normalizovane to ale neni :-)
Jakub Vrána :
Co je na tom nenormalizovaného? Je důležité si uvědomit, že skoro všechny primární klíče v tomto schématu jsou vícesloupcové. Takže třeba z hodnoty sloupce `otazka` se nedá odvodit hodnota sloupce `predmet`, protože primárním klíčem v tabulce otázek je dvojice (`predmet`, `id`).
Jakub Vrána : Tim:
Neberte to jako námět na flame, ale spíš jako dotaz zvídavého čtenáře. Proč je takový průser nepoužívat FK? Používám MyISAM (někde jsem četl, že MyISAM je rychlejší, zatímco pomalejší InnoDB má transakce), z toho důvodu FK nepoužívám a bez problému jsem se bez nich dosud obešel. Referenční integritu jsem si vždy byl schopen ohlídat sám.Pavel Stehule:
MyISAM je rychlejší pouze v některých operacích a pouze do určité velikosti tabulek. Relativně dobře se hodí jako cache pro jednouživatelské aplikace, pokud z nějakého důvodu nemohu použít InnoDB. Jinak při pádu modifikujícího SQL hrozí nekonzistence databáze, druhak díky pouze table lockům klesá průchodnost v multiuživatelském režimu. Takže ve výsledku může být MyISAM o dost horší - jen záleží, jaký si napíšete benchmark. My jsme např. zjistili, že JOINy MyISAM tabulek jsou extrémně pomalé, pokud se joinují tabulky nad 100MB.Pokud databázi používá pouze jedna aplikace, kterou píše jeden vývojář, tak relativně snadno lze zajistit referenční integritu z aplikace - ale nikdo nesmí přímo modifikovat databázi (upravovat aplikaci). Implementace RI na úrovni databáze jednak řeší práci programátora (navíc cca 20 znaků), druhak se jedná o poslední instanci - nikdo ji neobejde - ani nějaká další aplikace nebo programátor, který se snaží modifikovat databázi z MySQLAdmin a už nemá v hlavě schéma. Implementace referenční integrity v db je mnohem odolnější vůči lidským i sw chybám.
Diskuse je zrušena z důvodu spamu.
© 2005-2024 