[SQL] Datamodel periodes en gebeurtenissen

[thioz]

Voor een eigen projectje ben ik bezig met een database model voor een applicatie waarbij er zowel bepaalde periodes opgeslagen worden als specifieke gebeurtenissen.

Omdat het zoeken door de database voornamelijk op datum / periode zal gaan zal de 'events' table als kern gebruikt wordt om locaties, personen etc aan te koppelen. Het uitgangspunt is daarom ook om altijd maar 1 query te hebben voor het ophalen van gebeurtenissen in een periode/op een datum en vervolgens alle gekoppelde data daarbij los te querien.

Bij het schetsen van use-cases tegen allerlei verschillende mogelijkheden aan en vroeg me af hoe jullie met bepaalde problemen om zouden gaan of welke oplossingen jullie zouden kiezen.


1) Stel ik wil opslaan dat Einstein geleefd heeft van 14 maart 1879 tot 18 april 1955 zou ik ervoor kunnen kiezen om dit als 1 record in de database te zetten als een 'event' met een start- en einddatum

lomschrijving	startdate	enddate
leven einstein	1879-3-14	1955-04-18

Probleem hiervan is als ik hier ook plaatsen aan zou willen koppelen, zou het moeilijk zijn om specifiek aan te geven waar hij geboren is en waar hij overleden is. Ook voelt het opslaan van een enkele gebeurtenis (dus geen periode) binnen dit datamodel niet lekker omdat je dan eigenlijk geen echte start of einddatum hebt. Dus de record voor de release van Portal 2 zou als volgt in de database komen te staan:

lomschrijving	startdate	enddate
Portal 2 release	2011-04-01	null

Voordeel is dat je een eenduidige manier hebt om zowel perioden als losse gebeurtenissen op te slaan, al is dat laatste dan enigszins een hack.

2) Je zou ervoor kunnen kiezen om het leven van Einstein op te slaan als 2 records, namelijk zijn geboortedatum en de datum waarop hij stierf. Echter is het enige verband tussen deze 2 records dat het gerelateerd is aan Einstein ... in het voorbeeld van Einstein is het enigszins logisch dat de laatste datum zijn sterfdatum is, maar voor iemand die nog leeft zou dit een andere betekenis kunnen hebben.

Een oplossing hiervoor zou kunnen zijn om een bepaald type aan een datum (bijv: geboorte, overlijden, huldiging, ontdekking, scheiding, trouwen) mee te geven maar dit brengt meteen het probleem mee dat er aan een event ook meerdere personen gekoppeld kunnen zijn.

3) Het los definieren van periodes en events die gekoppeld zijn aan die periodes.

periode_id

periodenaam

startdate

enddate

event_id

periode_id

eventtitle

eventdate

Het nadeel hiervan is dat niet ieder event gekoppeld is aan een periode en dit of in de code of in de query afgevangen moet worden, wat niet echt een probleem is maar wat wel zwaarder is door extra condities en joins.

Ik geloof niet dat hier een holy-grail oplossing voor is die voor iedere mogelijke use-case zal werken die zal werken zonder complexere queries. Wel ben ik benieuwd welke aanpak anderen zouden hanteren en waarom.

[Niemand_Anders]

Alle gebeurtenis heeft toch ook een einddatum. Afhankelijk waarvoor je het inzet zal inderdaad de einddatum gelijk zijn aan de startdatum, maar dat is toch geen probleem? Ik zou in elk geval geen null values toestaan op het enddate veld. Dat maakt je queries uiteindelijk alleen maar complexer.

Omdat ik er vanuit ga dat de invoer van de gebeurtenissen gebeurt middels een user interface en niet direct via de database, kun je one-day entries gemakkelijk afvangen in code.

code:

if (enddate == null) enddate = startdate;

Uit je omschrijving van de verschillende events maak ik op dat eigenlijk de persoon centraal staat.
Je kunt dus ook een tabel 'person' maken welke geboortedatum en sterf datum bevat. Deze persoon maakt dan gedurende zijn leven verschillende gebeurtenissen mee. Maar de vraag is welke andere lang lopende gebeurtenissen je nog meer aan een persoon wilt koppelen? Kleuterschool tijdvak, arbeids tijdvak, vakantie periodes?

Ik denk dat het verstandig is dat een test set van gebeurtenissen maakt en op basis daarvan je data model gaat definiëren. Schrijf bijvoorbeeld eens de gebeurtenissen van je familie leden eens op of die van een aantal vrienden.

Daarnaast vraag ik mij serieus af of de eis dat de informatie met een enkele query opgehaald moet kunnen worden hier wel thuis hoort. Via een query als 'select * from persons inner join (select personid, description from events where x=a union select personid, description from periods where y=b)' kun je de informatie alsnog via een enkele query benaderen. Maar het querien van je database is een implementatie detail.

[thioz]

@Niemand_Anders ...

Oplossing 1 lijkt mij in de meeste gevallen ook wel de meest kneedbare oplossing al vind ik ergens een event dat geen duurt heeft, zoals je beschrijft eigenlijk net zo onlogisch als een event dat geen einde heeft ... maar da's een beetje een non-issue

Het is niet echt zo dat personen centraal staan, juist de gebeurtenis staat centraal, dit kan zijn de dag waarop Clinton president werd (single day event), maar ook de koude oorlog (periode). Wel is het zo dat 'de koude oorlog' een iets te ruim begrip is en sowiezo verdeeld zou moeten worden in losse gebeurtenissen .. misschien zou het dan toch handig zijn om een 'period' table toe te voegen die een begin/eind heeft en een foreignkey in de event table.

In principe is de geboortedatum van een persoon een eigenschap van die persoon dus zou dit bij zijn entity in de database horen, alleen is het dan weer een stuk lastiger om alle data in 1 keer te querien.

Wel is het natuurlijk mogelijk om een geboortedatum ook als een event te zien zodat je in ieder geval in de periode 1800 t/m 1900 de geboorte van Einstein terugkrijgt.

Die 'eis' van alles in 1 query ophalen is natuurlijk niet heel realistisch, vooral vanwege de vele koppelingen (met allerlei media en gegevens) is dit al niet mogelijk. Wel wil ik zoveel mogelijk proberen om de basisdata, de events dus, in 1 query uit te lezen... wat prima zou gaan met opzet 1.

back to the drawingboard

[Janoz]

Persoonlijk zou ik eerder neigen naar een event tabel met een enkele datum. Waarom zou de geboortedag en de sterfdag van Einstein belangrijker zijn dan zijn in november 1915 gehouden lezing over zijn relativiteitstheorie?

Omdat het domein nogal losjes is (het gaat over een heleboel verschillende onderwerpen) zou ik het ook niet te strak willen modeleren. Event tabel zou dan gewooon een date en een description kunnen zijn. Om de boel toch aan elkaar te kunnen koppelen kun je met een soort 'tags' werken. Die tags kun je vervolgens wel behoorlijk uitgebreid doen. Daar zou je een categorie aan kunnen hangen (persoon, oorlog, land, enz).

Wanneer je dan bijvoorbeeld alle events met de tag Einstein ophaalt, dan krijg je keurig een levensloop terug met geboorte, alle events waar hij wat mee te maken had, zijn sterfdag en daarna ook nog items waarin hij gememoreerd wordt op wat voor manier dan ook.

Voor een oorlog geld hetzelfde. Daar is het verschil tussen enkel start en eind aan de ene kant en alle events aan de andere kant veel duidelijker. De tweede wereldoorlog begon en eindigde voor de verschillende landen op heel andere momenten. En 1 van de belangrijkste gebeurtenissen in die oorlog is niet eens het begin of het einde. D-day zit daar ergens tussenin.

[thioz]

@Janoz

Ik was zelf ook al tot een vergelijkbare opzet gekomen waarbij een 'event' en een 'periode' als losse entities gedefinieerd zijn en deze eventueel aan elkaar gekoppeld zijn dmv een foreign key.

Tags is ook een goed idee ... daarnaast wil ik een periode ook echt een hard gedefinieerd type meegeven als classificatie , dus bijvoorbeeld een oorlog, ambtstermijn, leven etc

Ik denk dat ik, zoals vaker, aanvankelijk te moeilijk dacht

thanks voor jullie input

[Janoz]

Periode zou ik niet eens meer als een losse entiteit zien, maar ik ken niet alle requirements.

[thioz]

@Janoz,

Periode zou eventueel wel handig kunnen zijn voor bepaalde views binnen de app. Gezien het ook niet echt een noodzaak is zal het period_id veld ook gewoon nillable zijn denk ik. bijvoorbeeld zou 'het leven van Einstein' als periode best handig zijn omdat je niet echt kunt querien op 'events tussen zijn geboorte-event en zijn stef-event' ... omdat de events niet echt die specificatie hebben.

Niet echt de meest sexy oplossing natuurlijk maar een koppeltabel vind ik ook enigszins overkil voor een een-op-een relatie. Je zou natuurlijk ook een event toe kunnen kennen aan meerdere 'periodes' maar dat zal ,zoals het er nu naar uitziet, nooit echt nodig zijn.

[Janoz]

thioz schreef op woensdag 18 mei 2011 @ 11:16:
@Janoz,

Periode zou eventueel wel handig kunnen zijn voor bepaalde views binnen de app. Gezien het ook niet echt een noodzaak is zal het period_id veld ook gewoon nillable zijn denk ik. bijvoorbeeld zou 'het leven van Einstein' als periode best handig zijn omdat je niet echt kunt querien op 'events tussen zijn geboorte-event en zijn stef-event' ... omdat de events niet echt die specificatie hebben.

Maar wanneer je alle events die gekoppeld zijn aan Einstein opvraagt heb je toch keurig die view? Of zie je de events die naar hem refereren na zijn dood niet relevant?

Niet echt de meest sexy oplossing natuurlijk maar een koppeltabel vind ik ook enigszins overkil voor een een-op-een relatie. Je zou natuurlijk ook een event toe kunnen kennen aan meerdere 'periodes' maar dat zal ,zoals het er nu naar uitziet, nooit echt nodig zijn.

In mijn oplossing heb je sowieso een koppeltabel tussen een event en de 'tags'.

[thioz]

@Janoz ..

Natuurlijk zijn de events die refereren aan Einstein na zijn dood wel relevant... maar onder 'leven' van Einstein zou ik echter alleen de periode aan willen geven dat hij daadwerkelijk ademde .

Vandaar dat een soort periode met een begin-event en een eind-event makkelijk is om dit aan te geven. Met deze constructie kunnen ook meerdere periodes over elkaar heen gelegd worden.

Er komen daarnaast ook nog tags, personen, locaties / landen / steden die aan events gekoppeld gaan worden en deze zullen uiteraard allemaal via koppeltabellen werken.

[cariolive23]

De geboorte van Einstein is een event dat vrij kort heeft geduurd, dat geldt ook voor zijn overlijden. De periode tussen deze gebeurtenissen noemen we "leven", dat is een afgeleide van deze gebeurtenissen.