Navrh databazy
Ahoj,
potreboval by som poradit ohladom navrhu sql databazy.
Ide o obchod, doteraz sa tam pouziva suborova databaza. V principe je terajsia struktura rozdelena na hlavicky a obsah. Kazdy subor s hlavickami ma nadvazujuci subor s obsahom. Napr. Hlavicky_prijmov - obsah(polozky) prijmu, Hlavicky_vydajov_na_odberny_list - polozky atd. Takychto dvojic je asi 5. Subory z obsahmi pre vydaj maju takmer rovnaku strukturu. Dalej je tam subor so skladom a dalsie subory s ciselnikmi. Kedysi som zacinal s DBF, takze podobne rozlozenie mi pride take celkom logicke.
Toto by som potreboval preklopit do SQL. Nemusim dodrzat povodnu strukturu, do noveho systemu pojdu len ciselniky. Poziadavky na tej prevadzke su velmi specificke, tovar sa musi sledovat od prijmu az po vydaj, obrazne jogurt ABC s nejakym datumom vyroby moze mat inu predajnu cenu ako rovnaky jogurt ABC s inym datumom vyroby. Tovar sa tiez pouziva na vyrobu ineho tovaru (zo salamy a majonezy spravia salat). Tovar sa vydava na poukazky, kde zakaznik hradi len urcitu cast z ceny (zvysok hradi sponzor) a zaroven si moze zakaznik kupit ten isty tovar na rovnaky pokladnicny blok aj za plnu cenu. Poukazky aj s presnym rozpisom sa fakturuju sponzorovi (rozdiel co zakaznik nezaplatil). Nieco sa vydava na odberne listy a tie sa tiez potom hromadne fakturuju. Niektori zakaznici maju bonusove karty na ktore si zbieraju body a potom dostanu zlavu z ceny atd., atd.
Precital som si nieco o normalizacii databaz. Vychadza mi to ale, ze keby sa to malo vsetko navrhnut podla noriem, bolo by to neskutocne zlozite, neprehladne a rozbite na desiatky tabuliek. Preferujem jednoduchost a prehladnost aj za cenu nedodrzania noriem.
Moje prve uvahy smeruju k tomu, ze by sa vytvorili len dve tabulky pre obsah. Jedna pre prijem, ktora by obsahovala aj zostavajuci pocet kusov a aktualnu predajnu cenu, druha pre vydaj. Z tabulky pre vydaj by odkazovali cudzie kluce do hlaviciek vsetkych druhov vydajov (samostatne tabulky).
Takze prvy nastrel si predstavujem nejako takto:
Tabulka OBSAH_PRIJEM (obsahprijem_id, tovar_id, datum_vyroby, nakupna_cena, mnozstvo, aktualna_predajna_cena, zostatok_na_sklade, prijem_id)
Tabulka PRIJEM (prijem_id, dodavatel_id a dalsie specificke veci pre hlavicku prijmu...)
Tabulka OBSAH_VYDAJ (obsahvydaj_id, typ_vydaja, mnozstvo, cena_predajna, cena_sponzor, cena_zakaznik, obsahprijem_id, poukazka_id, odberny_list_id, spotrebaNaVyrobu_id, pokladn_doklad_id)
typ_vydaja potom moze nadobudat hodnoty [poukazka,odbernyList,beznyPredaj,spotrebaNaVyrobu ,....]
Tabulka POUKAZKA (poukazka_id, ...dalsie specificke veci pre poukazku...)
Tabulka ODBERNY_LIST(odberny_list_id, ...dalsie specificke veci pre odberny list...)
Tabulka SPOTREBA_NA_VYROBU(spotrebaNaVyrobu_id, ...dalsie specificke veci pre spotrebu na vyrobu...)
Tabulka POKLADN_DOKLAD(pokladn_doklad_id, ...dalsie specificke veci pre pokladnicny doklad...)
Napr.ak by riadok v OBSAH_VYDAJ mal typ vydaja hodnotu povedzme 'odberny list', tak by odberny_list_id odkazoval na prislusnu hlavicku a dalsie cudzie kluce by boli null. Ak by v OBSAH_VYDAJ mal typ vydaja hodnotu 'poukazka', tak by poukazka_id a pokladn_doklad_id odkazovali na prislusne hlavicky a zvysne cudzie kluce by opat boli null.
Hned ma ale napada prvy problem. Stava sa, ze sa pri prijme pomylia, dojde nespravny tovar atd. a chcu ho nasledne vymazat. Ak by som do OBSAH_VYDAJ pridal riadok s predajom tovaru a nasledne dalsi riadok ako storno, aj tak by nebolo mozne vymazat polozku z OBSAH_PRIJEM, kedze uz by na nu dva krat odkazoval cudzi kluc z OBSAH_VYDAJ.
Diky ak ste to docitali az sem, napisete na co si dat pozor, co urobit radsej inak a tiez ako...
pokud sis četl o normalizaci, tak jsi jistě narazil na její tři stupně. A taky na to, že normalizace není dogma, ale doporučení.
Z mého pohledu bych se např. zamyslel, zda je nutné mít tabulky řádků na každou operaci (příjem/výdej/prodej/atd.), když to je přece vlastnost tabulky hlavičky - ergo jsi neprovedl správně normalizaci. Z toho ti pak vypadne, že hlavičková tabulka bude mít pole "operace" a na řádcích spojených s určitým záznamem z hlavičkové tabulky už nic takového nepotřebuješ.
Dále, pokud aspiruješ na nějaký systém "skladů", tak bys podle toho měl ty sklady i vytvořit, tj. mít další datové struktury popisující sklad a náležitě každou operaci se skladem zpracovat, kontrolovat (např. kladné množství) a i dokladovat (archiv stavu skladů k určitému datu). Obecně sklad se musí pojmout stylem: "operace počáteční stav" - "operace příjem" (+ sklad) - "operace výdej" (- sklad)
edit: a ještě vidím cenové operace (tj. cenotvorba) - tam opět musíš uvážit, jak postavit data tak, abys byl schopen kdykoli rekonstruovat ceny v dané době. Já bych to třeba řešil časově platnými ceníky pro různé cenové úrovně. Struktura dat je ale závoslá na tom, zda se přeceňují položky v čase postupně, nebo celý sortiment najednou.
edit2: pokladní doklady jsou další "okruh", který bys měl řešit separátně, a to jako "pokladnu".. no z toho mi vychází už malé ERP, ještě přidat CRM modul
...vypadne, že hlavičková tabulka bude mít pole "operace" a na řádcích spojených s určitým záznamem z hlavičkové tabulky už nic takového nepotřebuješ....
To je pravda ak sa spravne rozumieme. Ale potom by som potreboval aj tak dalsie samostatne tabulky v ktorych by boli len specificke veci. Pretoze ak si kupis jogurt na poukazku tak sa tam este do tej poukazky zadava kolko hradi sponzor a kolko plati zakaznik, plus dalsich 5-6 veci, ktore sa vyskytuju len na poukazke a nikde inde. Jedna poukazka moze byt na viac kusov rozneho tovaru. Teda som tym chcel povedat, ze tie specificke veci by boli v hlavickach.
Mozes hodit nejaky priklad ako by si to rozdelil ty?
...a ještě vidím cenové operace (tj. cenotvorba) - tam opět musíš uvážit, jak postavit data tak, abys byl schopen kdykoli rekonstruovat ceny v dané době...
To je problem, ceny asi budem zapisovat priamo. Teoreticky kazdy jeden fyzicky kus moze mat roznu cenu, v praxi ale ma kazda polozka prijmu svoju vlastnu cenu (napr. Jogurt ABC s vyrobnym datumom vcera ma inu cenu ako ten isty jogurt vyrobeny dnes, eviduje sa vyrobna sarza co je zjednodusene datum vyroby). A tiez je mozne kupit ten isty jogurt od roznych dodavatelov za rozne ceny. A ja potrebujem mat evidenciu az po ten datum vyroby (vyrobnu sarzu)+dodavatela. V praxi na regali sa to miesa, ale s tym ja uz nic nespravim, aspon teoreticky to musi v PC sediet.
...no z toho mi vychází už malé ERP, ještě přidat CRM modul
...
Toho sa prave bojim. Nechcel by som z toho vyrobit nejaku obrovsku vec.
Inak diky za nazor a rady!
ad problém tabulek - nepotřebuješ. Můžeš přece všechna potřebná pole nadefinovat "napříč" jednou tabulkou, pro operace, kde nebudou potřeba, jednoduše nebudou vyplněna.
ad ceny - právě proto bys měl zavést sklady s nějakou "frontou" přijatých výrobků a cen, plus nějaký systém zpracování fronty, buď LIFO (zásobník) nebo spíše FIFO (fronta). Pak budeš moci ceny řešit algoritmicky, na základě nákupní ceny.
buď tvoříš funkční (a efektivní) systém, který reflektuje nějaký proces, nebo stvoříš polofunkční bastl, jehož přínos bude skoro čistá nula...
...Můžeš přece všechna potřebná pole nadefinovat "napříč" jednou tabulkou...
Uff, vela veci by sa skutocne takymto navrhom zjednodusilo. Ale ked to tak od oka odhadnem, bolo by to nejakych 30-40 stlpcov. Myslis, ze to nebude vadit pri vykone a tiez pri objeme dat (fyzicky velkost db)? Neviem ci null zabera menej miesta ako zadana hodnota.
ad sklady - vedel by si ma nakopnut ako si to predstavujes vo vazbe na prijem a vydaj? Nejaky mini vzor.
Lebo ja zatial uvazujem o tom ako som pisal na zaciatku, vyuzit obsah prijmu zaroven na sklad.
OBSAH_PRIJEM (obsahprijem_id, tovar_id, datum_vyroby, nakupna_cena, prijate_mnozstvo, aktualna_predajna_cena, zostatok_na_sklade,...). Doteraz sa nejaka extra evidencia cien nerobila, ked sa nieco precenilo nebol o tom ziadny zaznam (teda bol vo vydajoch, ak sa to medzi jednotlivymi preceneniami aj predalo).
Ale rad sa necham poucit ako to spravit lepsie.
Diky!
Na vykonu to urcite vadit nebude. 30 sloupcu neni nic.
Opravdu bys mel mit jednu tabulku jako hlavicku dokladu a dalsi tabulku jako radky dokladu.
V hlavicce spolecne udaje, stav dokladu, ... - normalizace
Sklady = tabulka s aktualnim stavem, tabulky s prijmem, tabulky s vydejem.
Jakou skladovou metodiku uvazujes ohledne cen (prumerne ceny, fifo, pevne ceny,...)? Pokud nechces ve skladu pracovat s cenami, vse se ti podstatne zjednodusuje.
Do skladu prijimas na zaklade prijemky. Ta zapise doklady do souboru prijemek (opet doporucuji hlavicku a radky). Prijem aktualizuje stav skladu. Ten stav je tam skoro nutny, protoze kdybys mel pro kazdou polozku stav dopocitavat, byla by to pro DB velka zatez.
Pak bude existovat soubor vydejek - opet hlavicka a radky. Vydej snizuje stav skladu.
Kazdy prodej bude generovat i vydejku. Pokud jsi schopny proidej a vydeje sloucit, opet si to zjednodusis. Pak v hlavicce dokladu bude indikace - prodej, rucni vydej, objednavka, ktera jeste nebude vyskladnena a nehybe se stavem skladu, ...
Hlavne se zamysli nad vsemi opravami dokladu, ktere mohou nastat. Storno vydeje je pomerne jednoduche. Muzes doklad zrusit, muzes jej v hlavicce oznacit jako stornovany, muzes generovat opacny doklad. Kazda z tech 3 moznosti souvisi se stavem systemu. Napr. kdyz budes rusit doklad z minuleho mesice a sklad jiz mas uzavreny (probehla inventura), musis generovat novy opacny doklad v aktualnim mesici.
Kazdopadne ruseni vydeje jen navysi sklad.
Ruseni prijmu je slozitejsi, protoze v te chvili jiz muzes mit zbozi vydane a prijem zrusit nepujde. Vim, ze logicky je to divne, ale v praxi naprosto bezne.
Ak tomu spravne rozumiem, tak v tvojom navrhu mas prijemky + obsah, vydajky + obsah a sklad. V podstate to je podobne aj v tej suborovej databaze, co je tam teraz.
To co radil touchwood, ak som spravne pochopil by bola len jedna tabulka pre obsah (riadky dokladov) +jedna tabulka pre vsetky hlavicky + a sklad. Preto som sa pytal, ci to nebude poznat na vykone a velkosti db, keby tam boli polia zo vsetkych hlaviciek a vacsina by bola null.
Ja to mam este zlozitejsie v tom, ze chcu robit bordel a v systeme sa to ma tvarit ako poriadok.
Napr. pride zakaznik s poukazkou na 10kg jablk (zakaznik ma platit 2EUR a sponzor 5EUR). A teraz zakaznik chce namiesto jablk pivo. A samozrejme tak, aby to vyslo do tych 5 EUR a neplatil nic. A majitelia obchodu to chcu takto robit. Je to proste katastrofa.
Alebo sa vyda nieco na poukazku, zakaznik zaplatil svoju ciastku a o par dni sa zisti, ze tam ma byt ina cena, lebo inak to sponzor nezaplati. Teraz jednoducho prepisu tu cenu zakaznika a cenu sponzora v povodnom doklade, aby sponzor nic nezistil a potom to samozrejme nesedi s kasou atd. Neviem ako toto robit, aby to vsetko sedelo.
Zda sa, ze mas s opravami dokladov skusenosti, tak ak by si mal nejaky napad bol by som vdacny...
Dalej, ako by si robil vyrobu? Generoval by si dva doklady - jednu vydajku pre spotrebu tovarov na vyrobu a dalsi doklad ako prijemku s vyrobenym vyrobkom? Alebo by si to dal do jedneho dokladu, pouzite suroviny ako minus a vyrobeny tovar ako plus?
A vlasne rovnaka otazka na inventuru. Zakaznik pozaduje (a aj je to logicke), aby cela inventura, to znamena plusy aj minusy boli v jednom doklade. Robis vydaj so zapornym mnozstvom alebo to, co sa na inventure najde do plusu davas do prijemky?
Pokud das do jedne tabulky radky vsech dokladu - prijem, vydej, prodej a budes to rozlisovat priznakem, je to samozrejme mozne.
Ale budes si muset na radku toho dokladu evidovat ruzne ceny. Napr. u skladovych vydejek jednak nakupni cenu a druhak prodejni cenu.
Budes mit doklady, ktere se netykaji prodeje, ale hybou se skladem:
- rucni prijem
- rucni vydej
- prevody mezi sklady (pokud mas vic skladu)
doklady, ktere se tykaji prodeje:
objednavka (nehybe se skladem, maximalne muze vytvaret rezervaci na skladu)
dodaci list (soucasne i skladova vydejka a pak faktura)
reklamace (bude zalezet, zda se zbozi prijme zpet nebo ne)
storno dokladu (pokud je to doklad z minuleho obdobi, pak jde vlastne o zaporny vydej a vraci zbozi do skladu)
...
Je treba si rozmyslet, zda to prinese vyhodu mit vse v jedne tabulce nebo ne.
Ja jsem u tebe na zacatku pochopil, ze mas vice stejnych typu dokladu rozdelenych do ruznych tabulek.
Inventura:
v okamziku inventury si ulozis podklady (opet nova tabulka - hlavicka a radky). Podklady obsahuji zbozi z pocitace, ktere by melo byt ve skladu + sloupec na zadani zjisteneho mnozstvi. Vytisknes papiry, aby mohli obehnout sklad a dopsat kolik ceho maji (nemusi videt mnozstvi z pocitace, aby je to nelakalo...)
Uzivatel provede inventuru a zapise zjistene mnozstvi.
Na zaklade toho pak vygenerujes rozdilovy doklad. Pokud zakaznik pozaduje jeden doklad, napr. prijemku, kde chybejici zbozi bude zaporna prijemka a snizi sklad, klidne to tak muze mit. My generujeme prijemku a vydejku a obe jsou pak prirazeny k inventure.
Doplnene podklady tam pak zustavaji, takze kdykoliv si je muzes znovu vytisknout.
Poukazky:
Pokud ma zakaznik poukazku na 10kg jablek a dostane za to pivo, jsou 2 moznosti, jak to udelat.
V zadnem pripade nesmis dovolit, aby se vydaly jabka v pocitaci a ze skladu dostal pivo. Sklad v pocitaci musi odrazet skutecnost. Jinak nebudes schopny uctovat, drzet stav skladu, delat inventury.
Takze me napadaji 2 moznosti:
1. Zakaznik prijde s poukazkou na 10kg jablek a dostane pivo. Normalne se vyda ze skladu zakaznikovi na doklad za sponzorovane penize pivo.
2. Zakaznik prijde s poukazkou na 10kg jablek a na dokladu sponzorovana dostane jabka. Nasledne se udela rucni doklad, kde se jabka vrati do skladu a vyda se pivo. Takze zakaznik ma na dokladu jabka, tohle vyjede sponzorovi a sklad je taky v poradku, protoze se ruznim dokladem opravil na skutecnost.
Jak jsem psal niz, skladove ceny a ceny prodejni jsou 2 ruzne veci, ktere si ziji vlastnim zivotem. Skladove ceny urcuje prijem, prodejni ceny urcuje smlouva se zakaznikem. Musis evidovat obe ceny. Vydajove ceny se zapisuji na pozadi na zaklade skladu, prodejni ceny jsou z ceniku pro zakaznika.
Pokud se nejake ceny meni v souvislosti se sponzorem, meni se pouze prodejni ceny.
K metodikam:
metodika pevnych cen - nejjednodusii, pokud probehne preceneni, vygeneruje se pouze rozdilovy doklad, ktery se zauctuje. V pripade, kdy nakupujes za ruzne ceny, musis priprijmu generovat a zauctovat rozdil mezi nakyupni a pevnou cenou.
metodika FiFo - jednoducha na udelani, slozita na opravy. Musis mit primou vazbu vydejek na prijemky, musis byt schopny zjistit, kolik z kazde proíjemky zbyva (vydavas z konkretnich vydejek). Problem nastava v pripade zmeny mnozstvi na prijemce, ze ktere se jiz vydavalo. Musis uvolnit mnozstvi, prevest vydeje na dalsi volne prijemky a az pak provest opravu. Znamena to, ze jeden radek na vydejce muze byt vazan k nekolika radkum prijemky.
metodika prumernych cen - ceny prijmu ovlivnuji (meni) aktualni skladovou cenu (tu si drzis v aktualnim stavu skladu). Vydej probiha za aktualni vydajove ceny. Musis mit zachovane poradi prijemek a vydejek. V pripade zmeny prijemky musis prepocitat celý nasledny zbytek skladu. Nekdy se to zjednodusuje offline pristupem - ceny vydeje pocitas az v okamziku uzaverky skladu. Nevyhodou offline je to, ze v prubehu mesice neznas skladove ceny.
Jeste doporucuji mit mesicni stavy skladu - odsouhlasene stavy na konci mesice. Pri vypoctech pak budes vychazet z posledniho stavu. Zrychleni a zafixovani spravnych odsouhlasenych dat.
...pokud probehne preceneni, vygeneruje se pouze rozdilovy doklad, ktery se zauctuje...
To bude asi potrebne len pre pouzitie v uctovnictve. Klient predajne ceny velmi nesleduje, cely sklad sa vedie v nakupnych cenach.
...Jeste doporucuji mit mesicni stavy skladu...
Kde zapisujes tie stavy skladov? Mas na to extra tabulku?
Rad by som tam moznost mat spatne stav skladu mal, lebo teraz maju len aktualny a ak si to na konci mesiaca nevytlacia, tak nemaju nic a obcas je to problem.
Ako zabezpecujes, aby nerobili doklady spatne so starym datumom? Ja to mam bezne, ze potrebuju nieco spatne prijat do stareho mesiaca. Napr.dodavatel doda doklad neskoro a pod. a musi to byt v uctovnictve este v starom mesiaci aj ked sa to do prijmu zadava az dnes.
A co sa tyka cien, nemam ani jednu z tych troch moznosti.
Kazda prijata polozka, t.j. kazdy riadok prijemky si zije svojim zivotom. Prevazne sa rata pevna marza, neskor sa vsak robia akcie atd. A niektore polozky si urcuje ceny priamo dodavatel, takze tam su pevne predajne ceny aj ked nakupne sa mozu lisit.
A tiez vydaj je zmixovany, spravne ma obsluha vyberat zo skladu aj podla datumu spotreby. V praxi sa to nerobi vzdy a budem musiet ja dorobit ako pomocku nejaku automatiku, zrejme FIFO.
K cenam.
Musis rozlisovat skladove ceny (nakupni + vydej v nakupnich cenach na strane skladu) a prodejni ceny, ktere jsou dohodnute se zakaznikem. Tyto 2 ceny jsou na sobe nezavisle, uctuji se na jine ucty a rozdil mezi nimi tvori marzi.
Proto je vhodne mit zvlast skladove doklady a zvlast prodejni doklady, i kdyz to pak je slozitejsi na udrzbu.
Aktualni stav skladu je jedna tabulka, ve ktere se pro kazdy sklad a polozku drzi aktualni mnozstvi a cena. Jakykoliv prijem nebo vydej tabulku aktualizuje.
Mesicni stav skladu je obdoba aktualniho stavu dkladu, doplnena pouze o obdobi. Jakmiule se provede uzaverka skladu. Uzaverka se obvykle provadi nekolik dnu po zacatku mesice, az probehnou inventury, dodelaji se vsechny doklady, tak dopocitas stav skladu na konci mesice (aktualni stav - pohyby po konci mesice) a ulozis.
Jakmile je sklad uzavreny, system neumozni poridit doklady do uzavreneho obdobi.
Musis rozlisovat dodavku od dodavatele, ktera prijde ve starem mesici a fakturu na tu dodavku, kterou dodavatel doda klidne se 14 dennim zpozdenim. Spravne bys to do skladu mel dostat do minuleho mesice za obvykle ceny a rozdil, ktery pripadne vznikne mezi fakturou a dodavkou zauctovat jako vicenaklady.
Podle toho kde a v jaké databázi. Základní pravidlo zní, že sloupce, kde je velká pravděpodobnost mají být na konci. Navím jakou uvažuješ databázi, ale třebas NULL v posledních sloupcích tabulky na Oracle nezabírá nic. Uprostřed to zabírá overhead na separaci sloupcu + znak null.
Bude to pre MS SQL. Trochu som googlil a udajne null nezabera skoro nic pre varchar, avsak vraj zabera plnu dlzku pre datove typy pevnej dlzky ako string[10] alebo int.
Dakujem vsetkym za reakcie a rady.
P.S. neviete co sa stalo s diskusiou databazy na builder.cz? Uz dlhsie som tam nebol a nejako to tam uz nezije...