[SQL] historische relaties en constraints

[Foeijonghaai]

Bij enkele projecten op mijn werk hebben we situaties waarbij we de geschiedenis van relaties tussen bepaalde objecten willen vastleggen.

Een voorbeeld:
- klasse Account (een account)
- klasse Sim (een simkaart)
- Gekoppeld aan elkaar middels een koppeltabel accountsimhistory.

De koppeltabel heeft een samengestelde sleutel:

• De FK naar account
• De FK naar sim
• De starttijd

Verder geldt:

• Een sim hoort bij een bepaalde klant tot de klant hem niet meer hoeft; er is dus een start en een stop. Per definitie: als stop == null, dan geldt de relatie nog.
• Een sim kan hergebruikt worden. Bijvoorbeeld bij dezelfde klant, maar ook bij andere klanten.

Zie ook


Om het niet onnodig ingewikkeld te maken heb ik even de rest van de attributen van beide klassen achterwege gelaten. Ook de (overbodige) attributen van de koppeltabel heb ik weggelaten.

UIteraard zijn er ook constraints:

• een sim kan niet tegelijkertijd in gebruik zijn door meerdere accounts
• een account kan geen meerdere sims tegelijkertijd hebben

Momenteel check ik de constraints in de software. Dat is uiteraard niet de beste oplossing, en momenteel ervaar ik ook problemen hierdoor (bijv. wanneer een gebruiker dubbelklikt op een button (ipv enkel klik) in de webapplicatie).

Ik ben dus bezig om de constraints door de database te laten afdwingen.

Het lijkt me (misschien zie ik iets over het hoofd) dat ik dit niet met standaard row-level of column-level constraints kan afdwingen. Immers een sim kan best meerdere keren voorkomen in de tabel, een account kan meerdere keren voorkomen in de tabel en de huidige primary key beschermt niet tegen meerdere relaties die 'tegelijkertijd' bestaan.

Ik heb een trigger gemaakt die voor een insert of een update kijkt of de actie zal leiden tot inconsistentie, en als dat zo is, de actie tegenhoudt. Een delete kan ik negeren, aangezien deze niet voor overlappingen kan zorgen.

De trigger is behoorlijk 'groot' geworden: andere behandeling voor een insert/update. Onderscheid tussen 'open' en 'gesloten' relaties. Controle op overlap voor account. Controle voor overlap voor sim.

Ik heb van te voren het een ander opgezocht en kwam onder andere stukken tegen over zogenaamde 'temporal databases' maar dat is een net iets andere insteek.

Ik kwam dit document tegen http://www.dcs.warwick.ac.../TemporalData.Warwick.pdf en daarin geven ze aan dat het erg lastig is en het gedeelte dat over constraints gaat komt bij mij niet duidelijk over.

De vraag
Ik vroeg me af of ik niet het wiel opnieuw zat uit te vinden en of hier geen andere (standaard) oplossingen voor zijn.

[joppybt]

Voor elke SIM-kaart mag er maar maximaal één record zijn in accountsimhistory met lege stopdatum.

Kun je niet gewoon een unique-constraint (index) zetten op sim+stopdatum?

SQL-server dwingt bijvoorbeeld af dat er dan maar één keer de NULL-waarde voorkomt.

[Foeijonghaai]

joppybt schreef op donderdag 08 december 2011 @ 15:41:
Voor elke SIM-kaart mag er maar maximaal één record zijn in accountsimhistory met lege stopdatum.

Kun je niet gewoon een unique-constraint (index) zetten op sim+stopdatum?

SQL-server dwingt bijvoorbeeld af dat er dan maar één keer de NULL-waarde voorkomt.

Bedankt
Dit is deels een oplossing: het lost het probleem op met 'er mag maar 1 'open' relatie zijn met een sim'.
Hetzelfde zou ik kunnen doen voor account. Het maakt de getriggerde functie al wat kleiner.

Maar het lost het probleem met de 'overlappende' relaties nog niet op.

[The Eagle]

Je zit het wiel opnieuw uit te vinden

Ik werk dagelijke met PeopleSoft (ERP suite), en daarin wordt dit opgelos middels een effective date en een effective status - ingangs datum en bijbehorende status.
Voor iedere mutatie op status (in jouw geval zou je start - stop als status kunnen zien) komt er een rij bij met een ingangsdatum.
Vervlgens doe ik, als ik de meest recente status op wil halen, een inner self join:

code:

SELECT A.CURRENCY_CD  
 , A.EFFDT  
 , A.DESCR  
 , A.DESCRSHORT  
 , A.COUNTRY  
 , A.CUR_SYMBOL  
 , A.DECIMAL_POSITIONS  
 , A.SCALE_POSITIONS  
  FROM PS_CURRENCY_CD_TBL A  
 WHERE A.EFFDT = (  
 SELECT MAX(I.EFFDT)  
  FROM PS_CURRENCY_CD_TBL I  
 WHERE I.CURRENCY_CD = A.CURRENCY_CD  
   AND I.EFFDT <= 
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')  
   AND I.EFF_STATUS = 'A')

In bovenstaand geval gaat het om een tabel met currency codes, maar ik denk dat je mijn punt wel snapt
Bij jou zou eff_status dus vervangen moeten worden door iets als "STOP is not NULL'.

Als jij in je applicatie validaties wilt op het wel of niet beschikbaar zijn van een SIM, dan zou je dus een view a la bovenstaande moeten maken en daartegen valideren.

[ Voor 7% gewijzigd door The Eagle op 08-12-2011 16:01 ]

[Motrax]

Je wilt een statustabel toevoegen waarin de toestand van de sim-kaart is opgenomen. In die toestand is de simkaart, eigenenaar en een tijdsafhankelijke indicatie opgenomen.

Ik zou voor zoiets kiezen:
sim-id
account-id
start datum
einddatum

En dan een index op de eerste drie velden, de einddatum kan leeg zijn wanneer het om de laatste toestand gaat. Eventueel voor performance nog een veld toevoegen met een boolean / 1/0 etc om aan te geven wat de meest recente toestand is.

Dan heb je nog wel de overweging of je een van-tot datum gebruikt, of een van tot-en-met datum. En wat je doet als een sim meer dan 1x per dag van eigenaar wisselt.

In data warehousing gaat het om een type 2 dimensie. Ik weet niet hoe de termen zijn in transactionele databases.

Grrr The Eagle Maar we zitten op één lijn gelukkig

[ Voor 3% gewijzigd door Motrax op 08-12-2011 16:01 ]

[The Eagle]

Hey, dit is mijn dagelijks brood

[Motrax]

De mijne ook, maar dan meer dat ik data uit ERP systemen haal en eventueel tijdsafhankelijk in een BI omgeving laad

P.S. SAP is beter

[ Voor 9% gewijzigd door Motrax op 08-12-2011 16:04 ]

[Foeijonghaai]

The Eagle schreef op donderdag 08 december 2011 @ 16:00:
Je zit het wiel opnieuw uit te vinden

Ik werk dagelijke met PeopleSoft (ERP suite), en daarin wordt dit opgelos middels een effective date en een effective status - ingangs datum en bijbehorende status.
Voor iedere mutatie op status (in jouw geval zou je start - stop als status kunnen zien) komt er een rij bij met een ingangsdatum.
Vervlgens doe ik, als ik de meest recente status op wil halen, een inner self join:

code:

SELECT A.CURRENCY_CD  
 , A.EFFDT  
 , A.DESCR  
 , A.DESCRSHORT  
 , A.COUNTRY  
 , A.CUR_SYMBOL  
 , A.DECIMAL_POSITIONS  
 , A.SCALE_POSITIONS  
  FROM PS_CURRENCY_CD_TBL A  
 WHERE A.EFFDT = (  
 SELECT MAX(I.EFFDT)  
  FROM PS_CURRENCY_CD_TBL I  
 WHERE I.CURRENCY_CD = A.CURRENCY_CD  
   AND I.EFFDT <= 
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')  
   AND I.EFF_STATUS = 'A')

In bovenstaand geval gaat het om een tabel met currency codes, maar ik denk dat je mijn punt wel snapt
Bij jou zou eff_status dus vervangen moeten worden door iets als "STOP is not NULL'.

Als jij in je applicatie validaties wilt op het wel of niet beschikbaar zijn van een SIM, dan zou je dus een view a la bovenstaande moeten maken en daartegen valideren.

Een sim die niet gekoppeld is aan een account, heeft in mijn oplossing geen rij met stop == null.
Een account die niet gekoppeld is aan eensim, heeft in mijn oplossing geen rij met stop == null.
Die 'validatie' is eenvoudig te doen en is het probleem niet zo. En om dit af te dwingen voordat de data de DB in gaat, lijkt de suggestie van joppybt voldoende (ga binnenkort testen).

De grootste moeilijkheid is dat er ook extra records ingeschoten kunnen worden (met stop ingevuld) of updates gedaan kunnen worden waarbij de relatie niet mag overlappen met andere bestaande relaties voor die sim en dat account.

Misschien begrijp ik je bijdrage niet goed.

[Foeijonghaai]

Motrax schreef op donderdag 08 december 2011 @ 16:00:
Je wilt een statustabel toevoegen waarin de toestand van de sim-kaart is opgenomen. In die toestand is de simkaart, eigenenaar en een tijdsafhankelijke indicatie opgenomen.

Ik zou voor zoiets kiezen:
sim-id
account-id
start datum
einddatum

En dan een index op de eerste drie velden, de einddatum kan leeg zijn wanneer het om de laatste toestand gaat. Eventueel voor performance nog een veld toevoegen met een boolean / 1/0 etc om aan te geven wat de meest recente toestand is.

Dan heb je nog wel de overweging of je een van-tot datum gebruikt, of een van tot-en-met datum. En wat je doet als een sim meer dan 1x per dag van eigenaar wisselt.

Dit is precies wat ik heb en wat in mijn plaatje staat (ok, plaatje is een beetje aan de kleine kant). Voor start en stop heb ik timestamps, dus datum+tijd.

In data warehousing gaat het om een type 2 dimensie. Ik weet niet hoe de termen zijn in transactionele databases.

Ok.

Bovenstaande is voldoende om de informatie op te kunnen slaan. Maar voorkomt niet dat er onjuiste data de DB in kan.

[Foeijonghaai]

joppybt schreef op donderdag 08 december 2011 @ 15:41:
Voor elke SIM-kaart mag er maar maximaal één record zijn in accountsimhistory met lege stopdatum.

Kun je niet gewoon een unique-constraint (index) zetten op sim+stopdatum?

SQL-server dwingt bijvoorbeeld af dat er dan maar één keer de NULL-waarde voorkomt.

Ik heb je suggestie getest, maar helaas geen resultaat. Misschien wel voor SQL-server, maar niet voor Postgres:

In general, a unique constraint is violated when there are two or more rows in the table where the values of all of the columns included in the constraint are equal. However, null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases may not follow this rule. So be careful when developing applications that are intended to be portable.

Zie ook http://www.postgresql.org...atic/ddl-constraints.html

Jammer maar helaas. Ik vond je oplossing wel elegant.

[The Eagle]

Waarom wil je validaties eigenlijk met constraints afdwingen? Dat soort dingen hoort in je applicatielogica te zitten, niet in je DB namelijk

[Foeijonghaai]

The Eagle schreef op vrijdag 09 december 2011 @ 14:38:
Waarom wil je validaties eigenlijk met constraints afdwingen? Dat soort dingen hoort in je applicatielogica te zitten, niet in je DB namelijk

Omdat ik dan de volgende dingen moet doen, afwisselend in de software en de database:

1. opvragen alle relaties voor dat account. Software vraagt database, database antwoord software.
2. kijken of daar 'open' relaties voor zijn (in de software)
3. opvragen alle relaties voor die sim. Software vraagt database, database antwoord software.
4. kijken of daar 'open' relaties voor zijn (in de software)
5. bepalen of er overlappingen gaan ontstaan voor dat account (in de software)
6. bepalen of er overlappingen gaan onstaan voor die sim (in de software)
7. indien alles ok, record inserten of updaten (software doet update naar database).

Dit kan allerlei concurrency-problemen geven. Dat is natuurlijk op te lossen met locks. Maar dan ben ik ook afhankelijk van de kundigheid van mijn collega's (helaas gaan we op meerdere manieren naar de database, PHP, Java, wel/geen ORM).

Als ik het met een trigger oplos:

• kan ik gewoon een insert/update doen die wel of niet goed gaat.
• wordt alles door de DB afgehandeld.
• is het in 1 keer opgelost (ivm meerdere applicaties die helaas op hun eigen manier naar de database gaan)

[The Eagle]

Ik snap je keuze. Ik blijf er echter bij dat dit soor dingen nieu op DB niveau opgelost moeten worden. Ik zou zelf een query schrijven. Beetje SQL'er moet dat gewoon kunnen namelijk. Wordt geen simpele select, dat snap je, maar dit soort dingen hoort pertinent niet op een DB thuis.

[Foeijonghaai]

The Eagle schreef op vrijdag 09 december 2011 @ 15:49:
Ik snap je keuze. Ik blijf er echter bij dat dit soor dingen nieu op DB niveau opgelost moeten worden. Ik zou zelf een query schrijven. Beetje SQL'er moet dat gewoon kunnen namelijk. Wordt geen simpele select, dat snap je,

Het zijn een behoorlijk aantal selects en daarna pas een keer een insert. Ik denk niet dat dit met 1 query op te lossen is.

maar dit soort dingen hoort pertinent niet op een DB thuis.

Kun je dat onderbouwen (artikelen, boeken)? No flame intended. Ik wil graag weten hoe het 'hoort'.

[The Eagle]

Een DB dient voor dataopslag en het serveren van data. Een applicatie is software, waarin je je (bedrijfs)logica opneemt. Dus ook validaties. Simple as that.
Een DB is niet primair gemaakt voor validaties van data t.o.v. andere data. Hooguit voor validaties t.a.v. de data die je er in stop (is een veld numeriek of alfanumeriek). Dat het kan wil niet zeggen dat het moet.

Ik kan je helaas geen linkje geven, maar je kunt op je klompen aanvoelen dat DB logica anders is als applicatielogica. vergelijk het maar met het verschil tussen financiele en personeels processen: salarissen vormen de overlap, maar het uitbetalen daarvan wil je toch echt doen in het systeem waar het thuis hoort: het financiele.

Edit: en die insert zou je op kunnen lossen door bijv iets als

insert into table where not exists (<selectvalidatie>)

toe te passen. Hoe je dat precies het beste aan zou kunnen passen weet ik niet. Wellicht dat je ook iets met meerdere roundtrips richting de server kunt doen - ik ken je applicatie niet namelijkl

[ Voor 18% gewijzigd door The Eagle op 09-12-2011 16:23 ]

[cariolive23]

The Eagle schreef op vrijdag 09 december 2011 @ 16:20:
Een DB dient voor dataopslag en het serveren van data. Een applicatie is software, waarin je je (bedrijfs)logica opneemt. Dus ook validaties. Simple as that.

Wanneer je met "een DB" een DBMS bedoelt, dan ben ik het niet met je eens. Een DBMS doet namelijk veel meer dan alleen wat bits en bytes opslaan. En corrupte data ontstaat 9 van de 10 keer juist doordat er te weinig validaties in de database worden gedaan. Ongeacht de data cq troep wat wordt aangeleverd door de applicaties, mag er nooit corrupte data in de database terecht komen en/of ontstaan. Zo zijn er vele validaties die onmogelijk door een applicatie kunnen worden uitgevoerd, denk maar eens aan zo iets simpels als een unique constraint. Of wil je echt bij iedere insert of update de tabellen gaan locken? Goed voor de performance...

Gebruik een DBMS als een DBMS en laat deze voor jou het werk doen.

Zowel applicaties als DBMS-en hebben hun sterke en zwakke kanten, zorg er voor dat je deze kent.

[farlane]

cariolive23 schreef op vrijdag 09 december 2011 @ 18:01:
Zowel applicaties als DBMS-en hebben hun sterke en zwakke kanten, zorg er voor dat je deze kent.

Ik ben eigenlijk wel benieuwd of je een oplossing hebt voor het probleem van de TS? Ik ben het nl met je eens en heb eigenlijk eenzelfde probleem maar geen goede oplossing 'in de database'.

[joppybt]

farlane schreef op vrijdag 09 december 2011 @ 20:12:
[...]
Ik ben eigenlijk wel benieuwd of je een oplossing hebt voor het probleem van de TS? Ik ben het nl met je eens en heb eigenlijk eenzelfde probleem maar geen goede oplossing 'in de database'.

Je zou voor de openstaande einddatum een vaste waarde kunnen gebruiken, bijvoorbeeld 31-12-9999.
Dan kun je even vooruit en kun je wel de unieke index gebruiken.
Niet super-elegant maar altijd beter dan corrupte data.

[cariolive23]

joppybt schreef op vrijdag 09 december 2011 @ 22:24:
[...]

Je zou voor de openstaande einddatum een vaste waarde kunnen gebruiken, bijvoorbeeld 31-12-9999.
Dan kun je even vooruit en kun je wel de unieke index gebruiken.
Niet super-elegant maar altijd beter dan corrupte data.

Ik zou de datum 'infinity' gebruiken, oneindig. Dat werkt prima, ook met een unique constraint.

[The Eagle]

Dan moet die "infinity" datum wel kunnen bestaan. Oracle kent zoiets namelijk niet
Wordt overigens meestal opgelost dor idd een datum als 31-12-2099 te kiezen. Voor jaar 9999 zou ik niet gaan iig - dat gaat niet altijd standaard goed qua conversie namelijk

cariolive23 schreef op vrijdag 09 december 2011 @ 18:01:
[...]
En corrupte data ontstaat 9 van de 10 keer juist doordat er te weinig validaties in de database worden gedaan. Ongeacht de data cq troep wat wordt aangeleverd door de applicaties, mag er nooit corrupte data in de database terecht komen en/of ontstaan.

Eens, corrupte data zou er nooit in mogen staan. Echter waar jij over praat is geen corrupte data, maar simpelweg foutieve invoer. Dat die vanuit bedrijfsperspectief misschien corrupt is zou kunnen, maar vanuit DB perspectief is dat geen corrupte data. Data is pas corrupt als ik in een CHAR veld "piet" heb ingevoerd en opgeslagen, en ik hem bij het uitlezen terugkrijg als "%*(^". En daar zijn DBMS'en wel goed voor: het ondervangen van corrupt gaan van data

Zo zijn er vele validaties die onmogelijk door een applicatie kunnen worden uitgevoerd, denk maar eens aan zo iets simpels als een unique constraint. Of wil je echt bij iedere insert of update de tabellen gaan locken? Goed voor de performance...

Serieus: noem mij 1 validatie die alleen op DB niveau uitgevoerd kan worden. Ik ben oprecht benieuwd namelijk

Sinds wanneer kan een beetje applicatie logica geen unique constraints afvangen Als jij toch weet wat je PK's zijn en je doet bij het saven van je data iets als

code:

$check = (select 'x' from target_table where a=1 and b=3 and c=6;)
if $check = 'x' then RaiseError else commit;

Waarbij a, b, en c je PK's zijn, dan ben je er toch

---

Even een uitstapje naar de wereld die PeopleSoft heet:

PeopleSoft maakt op DB niveau niet eens tabellen aan met Pri of foreign keys. Wel Unique indexen overigens. Dus theoretisch zou je alleen op basis van de indexen unique constraints kunnen krijgen als je data insert.

Het klinkt misschien vreemd, maar de keys zijn bij PS opgenomen in de applicatielogica. Er wordt een eigen recordschema bijgehouden met de opbouw van de records, hun sleutels en andere eigenschappen (aan het record gehangen code, fields, opties, auditinstellingen, etc). Dus da's toch echt de applicatie die dat afhandelt. En het wordt nog mooier: de applicatielogica is voor 90% opgeslagen in records in de DB. geldt ook voor alle (meestal dynamisch gegenereerde) webpagina's. Uiteraard een hoop caching op diverse niveau's, maar voldoende roundtrips naar de DB server om een conducteur flau van te laten vallen
En nee, dat geeft GEEN performance problemen

Da's ook 1 van de sterke kanten van een goede ERP suite: daar kan veel meer mee dan mensen denken. Ik geef eerlijk toe: de core is niet iets wat je in een half jaartje ontwikkelt. In de huidige technologie zit denk ik 20 jaar of meer onderzoek en ontwikkeling. Maar dan heb je ook wat.

---

Wel kan ik me voorstellen dat zoiets op iemand die nooit een dergelijke suite onder de kap heeft gezien, wat vreemd overkomt. begrijpen ook overigens; immers bij een normaal development traject zal nooit gekozen worden om een compleet development framework van scratch te bouwen - da's simpelweg te duur. Dus zie je idd zoals de TS ervaart, het maken van oplossingen die of op webserver niveau, of op DB niveau opgelost worden. Maar als je bijvoorbeeld naar een suite gaat kijken die in Java gebouwd is en op een applicatieserver draait, dan zie je 9 van de 10 kansen dat daar de validatielogica zit, en niet in de DB

Gebruik een DBMS als een DBMS en laat deze voor jou het werk doen.

Zowel applicaties als DBMS-en hebben hun sterke en zwakke kanten, zorg er voor dat je deze kent.

Coulden't agree more
We hebben zo te merken alleen een ander beeld van een DBMS en het kunnen daarvan

[JaQ]

The Eagle schreef op vrijdag 09 december 2011 @ 14:38:
Waarom wil je validaties eigenlijk met constraints afdwingen? Dat soort dingen hoort in je applicatielogica te zitten, niet in je DB namelijk

Omdat een fat-database (zoals het tegenwoordig heel populair genoemd wordt) er voor zorgt dat je front end een YAFET wordt (yet another frond end technology). Toon Koppelaars heeft daar een heel verhaal over geschreven dat jou als Oracle persoon aan zou kunnen spreken

(ik geef bewust geen oordeel of dat "juist" is of niet, komt omdat ik voor zowel een fat database als een db als domme storage goede argumenten ken. Typisch gevalletje van "it depends").

cariolive23 schreef op vrijdag 09 december 2011 @ 22:49:
Ik zou de datum 'infinity' gebruiken, oneindig. Dat werkt prima, ook met een unique constraint.

Alleen jammer dat de optimizer dan helemaal in de war raakt omdat de "low value" en "high value" van de waarden in de kolom op geen enkele wijze representatief zijn. Dummy waarden als 01-01-1900 (of 31-12-9999) zijn eigenlijk altijd fout en niet de manier om op te gaan met iets wat je eigenlijk null wilt laten zijn.

The Eagle schreef op zaterdag 10 december 2011 @ 18:05:
Serieus: noem mij 1 validatie die alleen op DB niveau uitgevoerd kan worden. Ik ben oprecht benieuwd namelijk

Gaat niet om kunnen, gaat om willen (de database doet die unique constraint controle iets sneller dan dat jij dat uit je app doet. Bovendien zal bij een volgende GUI op dezelfde database misschien de unique constraint vergeten worden, iets wat niet op kan treden als je de controle op het laagst mogelijke niveau vastlegt).

@TS:
Dit kan je niet handig afdichten met een unique of foreign key constraint. Ik weet bijzonder weinig van PostgreSQL, maar Oracle kent zoiets als een check constraint. Performance impact is potentieel hoog, maar dat gaat pas meespelen bij veel data die frequent wijzigt.

[ Voor 13% gewijzigd door JaQ op 11-12-2011 12:02 ]

[joppybt]

The Eagle schreef op zaterdag 10 december 2011 @ 18:05:
Sinds wanneer kan een beetje applicatie logica geen unique constraints afvangen Als jij toch weet wat je PK's zijn en je doet bij het saven van je data iets als

code:

$check = (select 'x' from target_table where a=1 and b=3 and c=6;)
if $check = 'x' then RaiseError else commit;

Waarbij a, b, en c je PK's zijn, dan ben je er toch

Neen, dit werkt niet. Er is altijd een kleine kans dat een ander proces net tussen jouw regel 1 en 2 inkomt en eenzelfde record insert. Dan is jou check al geweest en heb je dus niet door dat het andere process iets veranderd heeft.
Een (database) unique constraint is de enige manier om dit 100% zeker af te dekken.

[cariolive23]

JaQ schreef op zondag 11 december 2011 @ 12:00:
[...]

Omdat een fat-database (zoals het tegenwoordig heel populair genoemd wordt) er voor zorgt dat je front end een YAFET wordt (yet another frond end technology).

En daarmee krijg je dus verschillende implementaties van de applicatielogica (en dus data validaties) op verschillende plaatsen. Eén keer deze regels implementeren in de database, is dan wel zo eenvoudig, het voorkomt keer op keer terugkerende bugs, het bekende whack-a-mole.

(ik geef bewust geen oordeel of dat "juist" is of niet, komt omdat ik voor zowel een fat database als een db als domme storage goede argumenten ken. Typisch gevalletje van "it depends").

+1 En ik vermoed dat de "it depends" vooral afhangt van de beschikbare kennis en kunde.

Alleen jammer dat de optimizer dan helemaal in de war raakt omdat de "low value" en "high value" van de waarden in de kolom op geen enkele wijze representatief zijn.

Ik heb er nog nooit problemen mee gehad, gebruik het zelden, het zou best kunnen. Dit resultaten van EXPLAIN zullen dit ook laten zien wanneer je hier een performance probleem hebt.

Ik weet bijzonder weinig van PostgreSQL, maar Oracle kent zoiets als een check constraint.

PostgreSQL kent ook check constraints, werkt prima.

Ps. Een DBMS is een applicatie op zich, het is tenslotte software, daar kun je dus best logica in zetten.

[Foeijonghaai]

cariolive23 schreef op vrijdag 09 december 2011 @ 22:49:
[...]

Ik zou de datum 'infinity' gebruiken, oneindig. Dat werkt prima, ook met een unique constraint.

Ik heb dit zojuist geprobeerd.

Werkt inderdaad goed met de unique constraint en ook met sorteren werkt het zoals verwacht.

[Foeijonghaai]

JaQ schreef op zondag 11 december 2011 @ 12:00:
[...]
Omdat een fat-database (zoals het tegenwoordig heel populair genoemd wordt) er voor zorgt dat je front end een YAFET wordt (yet another frond end technology). Toon Koppelaars heeft daar een heel verhaal over geschreven dat jou als Oracle persoon aan zou kunnen spreken

Ik heb zojuist wat artikelen van hem gelezen en ik vind zijn ideeen erg interessant. Die ideeen sluiten ook aan bij mijn gevoel om het 'zo dicht mogelijk bij de data' op te lossen en het in een keer op te lossen, wat voor frontend er ook gebruikt wordt.

@TS:
Dit kan je niet handig afdichten met een unique of foreign key constraint. Ik weet bijzonder weinig van PostgreSQL, maar Oracle kent zoiets als een check constraint. Performance impact is potentieel hoog, maar dat gaat pas meespelen bij veel data die frequent wijzigt.

Ik ken de check-constraint. Alleen is deze check dusdanig complex dat ik denk dat ik er niet met 1 query vanaf kom. Ik heb in de Postgres-handleiding nog geen voorbeelden gezien van een check-constraint in combinatie een stored procedure, maar dat het er niet staat wil niet zeggen dat het niet kan. Ik ga dat eens proberen.

[JaQ]

Foeijonghaai schreef op maandag 12 december 2011 @ 11:20:
[...]

Ik heb zojuist wat artikelen van hem gelezen en ik vind zijn ideeen erg interessant. Die ideeen sluiten ook aan bij mijn gevoel om het 'zo dicht mogelijk bij de data' op te lossen en het in een keer op te lossen, wat voor frontend er ook gebruikt wordt.

Toon heeft ook een alleraardigst boek geschreven

Ik ken de check-constraint. Alleen is deze check dusdanig complex dat ik denk dat ik er niet met 1 query vanaf kom. Ik heb in de Postgres-handleiding nog geen voorbeelden gezien van een check-constraint in combinatie een stored procedure, maar dat het er niet staat wil niet zeggen dat het niet kan. Ik ga dat eens proberen.

Het werkt in Oracle prima en aangezien pgsql alles van Oracle probeert na te doen...

[Foeijonghaai]

Ik heb dit

code:

(select
  count(*)
from
  simaccounthistory h1,
  simaccounthistory h2
where
  (h1.simimsi = h2.simimsi or h1.accountnumber = h2.accountnumber) and
  (h1.start, h1.stop) overlaps (h2.start, h2.stop) and
  h1.start != h2.start and
  h1.stop != h2.stop) = 0

als check constraint geprobeerd, maar Postgres laat geen subqueries toe in de check-constraint.

Ik heb het daarna geprobeerd met een stored procedure:

code:

create or replace function
    justcheck()
returns
    boolean
as $$ 
    begin    

    if (
        select
      count(*)
    from
      simaccounthistory h1,
      simaccounthistory h2
    where
      (h1.simimsi = h2.simimsi or h1.accountnumber = h2.accountnumber) and
      (h1.start, h1.stop) overlaps (h2.start, h2.stop) and
      h1.start != h2.start and
      h1.stop != h2.stop) = 0 then
      return TRUE;
    end if;

        return FALSE;
    end;
$$ language 'plpgsql';

in combinatie met een check-constraint

code:

CONSTRAINT chk_justcheck CHECK (justcheck())

Dit werkt wel mooier dan met de trigger en de stored procedure:

• overlaps is is korter dan mijn eigen 'overlaps' implementatie
• heb nu 1 controle voor zowel overlaps op sim en account ipv 2 controles, een voor sim en een voor account
• hoef geen onderscheid te maken tussen insert en update

Helaas kan ik nog steeds invalide data inserten. Als ik die vervolgens probeer te wijzigen werkt de check wel en kan ik de invalide data niet meer ombouwen tot valide data Vermoedelijk is dit omdat de check geen weet heeft van de nieuwe data. Ik heb de handleiding van Postgres lopen uitpluizen of het toch mogelijk is om de nieuwe data te bekijken voordat deze de DB ingaat en ik denk dat er niets anders opzit dan toch een trigger te gebruiken, omdat dat echt ingrijpt vlak voor de insert en weet heeft van de nieuwe data.

Verder is de vraag in hoeverre 'overlaps' portable is naar andere databases, maar dat is momenteel van ondergeschikt belang.

[Foeijonghaai]

JaQ schreef op maandag 12 december 2011 @ 22:31:
Toon heeft ook een alleraardigst boek geschreven

Thx. Wij moesten tijdens de opleiding (UT) ook eerst de queries uitschrijven met verzamelingenleer en daarna pas omzetten naar SQL. Daar lijkt zijn boek ook over te gaan.

[cariolive23]

Foeijonghaai schreef op dinsdag 13 december 2011 @ 12:01:
Helaas kan ik nog steeds invalide data inserten.

Dat is niet zo gek, de query valideert alleen reeds aanwezige data in de tabel, niet de nieuwe data. Jouw functie accepteert zelfs geen parameters (de nieuwe data) als input, kan dus onmogelijk een correcte validatie uitvoeren.

Met een update heb je hetzelfde probleem, ook dan wordt alleen bestaande data gevalideerd en niet de nieuwe data.


Misschien dat een exclusion constraint iets voor jou is? Zie http://thoughts.j-davis.c...e-generalized-sql-unique/

[JaQ]

cariolive23 schreef op dinsdag 13 december 2011 @ 18:32:
Dat is niet zo gek, de query valideert alleen reeds aanwezige data in de tabel, niet de nieuwe data. Jouw functie accepteert zelfs geen parameters (de nieuwe data) als input, kan dus onmogelijk een correcte validatie uitvoeren.

Met een update heb je hetzelfde probleem, ook dan wordt alleen bestaande data gevalideerd en niet de nieuwe data.

Weet je wel zeker dat je hier juiste informatie verschaft? De check constraint zorgt er toch juist voor dat je je transactie niet kan voltooien?

cariolive23 schreef op dinsdag 13 december 2011 @ 18:32:
Misschien dat een exclusion constraint iets voor jou is? Zie http://thoughts.j-davis.c...e-generalized-sql-unique/

Nergens voor nodig.

De query in de stored procedure voldoet volgens mij niet aan de vraag van de TS. Zo eventjes spelend kom ik tot de conclusie dat

sim maar 1 keer uitgedeeld in een periode, uitkomst moet 0 zijn

SQL:

select count(*)
  from simaccounthistory h1, simaccounthistory h2
 where h1.simimso = h2.simimso
   and (h1.start, nvl(h1.stop,infinity)) overlaps (h2.start, nvl(h2.stop,infinity))

en 1 account maar 1 sim in een periode, uitkomst moet 0 of 1 zijn

SQL:

select count(simimso)
  from simaccounthistory h1, simaccounthistory h2
 where h1.accountnumber = h2.accountnumber
   and (h1.start, nvl(h1.stop,infinity)) overlaps (h2.start, nvl(h2.stop,infinity))

Niet echt samen gaat in 1 query.

[cariolive23]

JaQ schreef op dinsdag 13 december 2011 @ 22:05:
[...]

Weet je wel zeker dat je hier juiste informatie verschaft? De check constraint zorgt er toch juist voor dat je je transactie niet kan voltooien?

Een constraint moet data valideren alleen wordt hier een stored procedure aangeroepen die alleen de reeds aanwezige data gaat valideren. En dus niet de nieuwe data, die kun je dus zonder problemen opslaan.

De query in de stored procedure voldoet volgens mij niet aan de vraag van de TS.

Ik weet het wel zeker, er wordt namelijk helemaal niets gedaan met de in te voeren data, alleen oude data wordt bekeken met genoemde query.

Count is in zijn geheel overbodig, je wilt weten of er één of meerdere records aan de voorwaarde voldoen en niet meer dan dat.

In het kort:

SQL:

PERFORM 1 FROM tabel WHERE x = parameter; -- resultaat is onbelangrijk
IF FOUND THEN -- FOUND wordt TRUE wanneer er resultaten zijn gevonden
  RETURN FALSE; -- blijkbaar is er overlap, foutmelding!
ELSE
  RETURN TRUE; -- niks aan de hand, data kan worden opgeslagen
END IF;

[ Voor 3% gewijzigd door cariolive23 op 14-12-2011 08:55 . Reden: flatertje herstelt, meerdere dingen tegelijk doen, werkt niet. ]

[JaQ]

cariolive23 schreef op dinsdag 13 december 2011 @ 23:58:
Een constraint moet data valideren alleen wordt hier een stored procedure aangeroepen die alleen de reeds aanwezige data gaat valideren. En dus niet de nieuwe data, die kun je dus zonder problemen opslaan.

Je hebt gelijk. Ik ging er even vanuit dat de constraint afging nadat de data was toegevoegd (en in dezelfde transactie die query uitvoerde, dus dan zou de procedure wel false retourneren).

[Killemov]

Alleen de geschiedenis tussen twee tabellen vastleggen kan met Postgres (en vele andere ...) volledig in de database zelf. Het uitgangspunt is dan dat je een trigger kunt zetten op de update/insert/delete van de FK in BEIDE tabellen. ( Dus account.sim -> sim.simid EN sim.account -> account.accountid. ) En dan vervolgens in een stored procedure (pgSQL) de history records checken, maken of bijwerken. Je hoeft je dan in de applicatie alleen nog maar bezig te houden met sim en account. Ik heb zelf ooit eens een variant gebruikt waarin de geschiedenis van de relaties tussen diverse tabellen in een enkele historylogtabel werd bijgehouden.

Belangrijk: Let goed op de constraints van de tussentabel naar beide hoofdtabellen! Mogelijk wil je die niet eens hebben omdat je dan problemen zou kunnen krijgen met het verwijderen van sims of accounts.