Database ontwerp

[jelmervos]

Ik had even een probleempje.

Stel je moet een database maken voor een bedrijf om te factureren. Wat erin moet zijn Klanten, Artikelen, Facturen.

Je kan een factuur samenstellen door een klant te kiezen en een aantal artikelen (1 of meer). Dit is niet zo moeilijk. Maar nu wil je per artikel op de factuur de eigenschappen kunnen veranderen, zoals de prijs e.d. Immers, de ene klant kan meer betalen voor een artikel dan een andere.

Hoe zit deze database er dan precies uit zonder al te veel redundantie. Wat zouden jullie er van maken?

[Nielsz]

table redundant:

klantid
artikelid
prijs
andere gegevens.

[jelmervos]

Moet daar niet factuur bij?

[Nielsz]

Op dinsdag 04 december 2001 15:44 schreef jelmervos het volgende:
Moet daar niet factuur bij?

Kan, als je 'm op woensdag 10 gulden meer wil laten betalen
Maar verder regel je het per klant denk ik, en niet per factuur.

[dusty]

Nielsz wordt al steeds beter.. natuurlijk is hij per ongeluk vergeten dat je de orderid er ook bij wilt hebben. (immers wil je precies weten bij welke order die item hoort..)

[Nielsz]

Op dinsdag 04 december 2001 15:45 schreef dusty het volgende:
Nielsz wordt al steeds beter.. natuurlijk is hij per ongeluk vergeten dat je de orderid er ook bij wilt hebben. (immers wil je precies weten bij welke order die item hoort..)

maar het gaat toch per klant en niet per order?

[dusty]

Op dinsdag 04 december 2001 15:45 schreef Nielsz het volgende:
Kan, als je 'm op woensdag 10 gulden meer wil laten betalen
Maar verder regel je het per klant denk ik, en niet per factuur.

Dus als ik vandaag geheugen chips bij jou koop voor 75,- kan ik de rest van mijn leven voor dezelfde prijs die chips kopen bij jou ?

Of ga jij dan achteraf facturen sturen omdat de prijs 10,- omhoog is gegaan

[jelmervos]

Op dinsdag 04 december 2001 15:45 schreef dusty het volgende:
Nielsz wordt al steeds beter.. natuurlijk is hij per ongeluk vergeten dat je de orderid er ook bij wilt hebben. (immers wil je precies weten bij welke order die item hoort..)

Idd, dat bedoel ik. Maar op deze manier krijg je dus per oderid meedere records (= meedere artikelen).

[dusty]

Op dinsdag 04 december 2001 15:47 schreef jelmervos het volgende:
Idd, dat bedoel ik. Maar op deze manier krijg je dus per oderid meedere records (= meedere artikelen).

Yup en kan je de gehele order weer samenstellen

[Verwijderd]

misschien:

factuur-artikel-tabel

code:

+------------+------------+----------+
| factuur_id | artikel_id | prijs    |
+------------+------------+----------+

en dan prijs gewoon met de normale prijs vullen en als ie het wil wijzigen zorg je dat die wijziging in de bovenstaande tabel komt... vorkomt ook het probleem bij facturen waar de prijs voor een artikel wordt verhoogd terwijl een klant hem nog voor oude prijs heeft besteld.... tis maar een idtje

[Bpje]

Als ik je goed begrijp wil je een factuur kunnen maken die per klat verschillend is maar is het zaakd at die klant de volgende keer de zelfde prijs betaald. of is het van keer afhankelijk?

Je krijg zo en zo een de volgende tabellen:
product
product_nr, producten, omschrijving en prijs

Pkey: product_nr


klant
klant_nr, adres enz

Pkey: klant_nr


factuur
klant_nr, factuur_nr, datum, opmerking, totaalprijs enz

pkey: klant_nr en factuur_nr

gegevens over teschuiven naar deze tabel en als de omschrijving/prijs veranderd die daar voor in de plaats te schuiven.

factuur regel
factuur_nr, factuur_regel_nr, omschrijving en prijs

Pkey over factuur_nr, factuur_regel_nr
er zijn nog wel ander mogelijk heden maar die krijg je nog

Paul

[Nielsz]

Op dinsdag 04 december 2001 15:46 schreef dusty het volgende:

[..]

Dus als ik vandaag geheugen chips bij jou koop voor 75,- kan ik de rest van mijn leven voor dezelfde prijs die chips kopen bij jou ?

Of ga jij dan achteraf facturen sturen omdat de prijs 10,- omhoog is gegaan

Ik heb nu ff 10 minuten na gedacht hoe ik je nu af ga troeven.
_{Ik heb niets kunnen bedenken..}

[mvt]

Bak met fakturen, teken hokjes om verschillende onderdelen van de vakturen, dit worden je tabellen. Maak een tabel met al je fakturen, opgebouwd uit de primairie keys van alle andere tabellen + identificatie van de faktuur zelf. Als je wilt kan je de prijs van de artikelen in de artikeltabel zetten en een "kortingskolom" maken in de clienten tabel. Of je maakt een crosstab met artikel,client en prijs. Voordeel 1e manier: dynaamische prijzen. Voordeel 2e vaste prijzen

[Verwijderd]

klant: klantid, naam etc.
artikel: artikelnr, artikelomschrijving, prijs etc.
factuurregel: factuurid, artikelnr, aantal, (andere prijs) etc
factuur: factuurid, klantid, datum etc

een factuur heeft meerdere factuurregels; zo kun je dus meerdere artikelen koppelen aan een factuur.

dacht ik.

[raptorix]

Belangrijkste dingen zijn al gezegt, zelf zou ik zeggen hou ook een table met artikelwijzigingen bij kan je makkelijk doen door een trigger te maken op de artikel table en de boel weg te schrijven in history table.

Daarnaast bij factuur regels geen lookups maken met artikeltable, dit kan narigheid opleveren als de artikelen wijzigen of wegvallen(zowiezo beter een delete flag maken en niet fysiek gaan deleten.)

Praktisch is misschien om dagje daar met administratie mee te lopen, scheelt vaak erg domme fouten

[Crazy D]

Prijslijsten? (kortingen per klant per artikel).
Als klantnr-artikelcode in de prijslijst staat, die prijs of korting pakken, anders gewone verkoopprijs. En prijs gewoon opslaan bij de faktuurregels, wat je toch wel moet doen i.v.m. toekomstige prijswijzigingen.
Eventueel prijslijstcode(s) gebruiken, en die prijslijstcode bij de klant opslaan.
Korting % zou ik wel bij de regels opslaan, sowieso omdat dat leuk staat op je faktuur, maar ook omdat je later dan heel makkelijk kan kijken welke kortingen er gegeven zijn (eventueel ook handig i.v.m. verdere financiele afhandeling in de boekhouding)

[edit] raptorix noemt nog wat belangrijke zaken

[Chopper_Rob]

MMmmm, ik kan me vaag herinneren dat ik een paar jaar geleden zo'n vraag in mijn schoolboek tegen kwam...

[jelmervos]

Op dinsdag 04 december 2001 15:54 schreef Bpje het volgende:
Als ik je goed begrijp wil je een factuur kunnen maken die per klat verschillend is maar is het zaakd at die klant de volgende keer de zelfde prijs betaald. of is het van keer afhankelijk?

Per factuur moet men de prijs van een artikel bepalen. Dus kan altijd anders zijn op een factuur.

[Verwijderd]

Dit kan op verschillende manieren gedaan worden, afhankelijk van de functionaliteit welke je wilt.

Voor dit soort klusjes is handige software te krijgen welke je laat spelen met de indeling van je tabellen en de onderliggende relaties (one to one, one to many, many to many etc).

Ikzelf gebruik powerdesigner hiervoor, werkt erg prettig en kan je database ook gelijk voor je aanmaken op de server als je klaar bent.

Het grootste voordeel van dit soort software is dat je je database gelijk goed kunt documenteren (waar is dat veld goed voor?), je automatisch een 'changes-history' hebt, enz.

Goed, dit specifieke probleem..

De oplossing welke ik het meest gezien heb is dmv. een extra kortingstabel voor artikelen.

dus:

tabel: klanten
velden :NAW gegevens + ID nummer

tabel: artikelen
velden: Artikelgegevens, nominale prijs + ID nummer

tabel: facturen:
velden: Klant ID, ArtikelID, faktuurID, prijsafspraakID

tabel: prijsafspraken (oid)
velden: ID, type afspraak, waarde afspraak.

waarbij type afspraak bv. een integer is:
0 = percentuele korting (veld waarde-afspraak bevat dan percentage)
1 = directe korting (veld waarde bevat af te trekken bedrag)
enz enz.

[jelmervos]

Dat van die prijs afspraken is dus helemaal niet nodig.

Men plaatst een artikel op een factuur en bepaald dan de prijs. Dus geen kortingen e.d.

[Verwijderd]

tsja in dat geval lijkt mij de oplossing zoals Batser 'm aandraagt (een factuur bestand welke klant koppelt aan factuurregels) de beste.

[Verwijderd]

Op dinsdag 04 december 2001 16:18 schreef hezik het volgende:
tabel: facturen:
velden: Klant ID, ArtikelID, faktuurID, prijsafspraakID

wanneer je deze constructie hanteert krijg je dus de niet-gewenste redundantie;
Stel klant 1 bestelt voor factuur 1 artikel 1 en 2, dan krijg je dus dat klantnummer 1 per factuur 1 tweemaal opgeslagen wordt, dat wil je niet. ArtikelID moet er dus uit.

Daarom is het handig om een zogenaamde tussentabel te maken wanneer er sprake is van een veel-op-veel relatie. Namelijk, een factuur bevat meerdere artikelen en een artikel kan op meerdere facturen voorkomen. De tussentabel zorgt ervoor dat je een 1-n krijgt tussen klant en factuur, tussen factuur en factuurregel en tussen artikel(artikelID komt in deze tabel telkens eenmaal voor) en factuurregel(artikelID kan meerdere keren voorkomen).

succes ermee.

[Crazy D]

Op dinsdag 04 december 2001 17:31 schreef Batser het volgende:
Daarom is het handig om een zogenaamde tussentabel te maken wanneer er sprake is van een veel-op-veel relatie.

Faktuur-kopgegevens dus eigenlijk. Klantnr, datum, misschien een extra opmerking, eventueel afleveradres, wie 'm heeft ingevoerd (om zomaar ff wat dingen die in me opkomen te noemen)...

[jelmervos]

Dus ongeveer zo:


Klopt dit een beetje?

[Verwijderd]

Bij artikel mag de prijs dan toch nog weg?

[jelmervos]

Op dinsdag 04 december 2001 18:15 schreef hezik het volgende:
Bij artikel mag de prijs dan toch nog weg?

Nee, want er moet wel een standaard prijs zijn. Net als eenheid en omschrijving.

Bij artikel staan de standaard gegevens, maar ze kunnen het bij FactuurRegels altijd bewerken.

[Crazy D]

Op dinsdag 04 december 2001 18:12 schreef jelmervos het volgende:
Dus ongeveer zo:

Ziet er goed uit. Als die gegeven tenminste alles is wat je wilt

[jelmervos]

Op dinsdag 04 december 2001 18:32 schreef CrazyD_at_work het volgende:

[..]

Ziet er goed uit. Als die gegeven tenminste alles is wat je wilt

Kan er zo wat bij pleuren, maar het idee is goed zo.

[Martin Sturm]

Nog een hieraan relaterend vraagje:
Als je nou dus je facturen die in het verleden zijn uitgeschreven in de toekomst weer terug wilt kunnen vinden, en uit je database halen, is het noodzakelijk om alle producten in je database te houden ^toch?, ook al heb je die producten niet meer in je assortiment.
Wordt je database dan niet groot/traag naar verloop van tijd?? (even ervan uitgaande dat je redelijk wat mutaties in je assortiment hebt?).

[Noork]

gebruik anders in de factuurregel een veld met korting en hou bij product de prijs gewoon gehandhaafd

[Verwijderd]

Op dinsdag 04 december 2001 18:12 schreef jelmervos het volgende:
Dus ongeveer zo:
[afbeelding]

Klopt dit een beetje?

Ik zou de eenheid op de factuurregel weglaten, zit iets verpakt per 10 ofzo en iemand wil er maar 1 dan wordt aantal gewoon 0.1, lijkt me als je op de factuur eenheid veranderd dat het bijhouden van de voorraad lastig/onmogelijk is.

[Verwijderd]

Op dinsdag 04 december 2001 18:12 schreef jelmervos het volgende:
Dus ongeveer zo:
[afbeelding]

Klopt dit een beetje?

niet helemaal, je moet nu wel ArtikelID bij factuurregel instoppen, omschrijving en dergelijke kan weg, alles wat bij een artikel hoort en wat niet-veranderlijk is, sla je op in de artikel tabel, een gegeven zoals aantal zet je uiteraard in de factuurregel tabel aangezien die voor elke klant weer anders is. Wat je met die prijs doet; eventueel nog een tussentabel voor verschillende prijzen per artikel.

In ieder geval de omschrijving weer weg uit factuurregel, want die zie ik twee keer staan en volgens mij bedoel je 2 keer dezelfde omschrijving, namelijk die van artikel. Dit is dus redundantie.

Maar verder ziet het er goed uit!

[jelmervos]

Maar wat ik dus dacht is in tabel artikel de standaard gegevens. Zodra er een nieuwe regel komt die gegevens gebruiken voor een artikel waarna deze kan worden gewijzigd (maar dan wel alleen op die factuur natuurlijk).

Daarom zijn er ook geen relaties met de tabel Artikel.

[Verwijderd]

Op dinsdag 04 december 2001 19:44 schreef jelmervos het volgende:
Maar wat ik dus dacht is in tabel artikel de standaard gegevens. Zodra er een nieuwe regel komt die gegevens gebruiken voor een artikel waarna deze kan worden gewijzigd (maar dan wel alleen op die factuur natuurlijk).

Daarom zijn er ook geen relaties met de tabel Artikel.

als je geen relatie legt met de artikel tabel heeft het op zich ook geen zin deze op te nemen. De onderdelen (attributen in db-taal) de veranderen zijn bijv. aantal, dit attribuut sla je op in factuurregel, omschrijving veranderd in principe niet (vaak) dus dit sla je op bij Artikel. Wat je wel nog zou kunnen doen is bijvoorbeeld een extra veld opnemen in de factuuregel tabel waarin je een of andere extra mededeling zet (bijv. iets extra's over omschrijving).

De basis van je denken is goed, maar ik zou eens een goed boek zoeken over database-modellering. Een goede manier om te beginnen is het leren van "normaliseren". Het valt wat buiten de scope van deze thread om het hier uit te leggen dus, zoek een boek hierover. Een boek wat 'vroeger' bij ons op de opleiding gebruikt werd is "programmeren in Dbase IV" of anders is "Gegevensbanken, een inleiding" van H.Blanken en C.J.Date ook een goede.

[Verwijderd]

Op dinsdag 04 december 2001 18:12 schreef jelmervos het volgende:
Dus ongeveer zo:
Klopt dit een beetje?

Ziet er als basis goed uit, maar 't is nog niet voldoende...
Wel als de klant genoegen neemt met een stand alone factureer module die niks anders doet dan dat, maar die klanten ben ik nog niet tegengekomen.

Na een tijdje wil 'ie koppelen met z'n administratiepakket, en dan krijg je te maken met omzetgegevens (grootboeknummers, enz. en 1 artikel kan meerdere grootboeknummers hebben, bv. een deel 'omzet hardware' en een deel 'service after sales'), met 'accounts receivable' (aanmaningen, kredietbeperking, etc.), en met leuke dingen als BTW-afdrachten verantwoorden en zo.

Wanneer 't geen grote/belangrijke klant is, zou ik 'm verwijzen naar een leuk en betaalbaar factureer-pakket dat gewoon in de winkel te koop is.

Maar de basis van je datamodel klopt wel ongeveer, ook al is 'ie nogal beperkt.

[Verwijderd]

Op dinsdag 04 december 2001 20:44 schreef Batser het volgende:
als je geen relatie legt met de artikel tabel heeft het op zich ook geen zin deze op te nemen.

Tenzij deze waarden als default worden voorgesteld aan de gebruiker. Dat gebeurt dan client side, en dan hoeft geen relatie in het database ontwerp worden opgenomen. (Ik ken tenminste geen relatie "is standaard dat, maar kan straks iets heel anders zijn".)

De basis van je denken is goed, maar ik zou eens een goed boek zoeken over database-modellering. Een goede manier om te beginnen is het leren van "normaliseren".

Je zegt 't goed: "een goede manier om te beginnen". Normaliseren is een goede basis, maar niet zaligmakend, en soms zelfs contra-productief. Maar op 't moment dat je ervan afwijkt, moet je wel akelig zeker zijn van de redenen om niet te normaliseren.

[Crazy D]

Op dinsdag 04 december 2001 19:18 schreef CoDeR het volgende:
Ik zou de eenheid op de factuurregel weglaten, zit iets verpakt per 10 ofzo en iemand wil er maar 1 dan wordt aantal gewoon 0.1, lijkt me als je op de factuur eenheid veranderd dat het bijhouden van de voorraad lastig/onmogelijk is.

Ook dat kan voor historische info wel handig zijn om er wel bij op te slaan.

Op dinsdag 04 december 2001 19:33 schreef Batser het volgende:
In ieder geval de omschrijving weer weg uit factuurregel, want die zie ik twee keer staan en volgens mij bedoel je 2 keer dezelfde omschrijving, namelijk die van artikel. Dit is dus redundantie.

Tekstartikel cq. opmerking tussendoor? Kan 'ie wel handig voor zijn.
En voor als de omschrijving iets aangepast moet worden voor die ene klant. Kan ff zo snel niet een goed voorbeeld verzinnen, maar zie het bij verschillende klanten die de omschrijving iets aanpassen per klant (ok dat zou je weer in een aparte tabel kunnen opslaan... klantnr - artcode - omschrijving).

Op dinsdag 04 december 2001 21:00 schreef Afterlife het volgende:
Na een tijdje wil 'ie koppelen met z'n administratiepakket, en dan krijg je te maken met omzetgegevens (grootboeknummers, enz. en 1 artikel kan meerdere grootboeknummers hebben, bv. een deel 'omzet hardware' en een deel 'service after sales'), met 'accounts receivable' (aanmaningen, kredietbeperking, etc.), en met leuke dingen als BTW-afdrachten verantwoorden en zo.

Goed punt. Hoewel dat info is die je prima bij je artikel kan opslaan. BTW is misschien wel handig om bij de faktuur op te nemen, percentage dan, is ook nog weleens handig voor als de btw verandert (btw tabel dan maar? )

Op zich geen slecht idee trouwens om te kijken voor een standaartpakket, is al vrij snel goedkoper (afhankelijk van je uurloon natuurlijk), hoewel het op zich wel leuk is om zelf te maken natuurlijk, en dat kan ook z'n voordelen hebben (afhankelijk van hoeveel customized dingen de klant verder wil).

[Verwijderd]

Op dinsdag 04 december 2001 21:15 schreef Crazy_D het volgende:
Goed punt. Hoewel dat info is die je prima bij je artikel kan opslaan. BTW is misschien wel handig om bij de faktuur op te nemen, percentage dan, is ook nog weleens handig voor als de btw verandert (btw tabel dan maar? )

Neh, geen BTW-tabel. Een stuk of 4 BTW-types in je stamgegevens is voldoende. Maar wel in elke factuurregel het gehanteerde BTW-percentage opnemen.
Of eigenlijk is 't nog lastiger: in elk omzet-deel van die factuurregel het BTW-percentage opnemen. Gangbare oplossing is het bijhouden van een 'posting' tabel (dat wat op de factuur gaat komen), en een 'ledger' tabel (de omzet uitsplitsing). Ieder 'posting' record heeft 1 of meer bijbehorende 'ledger' records.
Verder is 't ook wel handig om van iedere geprinte factuur alle gegevens op te slaan. Factuurnummer, geadresseerde, vervaldatum, etc. zijn nogal voor de hand liggend, maar ik kwam er een tijdje terug (na een Euro conversie) achter dat ook de valuta waarin gefactureerd is misschien wel handig is...

Op zich geen slecht idee trouwens om te kijken voor een standaartpakket, is al vrij snel goedkoper (afhankelijk van je uurloon natuurlijk), hoewel het op zich wel leuk is om zelf te maken natuurlijk, en dat kan ook z'n voordelen hebben (afhankelijk van hoeveel customized dingen de klant verder wil).

Is erg leuk werk, en je leert er een hoop van!
Maar ik ben bv. voor de klant per dag duurder dan een pakketje Cash met factureermodule. Niet om op m'n borst te kloppen (en ik zie er zelf maar een fractie van), maar om aan te geven dat alles z'n perspectief heeft.

[Crazy D]

Op dinsdag 04 december 2001 21:47 schreef Afterlife het volgende:
Neh, geen BTW-tabel. Een stuk of 4 BTW-types in je stamgegevens is voldoende. Maar wel in elke factuurregel het gehanteerde BTW-percentage opnemen.

Voordeel van een aparte BTW tabel ("BTW stambestand" dus eigenlijk) is dat ook in historisch perspectief alles klopt. En je werkt altijd met dezelfde gegevens. En bij een BTW wijziging 'moet' je een nieuwe BTW code toevoegen, en dan klopt historisch gezien nog steeds alles. Maar het hoeft niet zo groot te zijn, 95% van onze klanten heeft 0%, 19% incl. en 19% excl., en nog historisch 17.5% incl. en excl.
(incl. en excl.: bij de een is de verkoopprijs incl. BTW, dus bedrag is "119% van prijs ex BTW", in het andere geval is de verkoopprijs ex BTW en is het simpel "prijs * 1.19")
Maar misschien denk ik wel te ver omdat ik veel met Exact werk en da's best een vrij uitgebreid faktureer-progje

Of eigenlijk is 't nog lastiger: in elk omzet-deel van die factuurregel het BTW-percentage opnemen. Gangbare oplossing is het bijhouden van een 'posting' tabel (dat wat op de factuur gaat komen), en een 'ledger' tabel (de omzet uitsplitsing). Ieder 'posting' record heeft 1 of meer bijbehorende 'ledger' records.

Da's denk ik afhankelijk van hoe de rest van je admin werkt. Bij Exact is het simpel gezegt zo:
- voer faktuur in
- print definitieve faktuur
- journaliseren/verwerken. Dat is het punt waarop de verschillende grootboekrekeningen uit het artikelbestand (of artikelgroepen) wordt gehaald, en waar de bedragen van de faktuur opgesplitst wordt in oa. BTW, omzet, korting, etc.
Die splitsing wordt weer in een tijdelijke tabel gezet (financiele mutaties kop- en regelstabel), en na verwerken in de grootboekmutaties geplaatst, waarvan de rest van de financiele administratie z'n gegevens haalt.
(zo ongeveer dan he... als er mensen intresse in hebben kan ik het wel wat uitgebreider opschrijven, mits ik daar geen gezeik mee kan krijgen natuurlijk. (deel van) de db layout wil ik ook wel plaatsen als er intresse in is, ik geloof niet dat de db layout bedrijfsgeheim is)

Verder is 't ook wel handig om van iedere geprinte factuur alle gegevens op te slaan. Factuurnummer, geadresseerde, vervaldatum, etc. zijn nogal voor de hand liggend, maar ik kwam er een tijdje terug (na een Euro conversie) achter dat ook de valuta waarin gefactureerd is misschien wel handig is...

Uhh ja da's idd wel erg handig Adresgegevens zijn op dat moment ook niet meer van belang dat ze dubbel in de admin staan, aangezien de adressen op de faktuur in principe niet meer aangepast moeten mogen worden (weer goed voor de history...)
(al is het niet zo gangbaar om de bedragen in de fakturen dan aan te passen. Wat ik heb gezien wordt er nogal vaak van 2 valuta's gebruik gemaakt: bedragen in de default valuta van de admin, nu dus nog meestal HFL maar vanaf 1-1 dus Euro), en een bedrag in 'ingevoerde valuta'. Zo kun je dus ook relatief makkelijk fakturen in belgische franken in kloppen, en blijven die gegevens zoals ingevoerd ook gewoon in die valuta staan). Maar ja die klote-euro is nu maar heel ff, en als het zeker is dat er geen fakturen naar het buitenland cq. niet-euro landen gaan, is dat misschien niet zo erg hard nodig. (tenzij ze over een paar jaar weer een andere valuta verzinnen voor europa )