Navrh DB ve 3NF - slozeny klic – MySQL – Fórum – Programujte.com

Ahoj lidi,

Potreboval bych trochu nakopnout s navrhem tabulek v DB, aby to odpovidalo 3 NF.

Chceme vytvorit databaz konektoru.
Konektor se sklada z tzv. plastoveho housingu, ktery ma obecne 1...N dutin (der) a kazda dutina muze byt obecne kompatibilni s 1...N terminaly.

Dutiny u jednoho konektoru se muzou lisit (prakticky by ale nemelo byt vic nez 4 ruzne dutiny)
Da se rict, ze kazdy "typ" dutiny je kompatibilni s nejakou sadou terminalu, obecne 1...N.
V jedne sade muze byt 1...N terminalu.
 
Cilem databaze je, aby bylo mozne vyhledat i vztah, dutina konektoru XYZ je kompatibilni s terminaly A, B, C pripadne s terminaly ze sady X.
 
Priklad:
Najdi vsechny kompatibilni terminaly pro pin1/dutiny1 konektoru XYZ.
 
nebo
 
Najdi vsechny konektory pouizivajici terminaly XYZ.
 
Navrh relaci prikladam v odkazu:
https://plus.google.com/photos/112121770498997352799/albums/6119356842940029329

Mam trosku problem s tabulkou PIN_LAYOUT a jejim klicem. Nemuz v tom byt nejaky skryty zadrhel, ktery by nesplnoval 3NF? Pripadne je tento navrh efektivni? Nenapada me, jak jinak rozlozit tabulky a navrhnout relace.

Dalsi vec co mi vrta hlavou je rozdeleni terminalu na sety. Sety ciste teoreticky nemusim pouzit, ale potom by vse bylo v PIN_LAYOUT tabulce.

Duvod, proc toto resim je, ze se na DB bude pripojovat aplikace, ktera bude vycitat data z DB. Pokud bude DB velmi spatne navrzena, tak se to promitne dal a bude to znamenat spoustu prace navic.

Diky za pripadne odpovedi a pomoc!

OT Nechapu, proc do toho lidi tahaji technicke pojmy. Bud potrebuji tabulku jednu a nebo more tabulek. Nazvat neco treti normalni forma si koleduje o problemy. Normalni je pojem pro vsechno, treti je cislo na dresu fotbalisty, forma se uziva pro odlevani :)

Kdyz navrhujes db, musis se zamyslet nad tim, co je hlavnim produktem

id_produkt
id_material1
id_material2
id_technologie
dalsi_sloupce

Pak mas tabulky, kde je seznam materialu1, materialu2, technologii. Pro ne je hlavni produkt zas mat1, 2, tech.

id_material1
dalsi_sloupce

id_material2
dalsi_sloupce

id_technologie
dalsi_sloupce

Tvuj hlavni produkt je konektor. Ten muze byt vyroben s ruznym poctem pinu, s ruznym rozlozenim pinu.

tab1

id_konektor
id_pinlayout (z tab2)
id_pinset (z tab3)
dalsi sloupce

tab2
id_pinlayout
dalsi sloupce

tab3
id_pinset
id_pinterminal (z tab 4)
dalsi sloupce

tab4
id_pinterminal
dalsi sloupce

Cili si myslim, ze jdes na to dobre. Jen bych si id nazval jako id a pridal jednoslovne jmeno pro tabulku, ke ktere to id patri.

Nejlepsi by bylo, kdyby jsi sem pridal par radku s daty, ktere ti do tech tabulek musi zapadnout. PRipadne to prirovnal k necemu realnemu. S konektory mnoho lidi nepracuje, nezna pojmy. Ale treba databaze adres klientu, muz/zena, tel.cislo, okres (ciselnik/tabulka okresu), obec (ciselnik obci), psc (ciselnik s psc), obor, v tom se spousta lidi orientuje.

Takhle, zkusim ti nastinit, co tu mam treba za db a jak mam tabulky.
Mam db seznam publikaci.
Publikace - nazev, popis, 1-n autoru, typ publikace, zanr, oddeleni, dalsi sloupce (tiskove udaje ISBN, pocet stran)

tab_publikace - id_pub, id_typ, dalsi_sloupce
tab_autor - id_autor, dalsi_sloupce (jmeno, prijmeni, datum narozeni, zeme narozeni, ...)
tab_pub_aut - id_publikace, id_autor
tab_zanr - id_zanr, dalsi_sloupce (scifi/detektivka, popis zanru...)
tab_pub_zan - id_publikace, id_zanr
tab_oddeleni - id_oddeleni, dalsi sloupce (anglicka lit/ceska lit, ...)
tab_pub_odd - id_publikace, id_oddelleni

Pak chces z toho seznam publikaci, tak je to jeden sileny dotaz s mnoha LEFT JOIN.
Pokud se ma vyhledavat, silenost stoupa a resil jsem to nekolika selecty v sobe, jejichz vysledkem je seznam id pro puvodni sileny dotaz s left joiny (20 ruznych prijmeni autoru mi to najde mezi 4000 publikacemi asi za 0.5s; samozrejme, to je extrem, vyhledavani mam omezene na asi 6-8 prijmeni naraz).

Ahoj Petre,

diky za komentare. Proc normalizace? Jako nezkuseny v DB navrhu je to pro me dobre voditko, jak navrhnout tabulky a zaroven se vyvaroval problemum, ze si smazu jeden zaznam a zjistim, ze jsem nesmazal jen dany konektor, ale zaroven s nim spojeny terminal.

Prepsat to do neceho jinym srozumitelne je pro me dost tezke, protoze ja zase nejsem uplne obeznamen s jinymi typy DB, kde figuruji muz/zena, apod. :-)

Plati toto:

Konektor_Franta (ma 10 der, 5 der ctvercovych, 5 der kruhovych)

-> dira1 = ctvercova -> kompatibilni s terminal_ID1

                                -> kompatibilni s terminal_ID2
                                .
                                -> kompatibilni s terminal_IDn
-> dira2 = ctvercova
-> dira3 = kruhova
.
.
-> dira10 = ctvercova
 

V zasade je to strom, ktery se vetvi. Tam je problem, ze ta dira nema svuj identifikator. Ma rozmer, ale nemusi znamenat, ze dira s rozmerem 1x1mm je u konektoru A stejna, jako dira 1x1 u konektoru B, pripadne toto urcit by bylo moc obtizne.

Nejvetsi problem mam asi s tabulkou PIN_LAYOUT (coz je vlastne rozlozeni der). Prijde mi divne, ze bude tabulka:

ID_KONEKTOR, ID_PIN, ID_TERMINAL_SET

a pro kazdou diru bude v tabulce zaznam. Coz pro konektor s dvaceti pinama znamena dvacet polozek. Ale to je asi dane nezkusenosti s databazema. Ono sto polozek konektoru a tomu cca odpovidajicich 1000 polozek PIN_LAYOUT asi neni moc.

Zeptam se takhle, muzu udelat relaci mezi tabulkou KONEKTOR a tabulkou PIN_LAYOUT, kdyz KONEKTOR ma klic pouze ID_KONEKTOR a PIN_LAYOUT ma slozeny klic ze tri polozek?

Databaze maji indexy (klice). Kdyz je ten index dobre udelany, treba pro id radku se pouziva autoincrement a cislo, tak Prohledat milion zaznamu podle klice je brnkacka. Tam je nejaky algoritmus optimalizovany prave na to, ze je kazde cislo jine a ze je to cislo. Treba to ma roztrizene jeste vnitrne do kategorii cisla >500, <500, <250, >250 a tak v 10 krocich dohleda existenci toho cisla. Cili 1000 polozek je pro nej se spravne udelanym indexem bezproblemove.
Kdybys tam ten index nedal (u mysql se musi spesl rici, ze ten sloupec slouzi jako index), tak misto 10 kroku pro kazde cislo musi projit vsech 1000 radku.

tab konektor
nazev, id_layout
"Franta", 3

tab layout
nazev, id_layout, pocet pinu
"Piny pro Franta", 4

tab piny
nazev, id_pin, id_komp, popis
"ctvercova", 2, 5, "ctvercovy kompatibilni s id1"
"kruhova"  , 3, 6, ",,,"
"ctvercova", 4, 7, "ctvercovy kompatibilni s id2"

tab komp
nazev, id_pin, id_komp
"id1 ctvercovy", 5
"id1 kruhovy", 6
"id2 ctvercovy", 7

tab pin_layout
id_layout, id_pin, pocet
4, 2, 2 .
4, 3, 2 ... layout 4 ma 3 ruzne piny, z toho 2 jsou ctvercove, 2 jsou kruhove, 2 jsou jine ctvercove
4, 4, 2 .
5, 2, 6 ]-- laout 5 ma jen jeden druh pinu

Snad jsem to vsechno pokryl :) SQL dotaz, chces jednu velkou tabulku (jen odhadem)...

SELECT
    `a`.*,
    GROUP_CONCAT( CONCAT(`e`.`pocet`,`d`.`nazev`,`e`.`nazev`) SEPARATOR ', ' ) AS piny
FROM
    konektor a
    LEFT JOIN layout b ON b.id_layout=a.id_layout
    LEFT JOIN pin_layout c ON c.id_layout=a.id_layout
    LEFT JOIN piny d ON d.id_pin=c.id_pin
    LEFT JOIN komp e ON e.id_komp=d.id_komp
GROUP BY
    c.id_layout
    d.id_pin
    e.id_komp
-- zgrupujes to podle tech tri hodnot a pak pospojujes sloupce
-- pres concat a radky pres group_concat (snad je to spravne :) )

Kdybys to negrupoval, tak kazda odlisnost udela novy radek. A taky to grupovani neni idealni u rozsahlejsich tabulek, protoze tam kopiruji obsah vsech radku pro kazdou odlisnost. To by se pak muselo resit trochu jinak, ze si vytahnes jen idecka jednim dotazem uvnitr a vnejsim dosadis hodnoty. Tim padem se budou krupovat jen idcka, coz je mene narocne na pamet.
O neco slozitejsi je dotaz na vyhledavani, protoze kazdou vyhledavanou polozku je lepsi resit zvlast sql dotazem, vytahnout id radku a pak to pres grupovani spojit.

http://www.tutorialspoint.com/mysql/mysql-concat-function.htm - co dela CONCAT
http://www.tutorialspoint.com/…y-clause.htm - co dela GROUP BY
GROUP_CONCAT dela totez, co concat jen pro sloupce se stejnym nazvem na jinem radku zgrupovane pres GROUP BY

Chybka. Jasne, ze se to musi grupovat podle id_konektor pro stejne radky :) A nad dotazem, v tabulce konektor, schazi id_konektor.
GROUP BY a.id_konektor
 

Ahoj Petre,

musim ocenit tvoji pomoc, dekuju! Ta DB neni dobre, ale jako nastrel je to rozhodne skvele. :-)

Priklad z praxe:
Konektor_MOLEX_10_pin

Toto je jeden z konektoru, ktery ma 10 dutin/pinu. Jeden z vhodnych pinu muze byt tento:
Terminal_A nebo tento
Terminal_B

Z obrazku je videt, ze vsechny piny nebo lepe receno dutiny jsou stejne. Cili vim, ze pro pin 1 - 10 lze vzdy pouzit Terminal_A nebo Terminal_B.

Jsou ale konektory, ktere muzou mit ruzne dutiny. Viz obrazek. Tam nastava problem s tim, ze pro dutinu 1 - 2 a 9 - 10 lze pouzit zminene terminaly, ale pro dutiny 3 - 8 musim pouzit jiny terminal, napr. Terminal_C.

Kazdy konektor ma sve unikatni ciselne ID. Stejne tak kazdy terminal ma sve unikatni ID.

Jelikoz ja potrebuju vedet, pro jaky pin/dutinu, lze pouzit jaky terminal, musim mit nekde tabulku rozvrzeni pinu s pridelenim terminalu - vlastne namapovani pinu konektoru na terminaly.

Proto jsem uvazoval o necem jako:

Tab Konektor:
ID_Kon, Nazev, Pocet pinu, Druhy Dutin
105, "Frantisek", 10, 1
106, "Frantisek", 8, 2

Tab Terminal:
ID_Terminal, Vyrobce, Povrchova uprava
35, Jana, Stribro
36, Petr, Bez_upravy
37, Ondra, Zlato

Tab Kompatibilni_Terminal (Propojuje konektor s terminaly):
ID_Kon, Pin, ID_Terminal
105, 1, 35
105, 1, 37
105, 2, 35
105, 2, 37

105, 3, 35
.
.
105, 10, 35
105, 10, 37
106, 1, 36
106, 2, 36
106, 3, 37 ..... 106, 6, 37
106, 7, 36
106, 8, 36

Jelikoz tech terminalu, ktere lze pouzit pro jednu dutinu je hodne, tak me napadlo udelat dalsi tabulku Terminal_Group:
ID_Term_Group, ID_Term
1, 35
1, 37
2, 36

A misto abych vypisoval ke kazdemu pinu vsechny terminaly, tak bych uvedl skupinu terminalu, kterou lze pouzit, cimz bych zmensil pocet polozek v tabulce kompatibilni terminal. Vcera jsem neco podobneho zkousel v MySQL, ale nejak se mi to nepodarilo, tak zkusim dneska, jestli zbyde cas. Problem byl v klicich, nebyl jsem schopen propojit tabulku Konektor a Kompatibilni_Terminal, ale to bylo asi tim, ze v tabulce kompatibilni_terminal jsem mel polozky nastavene jako primarni klic. Jelikoz je to vazebni tabulka, tak tam se klic asi nenastavuje, ze? Az dojdu dom, muzu poslat obrazek, co jsem splodil. :-)

http://www.imagehosting.cz/?v=konektoryt.png

Konektor - tabulka konektoru
Housing - definuje rozlozeni pinu. Stejne rozlozeni muze pozivat vice konektoru
Pin - tabulka vsech typu pinu
Housing_Pin - obsah pinu v housingu
Terminal - tabulka terminalu
SadaTerminalu - definuje sadu terminalu, ktere jsou kompatibilni se stejnymi piny. Do sady muze patrit vice terminalu
Pin_Sada - kompatibilita pinu se sadou terminalu

Tak, kdyz posles sql prikazy pro vytvareni tabulek, export z mysql, to by bylo taky fajn.
U tabulek skupin (vazeb, propojeni) samozrejme nesmis nastavit na sloupce autoincrement, kdyz se v nich muzou idecka opakovat. autoincrement je jen pro unikatni id, hlavni tabulky.
Ten napad je spravny, udelat mapu ke kazdemu portu. Naji podlecisla mapy treba 20 cisel pinu, to neni vubec velka zatez. U 1000 kusu pri 50 pinech na konektor uz to je 50x1000 radku, ale jsou tam jen cisla, tak si s tim sql poradi.
Taky muzes usetrit tabulku, kdyz vis, ze treba pocet tech terminalu je max 5, pro jistototu udelas 7 sloupcu ke kazdemu pinu. Nektery pin bude mit vyplneny prvni sloupec, jiny prvnich 5, a pod. Je to trochu neusporne na velikost db, ale usetril bys rychlos pri vykhledavani a mozna by se to i snadneji zapisovalo do db. Jen bys musel pak delat vetsi zasahy, kdyz bys potreboval vic sloupcu.

Jo, ten preklad, jsem prave myslel to, ze mi nejsou jasne pojmy. Udelat analogii s necim mene technickym. Proto jsem zminil knizku, autora, bydliste, pohlavi a pod. Neco z pojmu znam, ale jine nee.
Obrazek od "P" resit nejdu, tam je zas fura pojmu, kterym nerozumim a neni to ani v pekne tabulce ani jako text, abych si to preformatoval. To bych si prave upravil podle tech sql prikazu, co posles, abych si to mohl vytvorit v phpmyadminovi, pripadne hned upravit sql prikazy. tem rozumim :)

prehlednejsi mi prijdou schematka z work bechn, kde je propojeny klic s klicem v druhe tabulce a neni tam naplacane more barev.
http://michaelharris.eu/…orkbench.png

#8 P
Muzes trosku vysvetlit, jak myslis to rozlozeni pinu? Mas pravdu, ze jsou dva housingy, kde kazdy ma po deviti pinech, ale tim to konci.Do kazdeho muze jit jina sada terminalu. Krom toho ja potrebuji presne vedet, do ktere dutiny/pinu jde ktery terminal a to potrebuju vedet pro kazdy pin kazdeho housingu.

Kdyztak muzes slovne popsat ty vazby tabulek? Nejak jsem se ztratil v tech klicich apod...

#9 peter
Ja prave autoincrement asi nechci pouzit, protoze mam to unikatni cislo ID_HOUSING. Nebo vadi MySQL, ze to unikatni cislo neni od 1 - x? Coz je muj pripad, kdy mam polozky s unikatnim cislem, ale cisla jsou cca sedmimistne a podle me nejdou ani za sebou.

Ze nejdou za sebou nevadi.

Pokud neni unikatni cislo, bude to vadit, pokud budes konkretni vyrobek chtit upravovat. Prikaz pro sql je "vyber mi radek, kde id=124". A kdyz tech radku bude vic? Vybere dva. Ok. A ted mas prikaz, "uloz mi upravena data kde id=124". Ted nebude vedet, ktery z radku ma prepsat.

Ze neni 1-n nevadi, ale bude se podle toho pomaleji vyhledavat, protoze ...
0 - 65.000 je 2 znaky
SU-FPF-2014-12345 (univerzita-fakulta-rok-cislostudenta) je 17 znaku
Porovnat 2 znaky 17 pro 100.000 radku uz muze byt znatelne. Ono, sql si to optimalizuje, takze to treba bude jen dvojnasobny rozdil, ale i tak by ti to mohlo casem vadit.

Cili, bylo by dobre tam mit tam ciselny kod. Kdyz je unikatni, je to ok. Kdyz neni autoincrement, nevadi. Nicmene, klidne muzes udelat sloupec s autoincrement a ted kod vyrobku pouzivat jen ve vyjimecnych pripadech. Vyhodou je, ze kod muzes casem prepsat, ale id radku zustane stejne a i vsechna propojeni.

Mimochodem, nektere typy db umi propojit sloupce v db tak, ze ti nedovoli smazat treba pin, pokud jej pouziva nejaky vyroek a neni treba to hlidat na urovni php a dodatecnymi sql dotazy.

Petre super, diky moc za cenne informace! Dneska neco zkusim spachtit, budu si s tim hrat a pripadny vysledek hodim sem. Ani nevim, jak se vytvati nejaky interface, aby potom dotycni mohli v DB vyhledavat apod.

Predpokladam, ze to se naprogramuje v te databazi a v klientovi pro MySQL je uz potom videt, co dotycny s danou databazi muze delat, jak filtrovat apod. Tak snad nebudu zklamany. :-D