Db structuur facturen
Ik kan er niet helemaal uitkomen wat nu de beste structuur is voor facturen. Ik zal hieronder laten zien hoe ik het nu heb, maar dit zorgt voor ingewikkelde queries lijkt me.
verkoop_facturen
vfactnr (uniek auto increment)
klantnr
vfactdatum
vfactvervaldatum
inkoop_facturen
ifactnr (uniek auto increment)
klantnr
ifactdatum
ifactvervaldatum
artiekelen
artid (uniek auto increment)
artomsc (varchar (150))
artprijs (float (5,2))
join_facturen
joinid (uniek auto increment)
vfactnr
ifactnr
artid
Mijn idee was om in join_facturen dus de juiste factuurnummers zetten met artiekelid's. Echter wanneer ik dus de factuur wil weergeven moet ik gegevens hebben uit 3 a 4 tabellen! Namelijk: klanten, vfactuur/ifactuur, artiekelen en join_facturen.
Mij lijkt dit nogal omslachtig en wellicht een zware query, wat is jullie idee?
Gewijzigd op 15/01/2014 15:37:00 door Chris PHP
Decimal
Want float 5.45 + 5.45 zou best eens niet op 10.90 uit kunnen komen. Floats zijn bij benadering.
Verder mis je in de facturen het bedrag. Dat bedrag is niet gelijk aan de prijs van het verkochte artikel.
Want stel nu dat je vandaag een ding verkoopt voor 10 euro.
Dan komt daar een factuur uit voor 10 euro.
Morgen wordt het artikel wegens gestegen prijzen naar 11 euro.
Klant betaalt keurig de 10 euro, en jouw administratie beweert dat er 11 in rekening was gebracht....
En ik snap niet helemaal hoe jij join_facturen precies ziet. Of jij bedoelt met inkoopfactuur iets anders dan dat ik doe.
Verder zou ik niet alles zo afkorten. nu is het jou misschien duidelijk, maar is het dat ook nog over 1 jaar, of over 2 jaar voor een collega die er mee moet gaan werken en zich bij alles moet afvragen zou een artomsc soms artikelosmchrijving zijn,
danwel als hij een query schrijft, zich weer moet afvragen: welke letters laat ik weg uit inkoopfactuurvervaldatum
Het is nu iets meer typwerkt, maar scheelt je een hoop potentiële fouten
Thnx voor de input Float -> Decimal duidelijk.
Er komen nog meer velden bij maar het artiekel blijft uniek en de prijs wordt uiteraard ge-upadte wanneer deze hoger of lager wordt. Dus bijv pak koffie is 2,50 dit zal zo blijven totdat ik de prijs verhoof of verlaag. Het totaal bedrag moet nog ergens bij komen, maar ik weet niet precies hoe ik nu een query kan draaien die selecteerd wat ik nodig heb.
De join_facturen is om de artiekelen aan de juiste factuur te koppelen, bijv factuur001 heeft 3 artiekelen dit wordt dus weggeschreven in de join_facturen. De afkortingen is puur voor hier, omdat dit een voorbeeld is. Als ik het alemaal moet uittypen is veel werk.
Tevens documenteer ik alles, wat welke velden inhouden. Klantgegevens hebben zelfs id's ipv relatieve namen. Een aantal gegevens worden daarin encrypted opgeslagen en ge-decrypt wanneer ik ze opvraag. Puur omdat je steeds vaker leest dat er xxx.xxx aantal klantgegevens op straat liggen door database hacks.
Dit wil ik voorkomen voor mijn klanten.
Chris NVT op 15/01/2014 15:36:19:
Mij lijkt dit nogal omslachtig en wellicht een zware query, wat is jullie idee?
Geen enkel probleem, totdat je miljoenen facturen gaat behandelen. Zorg er alleen voor dat je de juiste indices aanmaakt, dat maakt uiteraard wel uit.
een volgnummer (id)
een FK naar een klant-id
een datum
een vervaldatum
een status (open, betaald etc) liefst met een FK naar een status tabel
een betaal datum
eventueel nog een factuurnummer, bijvoorbeeld 2014-1-999 voor factuur met id 999 in het eerste kwartaal
Facturen hebben 1 of meerdere regels
tabelfactuurregels
een id
een FK naar het factuurnummer (id)
een FK naar een artikel id
een aantal (5 stuks)
het bedrag (5 * 10 euro)
een btw percentage
wil je nu weten wat het bedrag is van deze factuur dan zijn er 2 opties:
ofwel bereken je dat door de sommatie van de betreffende factuur regels
ofwel bereken je dat eenmalig en sla je dat bij de factuurtabel op
Toevoeging op 15/01/2014 16:09:54:
oh, en bedenk ook vast wat te doen met facturen die in meerdere etappes betaald worden.
Toevoeging op 15/01/2014 16:10:55:
misschien sowieso wel een tabel voor de registratie van betalingen.
Maar voor het een heel boekhoudpakket wordt: wat ben je aan het bouwen?
Chris NVT op 15/01/2014 15:56:17:
Er komen nog meer velden bij maar het artiekel blijft uniek en de prijs wordt uiteraard ge-upadte wanneer deze hoger of lager wordt.
Nou, dat is dus juist niet de bedoeling. ;-)
De prijs van een gefactureerd artikel moet nooit meer kunnen wijzigen. Om die reden moet je een factuurtabel dus ook nooit joinen met een artikeltabel, maar moet je bij het aanmaken van de factuur de betreffende artikelregels kopiëren naar bijvoorbeeld een tabel met factuurregels.
Dat gaat dus niet alleen over de prijs van een artikel, maar ook naar welk adres je hem verstuurt.
Ik heb trouwens voor alleen de product gegevens van een bestelling/factuur al 7 tabellen nodig, dit gaat prima met één query
In factuur onder andere de volledige naw van de koper dus niet alleen een ref key en in factuurregel de producten compleet met omschrijving prijs en btw want in vijf jaar kan het product wel uit de handel zijn en de btw 30%. In factuur kan je desgewenst nog wat andere attributen (aankoop/verkoop) of referentie naar bestelling en betaling of status daarover opnemen. @Ger 7 tabellen? Ik ben benieuwd naar de andere 5?
brands, sizes, colors, suppliers en product_images.
Die laatste is voor een factuur natuurlijk overbodig, tenzij ....
Sorry voor de late reactie! Gisteren behoorlijk gehad met de migratie.
Dank u allen voor de input!!
@Ivo,
Dank je voor het voorbeeld, ik zal dat eens goed uitwerken en kijken hoe ik het kan gaan doen.
Mijn idee was om het factuurnummer uniek te maken, maar het is idd makelijker om dat niet te doen. Maar hoe bepaal je nu dan het eerst volgende factuurnummer? Daarom wilde ik het dus een unique auto_increment maken.
@Willem,
Yes ik snap je punt, kopt idd dat wanneer ik in mijn structuur de prijs zou wijzigen, hij het dus voor alle facturen zou wijzigen omdat ik deze daar doorvoer!
@Ger,
Ik ga zoiezo een script schrijven waar de facturen automatisch als pdf naar een share geschreven worden. Dus dan blijft het ten alle tijden 'gearchiveerd', tevens uiteraard een dagelijkse DB backup!
@Aar,
Daarom zit er ook een FK in naar de klantgegevens, zodat daar dus de NAW gegevens opgehaald kunnen worden lijkt me.
Nogmaals dank voor het meedenken!!
Chris NVT op 16/01/2014 10:50:51:
Daarom zit er ook een FK in naar de klantgegevens, zodat daar dus de NAW gegevens opgehaald kunnen worden lijkt me.
En dat moet juist niet! Bij een factuurdatabase moet je niet normaliseren, maar dupliceren.
Als je klant in de tussentijd verhuist, moet je na kunnen gaan dat je de factuur/artikelen naar het oude adres hebt gestuurd, en dat kan niet wanneer je de klantgegevens als FK opneemt.
Chris NVT op 16/01/2014 10:50:51:
@Aar,
Daarom zit er ook een FK in naar de klantgegevens, zodat daar dus de NAW gegevens opgehaald kunnen worden lijkt me.
Daarom zit er ook een FK in naar de klantgegevens, zodat daar dus de NAW gegevens opgehaald kunnen worden lijkt me.
Kijk zelfs daar al mee uit. Als je aan het einde van het jaar wil weten hoeveel facturen je hebt gestuurd naar klanten in land x, dan zal je dat waarschijnlijk aanmaken op basis van die gegevens. Maar wat als een klant in de tussentijd is verhuisd van land x naar land y? Je kan natuurlijk alle losse facturen door gaan spitten, maar dat lijkt me niet wenselijk. Ook dit soort gegevens (in elk geval die gegevens waarop je mogelijkerwijs nog eens zou willen zoeken en die kunnen veranderen), zou ik dus bij de factuur c.q. order opslaan, los van je klantgegevens tabel.
Maar dan kom ik toch terug op mijn eerste vraag, wat is nu de beste structuur?
Wat voor velden en in wat voor soort tabellen? Dan is namelijk het hele punt waar ik tegenaan loop.
Ik wil dat het factuurnummer uiteraard auto_increment en unique is.
Willem vp op 16/01/2014 11:03:44:
En dat moet juist niet! Bij een factuurdatabase moet je niet normaliseren, maar dupliceren.
Als je klant in de tussentijd verhuist, moet je na kunnen gaan dat je de factuur/artikelen naar het oude adres hebt gestuurd, en dat kan niet wanneer je de klantgegevens als FK opneemt.
Chris NVT op 16/01/2014 10:50:51:
Daarom zit er ook een FK in naar de klantgegevens, zodat daar dus de NAW gegevens opgehaald kunnen worden lijkt me.
En dat moet juist niet! Bij een factuurdatabase moet je niet normaliseren, maar dupliceren.
Als je klant in de tussentijd verhuist, moet je na kunnen gaan dat je de factuur/artikelen naar het oude adres hebt gestuurd, en dat kan niet wanneer je de klantgegevens als FK opneemt.
Hetzelfde geldt voor de artikelen. Prijzen kunnen gewijzigd worden, maar voor de facturen moet je de historische prijs op het moment van bestellen weten. Ook dupliceren daarom, niet slechts koppelen, of alle prijzen per tijdvak onderbrengen in een aparte tabel.
Verder kunnen btw-percentages nog veranderen en worden product- en dienstcategorieën, door overheidsbeleid, soms verplaatst van de ene naar de andere btw-tariefgroep (hoog/laag/vrijgesteld).
Order tabel
(alle data van de order die betrekking heeft op de gehele order)
- id
- datum
- klant (alle gegevens hierin copieren)
- totaal bedrag
Producten/diensten
- product id
- order id
- aantal
- prijs
- btw percentage
facturen
- factuur id
- order id
- datum
- totaal bedrag
- klant adres (indien factuur adres anders is dan de klant adres voor de order)
factuur regels
- factuur id
- product id
- bedrag
- factuur regel
factuur betaald
(apart opslaan van de facturen, zodat je eventueel meerdere betalingen per factuur kan registreren. Dit maakt het mogelijk om een factuur door een klant in delen te laten betalen)
- factuur id (niet uniek maken!)
- bedrag betaald
- datum
Gewijzigd op 16/01/2014 12:50:43 door Erwin H
Dank je! Hier kan ik al een heel stuk verder mee komen, wel een aantal vragen.
In de order tabel heb je staan klant (alle gegevens hierin copieren), lijkt me het makkelijkste dat ik deze gegevens in een array laad en die dan wegschijf?
In de facturen tabel heb je het factuur id staan, ik neem aan dat deze unique en auto_increment is? Als dat zo is kan ik in je opzet toch maar 1 order id koppelen? Of voeg je meerdere id's in het order id veld toe, en bijvoorbeeld scheiden met een comma?
Dan de laatste vraag, wat is nu dan een logische query om dit alles in een factuur op het scherm te krijgen?
Hartelijk dank voor de tijd en energie die je erin gegooit hebt!
Die factuur id kan je inderdaad auto increment maken (in elk geval uniek). Je kan er ook voor kiezen dat nummer elke keer zelf te genereren. Auto increment heeft namelijk wel de eigenschap dat er bepaalde waardes niet gebruikt worden als een insert om wat voor reden dan ook niet lukt.
Een factuur kan altijd maar aan 1 order vastzitten, je kan wel meerdere facturen per order hebben (ik zie niet hoe je 1 factuur voor meerdere orders kunt hebben, maar iemand die daar voorbeelden van heeft: meldt het :-)
Als je een factuur nodig hebt dan begin je in je query met de factuurn tabel. Daar haal je alle gegevens uit die je nodig hebt. Vervolgens join je de factuur regels tabel voor elke specifieke regel op de factuur. De order tabel kan je er weer aan joinen voor de klant gegevens en tenslotte kan je eventueel de 'facturen betaald' tabel joinen om het nog openstaande bedrag te bepalen.
Merk op dat het ook best kan zijn dat je die in twee queries wilt doen. De eerste voor de factuur specifieke gegevens, de tweede voor de factuur regels. Maar dat is een keuze.
Een factuur kan altijd maar aan 1 order vastzitten, je kan wel meerdere facturen per order hebben (ik zie niet hoe je 1 factuur voor meerdere orders kunt hebben, maar iemand die daar voorbeelden van heeft: meldt het :-)
Als je een periodieke facturatie hebt, kan je meerdere orders per factuur hebben.
Erwin H op 16/01/2014 13:10:25:
Een factuur kan altijd maar aan 1 order vastzitten, je kan wel meerdere facturen per order hebben (ik zie niet hoe je 1 factuur voor meerdere orders kunt hebben, maar iemand die daar voorbeelden van heeft: meldt het :-)
Dat heet "verzamelfactuur". Soms is het handig om niet per order te factureren, maar per tijdsperiode, bijvoorbeeld wekelijks of maandelijks. Je kan dan alle orders die in die periode zijn geplaatst op 1 factuur zetten. Apple doet dat bijvoorbeeld met iTunes. Als ik vandaag een app koop en morgen nog een, dan is de kans groot dat die op dezelfde factuur komen te staan.
Overigens zou ik in de ordertabel ook een (optioneel) referentienummer van de klant opnemen. Sommige klanten waarderen/vereisen het dat op de factuur/pakbon een verwijzing staat naar hun eigen inkoopordernummer.
Toevoeging op 16/01/2014 14:06:59:
Erwin H op 16/01/2014 13:10:25:
Die factuur id kan je inderdaad auto increment maken (in elk geval uniek). Je kan er ook voor kiezen dat nummer elke keer zelf te genereren.
Vanwege het door jou ook al genoemde nadeel van missende autoincrement-id's zou ik ervoor kiezen het factuurnummer zelf te genereren. De Belastingdienst vindt het niet leuk als er factuurnummers kwijt zijn... Ordernummers kun je wel laten autoincrementen; als daar een nummertje mist is het niet zo erg.
Let er wel op dat het uitvoeren van de queries voor het hoogste bestaande factuurnummer en het genereren van een nieuw factuurnummer in één transactie moeten plaatsvinden, omdat je anders het risico loopt dat er twee gelijke factuurnummers worden uitgegeven. Die tweede zal weliswaar mislukken (mits je het id als PK of UK hebt gedefinieerd) maar het is beter de problemen een stap voor te zijn.
Toevoeging op 16/01/2014 14:10:45:
Denk ook nog even na hoe je eventuele kortingen wilt verwerken. In de product/order-tabel de artikelprijs aanpassen, of een extra veld opnemen met het kortingsbedrag of -percentage? Daarnaast kun je verschillende soorten kortingen hebben, zoals tijdelijke aanbiedingen, staffelkortingen of klantgebaseerde kortingen.
Gewijzigd op 16/01/2014 14:12:13 door Willem vp