[DataModel] Relatie archief

[Verwijderd]

Voor een administratiesysteem waar ik momenteel aan werk zit ik met een vraagje. Misschien beschrijf ik het voortraject iets te uitgebreid, maar ik heb graag duidelijk wat ik doe en waarom.

Stel dat we uitgaan van twee tabellen 'personen' en 'functies'. Deze tabellen hebben de volgende relatie: Één persoon kan één of meer functies hebben en een functie kan door nul of meer personen uitgevoerd worden. Maw: een meer-op-meer relatie. Er is dus een tussentabel nodig om deze twee tabellen aan elkaar te koppelen. Laten we deze tabel: 'persoon_heeft_functie' noemen. Door in deze tabel de twee foreign keys op te nemen (een primary key van personen en een primary key van fucties) kunnen unieke relaties gelegd worden tussen personen en functies, een eigen primary key voor de tussentabel is hierbij niet nodig. Immers, de persoon 'Jantje' en de relatie met de functie 'directeur' kan maar één keer voorkomen.



Nu zit ik echter met het volgende. Ik wil graag een archief bijhouden zodat ik over een aantal jaren nog eens kan nakijken welke functies iemand gehad heeft of wie welke functies bekleed heeft. Met bovenstaande model zal dit niet lukken, want op het moment dat Jantje vertrekt als directeur zal de rij in de tussentabel 'persoon_heeft_functie' die deze relatie beschrijft moeten verdwijnen om te voorkomen dat Jantje onterecht als directeur aangemerkt zou kunnen worden na zijn aftreden.

Om toch de relatie te kunnen behouden voor future reference heb ik bedacht om de tussentabel uit te breiden met de velden 'startdatum' en 'einddatum'



Echter, nu zit ik met het probleem dat theoretisch gesproken iemand twee keer een bepaalde functie kan bekleden. Laten we stellen dat Jantje na een aantal jaar weer terug komt en wederom de functie directeur zou gaan bekleden dan zou wederom de relatie tussen persoon en functie gelegd worden. Op key niveau zou deze relatie exact hetzelfde zijn als de relatie van een aantal jaren eerder, waardoor de relatie niet meer uniek is!

Nu zou ik ervoor kunnen kiezen om een primary key te maken van startdatum. De foreign keys + de startdatum zouden een unieke combinatie vormen. Ophalen van de huidige functie bezetting zou vervolgens kunnen door een SELECT querie te doen met als WHERE clausule einddatum = null. Echter, dit werkt niet wanneer de startdatum of de einddatum van een functie in de toekomst ligt, maw: queries op deze tabel worden complex, omdat er altijd gecontroleerd moet worden (wanneer de huidige bezetting opgevraagd wordt) of de startdatum niet in de toekomst ligt en of de einddatum leeg is of dat de einddatum in de toekomst ligt.

Het lijkt mij, theoretisch gesproken, dat het van startdatum een primary key maken een prima oplossing is voor de archief functie. Echter, vraag ik mij af of het zomaar mag om een primary key toe te voegen aan een tussentabel? Daarnaast, moet een primary key opzich zelf niet altijd uniek zijn? In dat geval voldoet een datum element natuurlijk niet.

Alvast bedankt voor de hulp!

[whoami]

Daarnaast, moet een primary key opzich zelf niet altijd uniek zijn

Ja, da's net de bedoeling van een primary key: een row uniek in een tabel kunnen identificeren.

Je zou een unique constraint moeten maken op persoon_id, functie_id en startdatum.
Dit kan je primary key worden, maar hoeft niet persé. Je kan er ook voor kiezen om een bijkomend Id veld als PK te definieren.

Ivm je complexe queries:
Ik heb hier @ work een gelijkaardige situatie: een bepaald item kan verschillende 'regels' hebben, en er kan slechts 1 regel de huidige zijn. Hier heb ik in deze 'regels' tabel een bijkomend veld gemaakt die aanduidt of een regel-record nu geldig is, in de toekomst geldig wordt, of vroeger geldig was. Dit veld wordt iedere nacht dmv een batch process aangepast.

Echter, dit werkt niet wanneer de startdatum of de einddatum van een functie in de toekomst ligt

Dit snap ik niet goed... Als je filtert op je eind-datum, wat heeft je start-datum daar dan mee te maken ?
En kan het voorvallen dat je einddatum al gekend is op voorhand ? Bv, bij het aanvangen van een functie is de einddatum al gekend, of, vandaag is het reeds geweten dat Pietje volgende maand die bepaalde functie niet meer heeft ?

Om de huidige functie te bepalen zou je toch deze where kunnen doen:

code:

...
WHERE startDatum >= TODAY AND ( einddatum <= TODAY or einddatum IS NULL )

?

[ Voor 28% gewijzigd door whoami op 11-10-2006 16:41 ]

[Verwijderd]

whoami schreef op woensdag 11 oktober 2006 @ 16:37:
[...]
Je zou een unique constraint moeten maken op persoon_id, functie_id en startdatum.
Dit kan je primary key worden, maar hoeft niet persé. Je kan er ook voor kiezen om een bijkomend Id veld als PK te definieren.

Dat laatste heb ik ook aan gedacht, maar leek mij juist onzinnig aangezien de combinatie van de aangegeven keys al uniek is, vandaar dat de unique constraint mij momenteel de beste oplossing lijkt (al moet ik wel even na gaan kijken hoe ik die aan kan maken )

Ivm je complexe queries:
Ik heb hier @ work een gelijkaardige situatie: een bepaald item kan verschillende 'regels' hebben, en er kan slechts 1 regel de huidige zijn. Hier heb ik in deze 'regels' tabel een bijkomend veld gemaakt die aanduidt of een regel-record nu geldig is, in de toekomst geldig wordt, of vroeger geldig was. Dit veld wordt iedere nacht dmv een batch process aangepast.

Daar heb ik aan gedacht, maar als ik het database technisch op kan lossen vind ik dat wel net wat mooier.

[...]
Dit snap ik niet goed... Als je filtert op je eind-datum, wat heeft je start-datum daar dan mee te maken ?

Een startdatum kan in de toekomst liggen, als je een lijst wil met actieve persoon-functie relaties dan zal je dus ook moeten controleren of een relatie reeds actief is.

[...]
En kan het voorvallen dat je einddatum al gekend is op voorhand ? Bv, bij het aanvangen van een functie is de einddatum al gekend, of, vandaag is het reeds geweten dat Pietje volgende maand die bepaalde functie niet meer heeft ?

Dat is zeker mogelijk, iemand kan een bepaalde opzegtermijn in acht nemen.

[joepP]

Als je dit soort integriteit wil garanderen aan de databasekant ontkom je volgens mij niet aan triggers, mits deze in jouw DBMS ondersteund worden. Dan kan je voor een INSERT query gewoon controleren of deze wel geldig is (geen overlap met huidige actieve functie), en idem voor UPDATES. Ook kan je dan eenvoudig een veldje toevoegen dat een bitje zet of het dienstverband momenteel actief is (of niet). Een extra bijkomstigheid is wel dat je elke nacht een batch zal moeten draaien om de verlopen dienstverbanden niet langer actief te zetten.

Een andere mogelijkheid om je queries leesbaar te houden is om een default waarde (ver in de toekomst) voor einddatum op te nemen.

[BertS]

joepP schreef op woensdag 11 oktober 2006 @ 17:40:
Een andere mogelijkheid om je queries leesbaar te houden is om een default waarde (ver in de toekomst) voor einddatum op te nemen.

Dat zou mijns inziens verboden moeten worden... Dan ga je foutieve data invullen (hoe komt dat op overzichten, als je een lijstje met functies incl start/einddatum gaat presenteren aan de gebruiker) om je queries (waar alleen de ontwikkelaar mee zit, en niet de eindgebruiker) eenvoudiger te houden.

Dus je gaat concessies doen aan de functionaliteit (gebruiker ziet verkeerde datum op lijstje), om ontwikkeltijd te besparen.
Dan kun je net zo goed geen software ontwikkelen

[Verwijderd]

joepP schreef op woensdag 11 oktober 2006 @ 17:40:
Als je dit soort integriteit wil garanderen aan de databasekant ontkom je volgens mij niet aan triggers, mits deze in jouw DBMS ondersteund worden. Dan kan je voor een INSERT query gewoon controleren of deze wel geldig is (geen overlap met huidige actieve functie), en idem voor UPDATES.

Dat is een goed idee. Ik moet zeggen dat ik nooit heb leren werken met triggers (kende het niet dus), maar na enige snelle research denk ik wel dat ik het zou kunnen gebruiken.

Ook kan je dan eenvoudig een veldje toevoegen dat een bitje zet of het dienstverband momenteel actief is (of niet). Een extra bijkomstigheid is wel dat je elke nacht een batch zal moeten draaien om de verlopen dienstverbanden niet langer actief te zetten.

Ik wil toch me best doen om geen gebruik te maken van nightly batches. Ik wil zoveel mogelijk proberen de zaken in de database op te lossen, vandaar dat ik wel gecharmeerd ben van triggers.

Een andere mogelijkheid om je queries leesbaar te houden is om een default waarde (ver in de toekomst) voor einddatum op te nemen.

Ik ben het helemaal met bee-es eens dat dat geen goed plan is, for the same reasons.

[joepP]

bee-es schreef op woensdag 11 oktober 2006 @ 18:44:
[...]

Dat zou mijns inziens verboden moeten worden... Dan ga je foutieve data invullen (hoe komt dat op overzichten, als je een lijstje met functies incl start/einddatum gaat presenteren aan de gebruiker) om je queries (waar alleen de ontwikkelaar mee zit, en niet de eindgebruiker) eenvoudiger te houden.

Helemaal mee eens, ik gaf het vooral aan om aan te geven dat je eigenlijk niet om wat naardere queries heen kan.

[Siliakus]

Als je er voor kiest om de twee foreign keys niet te bundelen tot een primary key, dwing je geen unieke combinaties af. Kortom, de combinatie tussen persoon en functie kan dus meerdere keren voor komen.

Dus als je dan startdatum en einddatum als kolommen op neemt, kan je op die manier een archief opbouwen. Dan heb je ook geen extra kolom nodig voor de funtie die de persoon nu vervult, dat los je op in je query: sysdate() between startdatum and einddatum.

Helemaal handig is natuurlijk als je de einddatum van de huidige functie niet invult: select ..... where einddatum is null. Dan tackel je dat probleem ook gelijk.

[whoami]

Siliakus schreef op woensdag 11 oktober 2006 @ 20:43:
Als je er voor kiest om de twee foreign keys niet te bundelen tot een primary key, dwing je geen unieke combinaties af. Kortom, de combinatie tussen persoon en functie kan dus meerdere keren voor komen.

Niet als je er een unique constraint / unique index op legt.
Hier heeft het niet veel zin om een aparte PK te maken, maar het is wel mogelijk.

Helemaal handig is natuurlijk als je de einddatum van de huidige functie niet invult: select ..... where einddatum is null. Dan tackel je dat probleem ook gelijk.

Zoals de ts al zei, is dat niet mogelijk... Soms is een einddatum op voorhand bekend.

[Siliakus]

De unique constraint of het aanmaken van een index was je eigen gedachtenlijn. Mijn commentaar is als alternatieve oplossing op het probleem beschreven in de topic start bedoelt; het archiveren van functies in het verleden vervult door personen.

Ik wijs de topicstarter er op dat hij denkfout maakt bij de zin 'Door in deze tabel de twee foreign keys op te nemen (een primary key van personen en een primary key van fucties) kunnen unieke relaties gelegd worden tussen personen en functies'. Alleen bij het creeeren van een composite (primary) key op grond van de twee opgenomen foreign keys dwing je een unieke relatie tussen beiden af. Kortom: de persoon 'Jantje' en de relatie met de functie 'directeur' kan wel degelijk meerdere keren voorkomen!

En het verschil tussen verleden en huidige functie maak je dan inderdaad door twee kolommen, startdatum en einddatum, toe te voegen. Huidige datum tussen startdatum en einddatum of einddatum is null bepaalt dan wat de huidige functie is. Huidige datum > einddatum bepaalt dan de in het verleden vervulde functies.

[Verwijderd]

Siliakus schreef op woensdag 11 oktober 2006 @ 20:43:
Als je er voor kiest om de twee foreign keys niet te bundelen tot een primary key, dwing je geen unieke combinaties af. Kortom, de combinatie tussen persoon en functie kan dus meerdere keren voor komen.

Het bundelen van de keys kan ik in dit geval toch heel eenvoudig doen door de volgende regel in mijn CREATE query op te nemen?

SQL:

PRIMARY KEY(persoon_id, functie_id, startdatum),

Wat is het verschil tussen het bundelen tot een primary key en het maken van unique constraints?

[joepP]

whoami schreef op woensdag 11 oktober 2006 @ 20:52:
Niet als je er een unique constraint / unique index op legt.
Hier heeft het niet veel zin om een aparte PK te maken, maar het is wel mogelijk.

Ik weet het niet...

Soms zie je dat je achteraf toch aan die tabel wil refereren. In dit geval bijvoorbeeld door dingen als salaris, functioneringsgesprekken of andere specifieke opmerkingen aan dienstverbanden te koppelen. Dat gaat behoorlijk matig als er geen primaire key is, en achteraf toevoegen is ook niet zo fijn in een live omgeving.

Ik zal toch een autoinc veldje toevoegen eigenlijk.

[Siliakus]

Maar WAAROM maak je een primary key. Op die manier gaat juist de functionaliteit die je zoekt (archiveren!) verloren!

Een primary key is btw een unique key + index in één.

[Verwijderd]

Siliakus schreef op donderdag 12 oktober 2006 @ 17:11:
Maar WAAROM maak je een primary key. Op die manier gaat juist de functionaliteit die je zoekt (archiveren!) verloren!

Voor alle duidelijkheid: refereer je nu aan mijn post over het bundelen van de keys? Of aan het voorstel van joepP om toch nog een unieke ID toe te voegen?

[Siliakus]

Aan jouw eigen post, waarin je stelt dat je heel gemakkelijk een primary key kan aanmaken op drie kolommen.

*hier stond nog wat over indices, maar dat is nog niet relevant en daar is meer informatie voor nodig*

[ Voor 84% gewijzigd door Siliakus op 12-10-2006 19:18 ]

[Verwijderd]

Siliakus schreef op donderdag 12 oktober 2006 @ 19:07:
Aan jouw eigen post, waarin je stelt dat je heel gemakkelijk een primary key kan aanmaken op drie kolommen.

Mogelijk zie ik wat over het hoofd, maar waarom gaat de archiverings functie verloren als ik op bovenstaande methode een primary key creeer op drie velden??

Hoe zou ik dit dan beter kunnen doen? Met unique constraints zoals hieronder?

SQL:

CONSTRAINT persoon_heeft_functie UNIQUE (persoon_id, functie_id, startdatum)

Wat is dan, in dit geval, concreet het verschil tussen het maken van een primary key op meerdere velden en het maken van een unique constraint??

edit:
aangevuld met unique constraints vraag

[ Voor 32% gewijzigd door Verwijderd op 13-10-2006 19:45 ]

[Verwijderd]

Klein schopje...

Kan iemand mijn laatste vraag beantwoorden? Wat is dan in dit specifieke geval het verschil (in het uiteindelijke gebruik e.d.) tussen het gebruiken van een samengestelde primary key en het gebruiken van een enkele primary key icm een unique constraint op de foreign keys en de startdatum?

[Siliakus]

Een samengestelde primary key betekend dat de kolommen over welke je hem samensteld alleen mogen voorkomen in unieke combinaties. Dus in jouw geval mag (persoon_id, functie_id, start_datum) VALUES (1,1,17-06-1980) maar één keer voor komen.

Een primary key op persoon_id en een unique constraint op de combinatie functie_id, start_datum betekent in dit geval effectief het zelfde. (1, 1, 17-06-1980) mag maar één keer voor komen.

Kortom, geen primary key definieren en geen unique constraint toepassen. Indexen op persoon_id en functie_id en gaan met die banaan!

[Verwijderd]

Thx Siliakus, het duurt even, maar ik geloof dat ik eindelijk begrijp waar je heen wilt. Door geen primairy key op te nemen en alleen de persoon_id en de functie_id te indexeren zijn de dubbele combinaties mogelijk.

Anderzijds zou een unique constraint op de beide indexen icm de startdatum er wel voor zorgen dat zaken onmogelijk (per ongeluk waarschijnlijk) dubbel ingevoerd kunnen worden.