[MySQL] opzetten tabel fianciële mutaties

[nota]

Ik ben bezig met het opzetten van een kasboek in een MySQL database, dat later benaderd zal worden via PHP. Nu wil ik graag als bron voor dit kasboek de mutaties van een bankrekening gebruiken. Een bestand met deze mutaties wordt via de bank (internetbankieren) opgehaald in de vorm van een CSV bestand. Dit CSV bestand moet middels een nog te programmeren webinterface geimporteerd worden waarna het verwerkt wordt in het kasboek, en wel specifiek in de tabel "mutaties".

Nu is het probleem dat het verkregen csv-bestand geen uniek id per record bevat. Om er nu voor te zorgen dat je geen duplicates krijgt omdat je bestanden met overlappende periodes importeert had ik het volgende bedacht:

Het wordt verplicht om altijd de mutaties vanaf het begin van het gewenste kalenderjaar de importeren. Bij het importeren van deze mutaties worden er twee kolommen gezet: het "kalenderjaar" en een "id" (en natuurlijk alle mutatiedata), waarbij het id PER jaar vanaf "1" op moet lopen. Samen moeten deze twee kolommen de unieke sleutel vormen. Aangezien er historisch nooit iets kan veranderen in een mutatiebestand, dwz dat een nieuw bestand altijd evenveel of meer regels bevat dan het oude bestand, kun je bij een import gewoon alle records van het te importeren kalenderjaar verwijderen en de regels opnieuw inlezen. Iedere regel die al bestond krijgt dan in feite altijd weer hetzelfde id terug. id's mogen dus dubbel voorkomen in de tabel maar NIET met hetzelfde kalenderjaar.

Ik heb nu eigenlijk twee vragen:

- Hoe krijg ik het voor elkaar om zonder een primary key te gebruiken, de kolommen "kalenderjaar" en "id" samen UNIQUE te maken. Ik heb in PHPMyAdmin beide kolommen geselecteerd en vervolgens "UNIQUE" geselecteerd. Dit lijkt goed te gaan totdat ik twee records invoeg met hetzelfde id (1) maar met een verschillend kalenderjaar (resp. 2010 en 2011). Ik krijg dan de foutmelding: "#1062 - Duplicate entry '1' for key 1".

- Is dit wel de meest handige manier of heeft er iemand een andere suggestie?

[cariolive23]

"#1062 - Duplicate entry '1' for key 1"

Blijkbaar staat er al dubbele data in de tabel.

Het aanmaken van een unique index (= constraint) kun je beter zelf in SQL opstellen, dan weet je exact wat er gebeurt:

SQL:

CREATE UNIQUE INDEX naam_van_jouw_index ON tabelnaam (col1, col2, col3);

[nota]

cariolive23 schreef op woensdag 24 maart 2010 @ 21:11:
[...]


Blijkbaar staat er al dubbele data in de tabel.

Het aanmaken van een unique index (= constraint) kun je beter zelf in SQL opstellen, dan weet je exact wat er gebeurt:

SQL:

CREATE UNIQUE INDEX naam_van_jouw_index ON tabelnaam (col1, col2, col3);

Deze heb ik uitgevoerd:

CREATE UNIQUE INDEX mutatieId ON mutaties (jaar, id);

Maar nog steeds krijg ik dezelfde foutmelding bij het invoegen van een nieuw record (er staat 1 record in de tabel met "jaar"="2010" en "id"="1" en ik wil een record toevoegen met "jaar"="2011" en "id"="1").

[Voutloos]

Laat je structuur eens zien. Daar staat nu wellicht toch nog een PK of Unique op enkel de `id` kolom in.

[nota]

Voutloos schreef op woensdag 24 maart 2010 @ 21:21:
Laat je structuur eens zien. Daar staat nu wellicht toch nog een PK of Unique op enkel de `id` kolom in.

bedankt! Bij het zoeken naar de tabelstructuur kwam ik er achter dat je via "details" de indices te zien krijgt, hier stond inderdaad nog een verdwaalde index op alleen het "jaar" veld in. Deze heb ik weggehaald en nu werkt het prima!

Staat alleen nog mijn vraag of dit de beste manier is of dat iemand een suggestie heeft hoe het beter zou kunnen (nu zit ik namelijk nog wel met de beperking dat je altijd de mutaties van het volledige jaar moet inlezen omdat anders alle relaties met de mutatietabel niet meer kloppen)

[Alain]

Ik heb hier zelf ook over nagedacht. Ik zou zelf niet per jaar werken voor het importeren van data, aangezien je tussendoor ook wilt weten hoe je ervoor staat. Hoe dan ook kom ik er op uit, dat ik dit niet in de database wil regelen.

Je zou een systeem kunnen bedenken waarbij je alleen importeert vanaf een bepaalde datum en de mutaties van die dag controleert op alle velden. Als alle velden gelijk zijn, is er een hele grote kans dat de mutatie al in de database staat. Helemaal waterdicht kun je dit niet krijgen, aangezien je geen uniek veld / combinatie van velden hebt per mutatie.

Ik zou geen index leggen op jaar en een niets zeggend id. Het datamodel heeft niks te maken met de wijze waarop je data importeert. Een niets zeggend id is alleen handig als je de mutatie's wilt koppelen aan andere tabellen.

[prutsger]

Ik heb hier ook al eens over nagedacht, omdat ik voor mijzelf ook een kasboek in PHP wil programmeren, alleen staat dat nu even in de ijskast.

Je kunt nooit controleren of de gebruiker ook echt alles vanaf het begin van het jaar probeert te importeren. Doet hij dat per ongeluk (of expres ) niet, dan krijg je geheid inconsistente data. Wat je het beste zou kunnen doen is een primary key leggen op de velden die de transactie identificeren, ik den zo snel even aan datum/tijdstip, rekeningnummer. Misschien dat daar nog wat bij moet. Dan maakt het verder geen bal uit welk id er vervolgens in je database bijkomt. Dit is ook waar een primary key voor is bedoeld, ook al wordt er vaak een id voor `misbruikt` (wat ik overigens zelf ook bijna altijd doe).

Vervolgens ga je dus bij een import kijken of de transactie al in je tabel zit. Zo niet dan zet je hem er in.

[Kalentum]

prutsger schreef op donderdag 25 maart 2010 @ 10:52:
Ik heb hier ook al eens over nagedacht, omdat ik voor mijzelf ook een kasboek in PHP wil programmeren, alleen staat dat nu even in de ijskast.

Je kunt nooit controleren of de gebruiker ook echt alles vanaf het begin van het jaar probeert te importeren. Doet hij dat per ongeluk (of expres ) niet, dan krijg je geheid inconsistente data. Wat je het beste zou kunnen doen is een primary key leggen op de velden die de transactie identificeren, ik den zo snel even aan datum/tijdstip, rekeningnummer. Misschien dat daar nog wat bij moet. Dan maakt het verder geen bal uit welk id er vervolgens in je database bijkomt. Dit is ook waar een primary key voor is bedoeld, ook al wordt er vaak een id voor `misbruikt` (wat ik overigens zelf ook bijna altijd doe).

Vervolgens ga je dus bij een import kijken of de transactie al in je tabel zit. Zo niet dan zet je hem er in.

Zat ik ook aan te te denken. Er is een klein probleem: wat nu als iemand twee keer op een dag naar hetzelfde rekening met dezelfde omschrijving en hetzelfde bedrag geld overmaakt. Als ik de CSV dump van mijn bank bekijk zou dat twee keer exact dezelfde regel opleveren. Het is een beetje een theoretisch voorbeeld maar het kan wel.

Bij mijn bank (ING) is zo dat je geen transacties van vandaag kan exporteren. Dus je kan er ook nog voor kiezen om geen transacties te importeren van dagen die je al in je database hebt. Maar dat is dan wel onder de aanname dat de bank niet nog dingen toevoegt op 24 maart terwijl het al 25 maart is.

[BertS]

En dat laatste doen ze wel, bijvoorbeeld met bankkosten. Die komen ergens in de loop van de maand binnen, en staan dan geboekt op de eerste van de maand.

[prutsger]

Dan gooi je ook nog het bedrag in de key, dan wordt de kans dat het voorkomt nog kleiner. Maar ja theoretisch kan het nog steeds. Ik weet er zo ook even geen oplossing voor.

Ik heb ook ing en heb even de export bekeken. Bij een betaling van het type 'Betaalautomaat' komt een tijdstip in de omschrijving te staan, en een transactienummer.
Ik zie bijvoorbeeld in mijn export twee maal een betaling bij de supermarkt op dezelfde dag, die zou je lastig kunnen onderscheiden, maar dat kan dus met het omschrijving veld.
Voor betalingen van derden is dit wat lastiger, maar daar zie ik eigenlijk ook overal wel een factuurnr oid staan.

Je zou natuurlijk kunnen importeren wat duidelijk geimporteerd kan worden, en bij twijfel records markeren en in een scherm tonen en de gebruiker laten kiezen wat er mee moet gebeuren?

Maar makkelijk is het niet... zou handig zijn als ING dan zelf al een uniek id aan een transactie zou hangen.

[Hydra]

Ik heb zelf een tooltje gemaakt dat mijn internetbankierenexports samenvoegt zodat ik 1 grote spreadsheet krijg. Wat ik gewoon doe is kijken naar de mutatiedata en het punt vinden waarbij de oude en niewe sets niet meer in elkaar passen. Algorithmisch ga ik dus aan de hand van het oudste record in de nieuwe download terugzoeken in de grote set om dat punt te vinden, waarna ik alle dubbele verwijder en alleen nieuwe mutaties overhoud, welke dan aan de grote set toegevoegd worden.

Je zult een dergelijke check toch moeten doen omdat mutaties niet exact op volgorde van datum/tijd in je export staan.

[magiel]

rutgerw schreef op donderdag 25 maart 2010 @ 11:03:
[...]


Zat ik ook aan te te denken. Er is een klein probleem: wat nu als iemand twee keer op een dag naar hetzelfde rekening met dezelfde omschrijving en hetzelfde bedrag geld overmaakt. Als ik de CSV dump van mijn bank bekijk zou dat twee keer exact dezelfde regel opleveren. Het is een beetje een theoretisch voorbeeld maar het kan wel.

Bij mijn bank (ING) is zo dat je geen transacties van vandaag kan exporteren. Dus je kan er ook nog voor kiezen om geen transacties te importeren van dagen die je al in je database hebt. Maar dat is dan wel onder de aanname dat de bank niet nog dingen toevoegt op 24 maart terwijl het al 25 maart is.

prutsger schreef op donderdag 25 maart 2010 @ 11:45:
Dan gooi je ook nog het bedrag in de key, dan wordt de kans dat het voorkomt nog kleiner. Maar ja theoretisch kan het nog steeds. Ik weet er zo ook even geen oplossing voor.

Ik heb ook ing en heb even de export bekeken. Bij een betaling van het type 'Betaalautomaat' komt een tijdstip in de omschrijving te staan, en een transactienummer.
Ik zie bijvoorbeeld in mijn export twee maal een betaling bij de supermarkt op dezelfde dag, die zou je lastig kunnen onderscheiden, maar dat kan dus met het omschrijving veld.
Voor betalingen van derden is dit wat lastiger, maar daar zie ik eigenlijk ook overal wel een factuurnr oid staan.

Je zou natuurlijk kunnen importeren wat duidelijk geimporteerd kan worden, en bij twijfel records markeren en in een scherm tonen en de gebruiker laten kiezen wat er mee moet gebeuren?

Maar makkelijk is het niet... zou handig zijn als ING dan zelf al een uniek id aan een transactie zou hangen.

Kun je niet doen wat Rutgerw zegt, en de door jou omschreven situatie echoen als deze zich voordoet (wat laten we wel wezen, zo vaak gebeurd dat niet) en dan kiezen of dit juist of onjuist is? Daarbij worden identieke regels in jou omschrijving toch simultaan geimporteerd, dus ik zie niet in wat voor probleem dat op kan leveren.

[prutsger]

magiel schreef op donderdag 25 maart 2010 @ 13:57:
[...]


[...]


Kun je niet doen wat Rutgerw zegt, en de door jou omschreven situatie echoen als deze zich voordoet (wat laten we wel wezen, zo vaak gebeurd dat niet) en dan kiezen of dit juist of onjuist is? Daarbij worden identieke regels in jou omschrijving toch simultaan geimporteerd, dus ik zie niet in wat voor probleem dat op kan leveren.

Dan ga je voorbij aan:

BertS schreef op donderdag 25 maart 2010 @ 11:10:
En dat laatste doen ze wel, bijvoorbeeld met bankkosten. Die komen ergens in de loop van de maand binnen, en staan dan geboekt op de eerste van de maand.

Dan loop je dus het risico dat je transacties mist. Zo vaak zal het inderdaad niet gebeuren dat de situatie zich voordoet dat twee transacties dezelfde eigenschappen hebben, zeker niet als je de omschrijving in je key opneemt (omdat daar het tijdstip in genoemd wordt).