[MySQL] adres tabel schoon houden, constraint of procedure ?

[MichielioZ]

Een waarschijnlijk simpele vraag, maar kom er even niet uit...
De volgende situatie:
- Tabel "persoon": ID, voornaam, achternaam etc.
- Tabel "adres": ID, straatnaam, huisnummer etc.
- Koppeltabel "persoon_adres": ID van persoon, ID van adres

De kolommen in de koppeltabel zijn FK's, waarbij er nu een constraint op zit dat als de persoon of het adres verwijderd wordt, de koppeling ook verwijderd wordt.
De reden dat er niet gewoon een "persoon_adres" tabel is waar direct de adres gegevens in staan is omdat ik ook "klant" en "leverancier" e.d. wil kunnen koppelen, maar vooral omdat ik graag per adres een unieke ID zou willen hebben. (ivm routes uitzetten)

Enige probleem met bovenstaande is dat je meestal een "persoon" zult verwijderen en als er dan geen koppeling meer bestaat met dat adres (dus ook niet naar andere personen, klanten e.d.) het adres nooit uit de database verdwijnt...
Nu zit ik zelf te denken aan een stored procedure om dit af te vangen, maar kan me voorstellen dat er een elegantere oplossing mogelijk moet zijn... iemand ?

[NMe]

On delete cascade, maar daarmee moet je wel verdomd goed weten wat je doet. Als je dat aan te veel tabellen hangt, dan cascade je binnen de kortste keren je halve database. Wat dat betreft is een SP inderdaad wat veiliger.

[Voor 12% gewijzigd door NMe op 15-05-2011 13:42]

[RobIII]

Dus als ik Jantjes adres aanpas dat toevallig ook het adres van Pietje is omdat ze allebei nog thuis wonen gaat Pietje automatisch mee naar het nieuwe adres? Ik lees eigenlijk een beetje een doorgeslagen (of "minder goed" uitgevoerde) normalisatieslag... Of ik lees het verkeerd, dat kan ook Ik zie gewoon even niet waarom een adres niet gewoon bij de persoon zelf opgeslagen is in dezelfde tabel.

[Voor 22% gewijzigd door RobIII op 15-05-2011 13:44]

[C0rnelis]

RobIII schreef op zondag 15 mei 2011 @ 13:43:
Dus als ik Jantjes adres aanpas dat toevallig ook het adres van Pietje is omdat ze allebei nog thuis wonen gaat Pietje automatisch mee naar het nieuwe adres? Ik lees eigenlijk een beetje een doorgeslagen (of "minder goed" uitgevoerde) normalisatieslag... Of ik lees het verkeerd, dat kan ook Ik zie gewoon even niet waarom een adres niet gewoon bij de persoon zelf opgeslagen is in dezelfde tabel.

Afhankelijk van de toepassing kan het sneller zijn om gegevens te scheiden, maar dit is enkel bij gigantische tabellen het geval.

[NMe]

C0rnelis schreef op zondag 15 mei 2011 @ 13:47:
[...]

Afhankelijk van de toepassing kan het sneller zijn om gegevens te scheiden, maar dit is enkel bij gigantische tabellen het geval.

Dat ga je bij een personen/adressentabel niet merken wanneer je indices goed staan. Zelfs niet in MySQL.

[The Eagle]

Niet te moeilijk doen allemaal. 2 extra sleutels opnemen in de tabel: effective date en effective status.
Status kan actief of inactief zijn, effective date is de datum waarop status ingaat en de rij dus actief (of inactief) is of wordt. Zeg maar de ingangsdatum van de gegevens.

Zo kun je met een subselect altijd de max(effdt) where eff_status ='A' ophalen en weet je dus altijd welke gegevens er relevant zijn.

Apart from that: waarom een persoon verwijderen? Die kun je ook gewoon op inactief zetten als je bovenstaande toepast op de persoonstabel

Bovenstaande is overigens gebaseerd op 10 jaar ervaring in de ERP wereld, ik werk dagelijks zo met financiele en HR systemen. Werkt als een tierelier voor al je gegevens. Ik kan je verder melden dat om diverse redenen, het verwijderen van historische gegevens uit een DB eigenlijk not done is, tenzij je de boel elders archiveert.
Naast dat het ronuit kut terugzoekt als een oude klant wer bij je aanklopt of als je oude facturen oid op moet zoeken, zijn er ook gewoon wettelijke verplichtingen omtrent het bewaren van data en boekhoudingen, ongeacht digitaal of analoog.

[NMe]

The Eagle schreef op zondag 15 mei 2011 @ 14:02:
Apart from that: waarom een persoon verwijderen? Die kun je ook gewoon op inactief zetten als je bovenstaande toepast op de persoonstabel

WBP.

[The Eagle]

NMe schreef op zondag 15 mei 2011 @ 14:02:
[...]

WBP.

Geef me 1 goede reden waarom je inactief gezette data aan een user zou tonen danwel in processing zou gebruiken
WPB is leuk maar wordt vaak als een wassen neus gebruikt. Zie ook http://www.rijksoverheid....rkerspersoonsgegevens.pdf

Correctie of verwijdering kan op verzoek van de eigenaar van de gegevens. Dan moet er dus een formeel verzoek komen. Dat maak je niet vaak mee, en als dat wel zo is kun je altijd nog een incidentele delete uitvoeren. Maar dat moet zeker geen standaard zijn.
Verder staat er letterlijk in:

4.8 Hoe lang mag ik persoonsgegevens bewaren?
U mag persoonsgegevens niet langer bewaren dan noodzakelijk is voor het
doel waarvoor u de gegevens verzamelt of (verder) verwerkt.
Hoelang u uw gegevens feitelijk mag bewaren, hangt af van het doel waarvoor
u de gegevens hebt verzameld en verder verwerkt. Dit kan per situatie verschillen:
er is niet een vaste bewaartermijn. U moet zich steeds afvragen of het voor
uw doel nog noodzakelijk is de gegevens te bewaren. Soms kan het echter juist
noodzakelijk zijn om iemands naam te bewaren, om er voor te zorgen dat aan
diegene bijvoorbeeld géén mailings meer worden verstuurd.Wat noodzakelijk
is, kan in de publieke sector anders liggen dan in de private sector. Soms
verplicht een wet u gegevens gedurende een bepaalde periode te bewaren.
Als het voor uw doel niet meer noodzakelijk is de gegevens te bewaren, moet u
de persoonsgegevens:
• verwijderen; of bijvoorbeeld
• alle identificerende kenmerken verwijderen.
U mag persoonsgegevens langer bewaren als dat gebeurt voor historische,
statistische of wetenschappelijke doeleinden.
Het maakt daarbij niet uit of de gegevens oorspronkelijk zijn verzameld voor
dat historische,wetenschappelijke of statistische doel.

Oftewel: je mag ze gewoon laten staan. Niks verwijderen, niet moeilijk doen.
Voor zover ik kan zien kan een rechter oid je ook verwiesen om gegevens te verwijderen. Maar ook daar geldt weer die incidentele sql delete.

Nogmaals: standaard verwijderen van oude gegevens moet je echt NOOIT willen doen. Levert alleen maar ellende op.

[MichielioZ]

RobIII schreef op zondag 15 mei 2011 @ 13:43:
Dus als ik Jantjes adres aanpas dat toevallig ook het adres van Pietje is omdat ze allebei nog thuis wonen gaat Pietje automatisch mee naar het nieuwe adres? Ik lees eigenlijk een beetje een doorgeslagen (of "minder goed" uitgevoerde) normalisatieslag... Of ik lees het verkeerd, dat kan ook Ik zie gewoon even niet waarom een adres niet gewoon bij de persoon zelf opgeslagen is in dezelfde tabel.

Je eerste punt is op te vangen door Pietje eerst een extra adres te geven en z'n oude adres koppeling eventueel te verwijderen.
Ik heb misschien de koppeling tabel niet helemaal goed uitgelegd, aangezien ik wel heb aangehaald dat ik dit ook voor routes wil kunnen gebruiken, wordt hier ook opgeslagen of het z'n huidige woonadres betreft of een adres waar ie in de route opgehaald kan worden.
Een persoon kan dus meerdere adressen hebben, maar heeft 1 "default" adres en eventueel dus "archief" en "route" adressen.

edit:
Maar misschien heb je gelijk met "doorgeslagen normalisatie", aangezien dit systeem nu ook werkt met een "persoon_adres" tabel waar direct het adres wordt opgeslagen... Het huidige voorstel is er vooral omdat routes uitstippelen een stuk makkelijker wordt als je een adres_id hebt.

[Voor 12% gewijzigd door MichielioZ op 15-05-2011 14:30]

[Gomez12]

Persoonlijk ben ik bij dit soort dingen altijd meer geneigd om je adrestabel gewoon zo te laten als die is, bij de persoon_adres verwijs je enkel naar een ander adres met een nieuwe ingangsdatum.
En dan met een (handmatig/laat periodieke) uitgevoerde SP oid zet je de huidige niet meer gebruikte adressen op niet meer tonen.

De meeste dingen bij een adreswijziging gaan fout in de 1e paar weken. Als ik vorige week een adreswijziging heb doorgegeven en ik heb nu een vraag over waar mijn order van 3 weken geleden blijft, dan wens ik niet te horen dat die op mijn nieuwe adres is afgeleverd 3 weken geleden.

Sowieso vind ik dit soort data nooit echt bagger-data, je kan er leuke combo-boxjes etc mee vullen als iemand ergens een straat in moet vullen. Of je nu op die straat momenteel wel of niet een klant hebt wonen is minder relevant, gewoon in een combo-box douwen en je adressenbestand wordt automatisch een minder zooitje.

[The Eagle]

Yup, en met een ingangsdatum biedt je zelf je klanten de mogelijkheid om (al dan niet online) een toekomstige adreswijziging in te voeren. Erg klantvriendelijk

offtopic:
Zo konden wij op een gegeven moment mensen toekomstig zwanger maken enzo. Systeem liet dat zelfs bij mannen toe - maar dat was een stukje maatwerk ivm toeslagen die mannen kregen bij vaderschapsverlof. Ook een toekomstig overlijden was in te vullen

[BurningSheep]

Ik vind het ook een beetje vreemd om voor adres een aparte tabel te maken. Als je het adres in de persoon tabel stopt dan heb je niet het probleem dat er adressen blijven hangen wanneer je personen verwijdert. Ik vind het ook een beetje twijfelachtig om ID's toe te kennen aan adressen. Je zegt dat die wens voortkomt uit het feit dat je routes wilt plannen, maar ik zie die noodzaak niet. Waarschijnlijk gaat een route van de ene persoon naar een andere persoon en niet van adres naar adres, want dan mis je informatie. Stel dat er twee personen op 1 adres wonen en je hebt alleen het ID van het adres dan weet je dus niet of je daar bent voor persoon A of B.

Tenzij je echt een goede reden hebt om van adres een aparte tabel te maken zou ik de adres velden verplaatsen naar de tabel persoon (en eventuele andere tabellen die een adres hebben).

[MichielioZ]

BurningSheep schreef op zondag 15 mei 2011 @ 16:26:
Ik vind het ook een beetje vreemd om voor adres een aparte tabel te maken. Als je het adres in de persoon tabel stopt dan heb je niet het probleem dat er adressen blijven hangen wanneer je personen verwijdert. Ik vind het ook een beetje twijfelachtig om ID's toe te kennen aan adressen. Je zegt dat die wens voortkomt uit het feit dat je routes wilt plannen, maar ik zie die noodzaak niet. Waarschijnlijk gaat een route van de ene persoon naar een andere persoon en niet van adres naar adres, want dan mis je informatie. Stel dat er twee personen op 1 adres wonen en je hebt alleen het ID van het adres dan weet je dus niet of je daar bent voor persoon A of B.

Tenzij je echt een goede reden hebt om van adres een aparte tabel te maken zou ik de adres velden verplaatsen naar de tabel persoon (en eventuele andere tabellen die een adres hebben).

Op dit moment is er al een aparte tabel voor adressen, maar dan "persoon_adres", "klant_adres" etc.
Hierin staan dus meerdere adressen per persoon/klant.
Jou aanpak, dus met adres rechtstreeks in de persoon tabel, zorgt ervoor dat als iemand op adres A woont, maar op adres B opgepikt wenst te worden, er voor adres B een andere persoon aangemaakt zou moeten worden (of 1 extra adres kolom bij de persoon, maar waar stop je dan met kolommen aanmaken ?).
Nog een reden om dat bijvoorbeeld bij een klant te doen is wanneer de klant verhuist, je geen nieuwe klant hoeft aan te maken (die dus ook geen koppeling meer heeft met facturen e.d.), maar gewoon een nieuw adres toevoegd en dat oude adres op "archief" zet.

Genoeg redenen om adres in een aparte tabel te zetten, maar weet nog niet 100% of ik dan alle adressen in 1 tabel moet zetten (zoals het voorstel nu is), of moet laten staan zoals het nu is (tabellen "persoon_adres", "klant_adres" etc. met daarin rechtstreeks de adressen)

edit:
Ik begin te neigen naar het nieuwe voorstel, omdat je in de koppel tabel(len) eventuele extra informatie kwijt kunt. Ik denk dat het dan zoals al eerder vermeld is, geen ramp is om "orphaned" adressen te hebben, die je eventueel nog periodiek/handmatig zou kunnen opschonen...

[Voor 7% gewijzigd door MichielioZ op 15-05-2011 17:23]

[BurningSheep]

Hmm, de requirements zijn me nog steeds een beetje onduidelijk. Ik krijg het idee dat er verschillende type adressen zijn, maar misschien is dat verder niet zo relevant.

Als je in de persoon tabel een adres zet dan is dat een specifiek adres, meestal het adres waar hij woont, daar is er maar 1 van en kan dus in de tabel persoon.

In jouw geval, waar een persoon een X aantal aflever adressen heeft waar hij uit kan kiezen dan maak je daar een tabel voor. Ik vind het dan logisch om dat zonder koppeltabel te doen, dus een adres met een referentie naar persoon. Bij het verwijderen van de persoon kan je dan ook meteen al zijn adressen weggooien.

Of is er een requirement die het noodzakelijk maakt om een koppeltabel te gebruiken?

[MichielioZ]

BurningSheep schreef op zondag 15 mei 2011 @ 17:41:
Als je in de persoon tabel een adres zet dan is dat een specifiek adres, meestal het adres waar hij woont, daar is er maar 1 van en kan dus in de tabel persoon.

In jouw geval, waar een persoon een X aantal aflever adressen heeft waar hij uit kan kiezen dan maak je daar een tabel voor. Ik vind het dan logisch om dat zonder koppeltabel te doen, dus een adres met een referentie naar persoon. Bij het verwijderen van de persoon kan je dan ook meteen al zijn adressen weggooien.

Of is er een requirement die het noodzakelijk maakt om een koppeltabel te gebruiken?

Een koppeltabel is geen "requirement".
Het is wel een vereiste dat een persoon meerdere adressen kan hebben. (om o.a. de redenen die ik in m'n vorige post aanhaalde)
Zoals ik al zei is de huidige opbouw al zo dat er een "persoon_adres" tabel is, waarin zijn adressen staan.
Om dan daarnaast nog in de tabel "persoon" zijn woonadres te zetten (welke kan veranderen en als je 'm dan gewoon aanpast, de historie verliest) lijkt me een beetje dubbel op...
1 stap verder is dan om 1 tabel met alle adressen te hebben en alleen koppelingen te maken.
Voor ons heeft dat bepaalde voordelen, maar of het "echt" een goed idee is... ?!

[MichielioZ]

Ok, om het even duidelijker te maken (wat ik altijd denk bij de eerste post al te zijn, maar wat meestal niet zo blijkt te zijn )

Situatie 1: (huidige)
- persoon: id, voornaam, achternaam etc.
- persoon_adres: persoon_id, straat, huisnr etc.
- klant: id, bedrijfsnaam, KvK_nr, Btw_nr etc.
- klant_adres: klant_id, straat, huisnr etc.

Situatie 2: (voorstel)
- adres: id, straat, huisnr etc.
- persoon: id, voornaam, achternaam etc.
- persoon_adres: persoon_id, adres_id
- klant: id, bedrijfsnaam, KvK_nr, Btw_nr etc.
- klant_adres: klant_id, adres_id

Technisch gezien is het voor ons een nadeel dat situatie 1 geen adres_id heeft. (vandaar het voorstel)
Het enige echte nadeel wat ik zelf aan situatie 2 kan toekennen is dat er altijd een extra join uitgevoerd moet worden...

[Gomez12]

The Eagle schreef op zondag 15 mei 2011 @ 15:49:
Ook een toekomstig overlijden was in te vullen

Er was dan hopelijk wel een melding naar de toekomstige weduwe ingesteld zodat die het ook van te voren wist

BurningSheep schreef op zondag 15 mei 2011 @ 17:41:
Als je in de persoon tabel een adres zet dan is dat een specifiek adres, meestal het adres waar hij woont, daar is er maar 1 van en kan dus in de tabel persoon.

Hoe kom je erbij dat er maar 1 adres per persoon is? Een gemiddeld persoon heeft in zijn levensloop al meerdere adressen, dus daar zou ik ook op programmeren.

Zoals ik al eerder zei, als ik vorige week verhuisd ben en ik heb een vraag over waar de order van 3 weken geleden is gebleven dan wens ik niet te horen dat die 3 weken geleden naar mijn huidige adres gestuurd is (dat is namelijk onmogelijk, toen wist ik het adres nog niet eens)

[kwaakvaak_v2]

Gomez12 schreef op zondag 15 mei 2011 @ 23:49:


Zoals ik al eerder zei, als ik vorige week verhuisd ben en ik heb een vraag over waar de order van 3 weken geleden is gebleven dan wens ik niet te horen dat die 3 weken geleden naar mijn huidige adres gestuurd is (dat is namelijk onmogelijk, toen wist ik het adres nog niet eens)

Mag toch hopen dat je niet altijd op zo'n korte termijn verhuist

maar goed, qua data model. Ik zou mij vooral geen zorgen maken over extra joins met de adres tabel en gewoon per order een adres_id opslaan ipv lastige bochten met constraints te maken en in de adres tabel ook een gebruikers_id opslaan zodat je weet welk adres bij welke gebruiker hoort. En dan of met een datum bereik het huidige aktieve adres ophalen, of met een revision id waarbij het hoogste id telt als huidige revision.

Als relationale databases ergens goed in zijn, is dat wel het opslaan van, jawel relationele data. En of je daar nu wel of geen constraint opzet hangt meer af van je onder liggende logica. Als je het bijvoorbeeld met iets als doctrine/php maakt zorgt die ervoor dat relaties automagisch cascaded worden opgeslagen.

Echte snelheidswinst in je applicatie gaat imho niet echt zitten in dit soort micro optimalisaties. Het is wel leuk om mee bezig te zijn, maar echt praktisch nut heeft het niet echt