Tidshåndtering og sporing - del 2 gyldighetsperiode

Tidshåndtering og sporing - del 2 gyldighetsperiode

I offentlig forvaltning stilles det krav til sporing av data, dette for å ivareta personvernet og for å kunne etterprøve saksbehandlingen. Så hvordan håndtere flere versjoner samtidig av samme informasjonsobjekt, men med forskjellig gyldighetsperiode? 

I del 1 (bruksperiode) av denne serien tok vi for oss hvordan man kan etablere sporing av endringer ved hjelp av tabellversjonering med hensyn på bruksperiode. Dette løste problemet med å spore endringer og når informasjon har vært tilgjengelig for bruk. I denne artikkelen adresseres utfordringen med å håndtere flere samtidige versjoner av samme informasjonsobjekt, men med forskjellig gyldighetsperiode. Et eksempel på informasjonsobjekt som kan ha forskjellig gyldighetsperiode er grunnbeløpet (G) som vanligvis justeres en gang i året. Det er helt nødvendig å håndtere dette på en konsistent måte for å kunne gjøre korrekt beregning av støttebeløp gjennom å dokumentere når ting faktisk er gyldige i virkeligheten.

Ved å innføre en meta-attributt som angir perioden for når data er gyldige og kombinere dette med tabellversjonering så kan man enkelt svare på spørsmålet når eller hvor lenge har disse dataene vært gyldige. En bieffekt av å innføre av gyldighetsperiode er at vi også kan legge inn informasjon som skal gjelde fram i tid. Denne artikkelen belyser hva som må til for å etablere sporing ved hjelp av konsistent tidshåndtering og tabellversjonering. Dette er eksemplifisert og det blir også vist hvordan man kan bruke databaseteknologi fra IBM/Oracle for å realisere dette. Artikkelen er en del av en serie.

Tabellversjonering med hensyn på gyldighetsperiode

Som i del 1 bruker vi tabellversjonering for å håndtere endringer. For hver gang informasjon i en en rad blir oppdatert så må man ta vare på en kopi av den gamle raden og lage en ny rad med ikkeoverlappende tidsperiode. Men til forskjell fra bruksperiode så anvendes ikke historietabell. I stedet lagres versjonerte rader i den aktive tabellen og man legger til et metadata-attributt for å angi når dataene er gyldige (gyldighetsperiode). Ved å analysere gyldighetsperioden kan man så avgjøre om raden er gyldig for et gitt tidspunkt eller ikke. Det er opp til applikasjonen/bruker å skrive spørringer som utnytter dette og det kompliserer oppdatering og bruk av versjonerte data noe.

Gyldighetsperiode er relatert til databaseteoriens "valid time" og angir når dataene er gyldige i virkeligheten denne informasjonen håndteres av applikasjonen. Transaction time angir når dataene er i bruk (se del I).

Eksempel  - gyldighetsperiode

Følgende eksempel illustrere tabellversjonering med hensyn på gyldighetsperiode. Vi har en tabell som holder styr på en persons adresse og husleie og så gjennomfører vi følgende transaksjoner:

  1. Per blir registrert første gang med adresse Oslo og husleie 9300 av bruker B1, med gyldighet fra 2000-01-01 og fremover.
  2. Pers husleie blir oppdatert til 20000 av bruker B2, dette har virkning fra 2008-08-01.
  3. Pers adresse og husleie blir oppdatert til London, 15000 av bruker B3, flyttingen skjer 2010-04-21.
  4. Pers adresse og husleie endres til Paris, 12000 av bruker B4, flyttingen skjer den 2013-11-05.

Vi utvidere eksemplet fra del 1 med innføre en gyldighetsperiode.

Transaksjon 1: Utføres 2009-01-01 - Per blir registrert første gang med adresse Oslo og husleie 9300 av bruker B1

Person

Adresse

Husleie

gyldighetsperiode

registert_av

bruksperiode 

Per

Oslo

9300

2000-01-01 – 9999-12-31

B1

2009-01-01 – 9999-12-31

Gyldighetsperioden angir når dataene riktige. Her bruker vi 9999-12-31 til å angi at så vidt vi vet så er informasjonen lagret i raden gyldig i all overskuelig fremtid.

Transaksjon 2: Utføres 2013-02-03 - Pers husleie blir oppdatert til 20000 av bruker B2, dette har virkning fra 2008-08-01.

Her dokumentere Per at han har flyttet fra og med 2008-08-01. For å håndtere dette lager vi en ny rad som er gyldig fra og med 2008-08-01 og lukker gyldighetsperioden på den gamle raden tilsvarende. Legg merke til at dette først blir registrert i databasen den 2013-02-03.

Person

Adresse

Husleie

gyldighetsperiode

registert_av

bruksperiode 

Per

Oslo

9300

2000-01-01 – 2008-08-01

B1

2013-02-03 – 9999-12-31

Per

Oslo

20000

2008-08-01 – 9999-12-31

B2

2013-02-03 – 9999-12-31


Transaksjon 3: Utføres 2013-12-05 - Pers adresse og husleie blir oppdatert til London, 15000 av bruker B3, flyttingen skjer 2010-04-21

Som i transaksjon 2 så lukker vi den raden som ikke lengre er gyldig og legger til en ny med riktig startdato.

Person

Adresse

Husleie

gyldighetsperiode

registert_av

bruksperiode 

Per

Oslo

9300

2000-01-01 – 2008-08-01

B1

2013-02-03 – 9999-12-31

Per

Oslo

20000

2008-08-01 – 2010-04-21

B2

2013-12-05 – 9999-12-31

Per London 15000 2010-04-21 – 9999-12-31 B3 2013-12-05 – 9999-12-31


Transaksjon 4: Utføres 2014-02-20 - Pers adresse og husleie endres til Paris, 12000 av bruker B4, flyttingen skjer den 2013-11-05.

Som i transaksjon 3 så lukker vi den raden som ikke lengre er gyldig og legger til en ny med riktig startdato.

Person

Adresse

Husleie

gyldighetsperiode

registert_av

bruksperiode 

Per

Oslo

9300

2000-01-01 – 2008-08-01

B1

2013-02-03 – 9999-12-31

Per

Oslo

20000

2008-08-01 – 2010-04-21

B2

2013-12-05 – 9999-12-31

Per London 15000 2010-04-21 – 2013-11-05 B3 2014-02-20 – 9999-12-31
Per Paris 12000 2013-11-05 – 9999-12-31 B4 2014-02-20 – 9999-12-31

Ved å følge de enkle fremgangsmåten ved å lukke gyldighetsperioden på de gamle radene etterhvert som ny informasjon legges til kan vi spore hva som har vært gyldig. Vi kan også kombinere dette med bruksperiode og se hva som faktisk var registrert i databasen på et gitt tidspunkt.  Vi kan også lett finne den siste raden som er gyldig, det er den med bruk_til lik 9999-12-31. 

Illustrert med følgende figur:

Historietabell

Her viser vi historietabellen for eksemplet over. Den inneholder de gamle radene. Informasjon i gyldighetsperioden blir tatt vare på lik linje med de andre attributtene, dermed har vi oversikt over hva vi trodde var gyldig på brukstidspunktet.

Historietabell

Person

Adresse

Husleie

gyldighetsperiode

registert_av

bruksperiode 

Per

Oslo

9300

2000-01-01 – 9999-12-31

B1

2009-01-01 – 2013-02-03

Per

Oslo

20000

2008-08-01 – 9999-12-31

B2

2013-02-03 – 2013-12-05

Per London 15000 2010-04-21 – 9999-12-31 B3 2013-12-05 – 2014-02-20

 

Databaseeksempel

Her angis hvordan man kan etablere tabellversjonering i IBMs DB2 og i Oracles 12c. Videre vises hvordan man kan angi gyldighetsperiode og spørringer som utnytter den informasjonen.

Den store forskjellen mellom de to er at Oracle bruker NULL-verdi i periodene for å angi uendelig mens IBM bruker en definert maksverdi. Oracle tillater nå egne navn for gyldighetsperiode men kan også bruke egne metadata-attributter. DB2 krever at man må sette opp tidsperiode og  på en spesiell måte og deklarere dette i primary key.

DB2 version 10+

CREATE TABLE adresse(
navn VARCHAR(10) NOT NULL,
adresse VARCHAR(15) NOT NULL,
husleie REAL NOT NULL,
gyldig_fra DATE NOT NULL,
gyldig_til DATE NOT NULL,
registrert_av VARCHAR(10) NOT NULL,
bruk_fra TIMESTAMP(6) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
bruk_til TIMESTAMP(6) NOT NULL GENERATED ALWAYS AS ROW END,
ts_id TIMESTAMP(6) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
PERIOD system_time (bruk_fra, bruk_til),
PERIOD business_time (gyldig_fra, gyldig_til),
PRIMARY KEY(navn, business_time WITHOUT OVERLAPS));

Legg merke til primærnøkkel som angir at navn og business_time ikke skal overlappe. Dette sørger for at databasen nekter oss å legge inn gyldighetsperioder som ikke er konsistente. 

INSERT INTO adresse(navn, adresse, husleie, gyldig_fra, gyldig_til, registrert_av)
VALUES ('Per', 'Oslo', '9300', '2000-01-01', '9999-12-31', 'B1');
UPDATE adresse FOR PORTION OF business_time FROM '2008-08-01' TO '9999-12-31‘
SET husleie='20000', registrert_av='B2'
WHERE navn='Per' and adresse = 'Oslo';
DELETE FROM adresse
FOR PORTION OF business_time FROM '2002-01-01' TO '2005-12-31‘
WHERE navn = 'Per';

DB2 har operasjoner for å oppdatere eller slette data versjonert med hensyn på gydlighet. 

-- hvordan var gyldig på et gitt tidspunkt
SELECT * FROM adresse FOR business_time AS OF '2013-06-01';
 
-- endre gyldighetstid for eksisterende spørringer
SET CURRENT TEMPORAL business_time = '2013-06-01';

Oracle 12c

CREATE TABLE adresse(
navn VARCHAR(10) NOT NULL,
adresse VARCHAR(15) NOT NULL,
husleie REAL NOT NULL,
gyldig_fra DATE NOT NULL,
gyldig_til DATE NOT NULL,
registrert_av VARCHAR(10) NOT NULL,
PERIOD FOR gyldighet(gyldig_fra, gyldig_til));
ALTER TABLE adresse FLASHBACK ARCHIVE;

Her sørger Oracle flashback archive mekanismen for håndtering av gyldighet og tillater oss å lage egne datafelter for metadata-attributter for å håntere gyldighet.

Manipulere data

Oracle har ingen DML operasjoner for å oppdatere eller slette data med hensyn på gyldighet slik som IBM har.

-- endre temporal tid til å returnere rader som er gylidige på et gitt tidspunkt
EXECUTE DBMS_FLASHBACK.ENABLE_AT_VALID_TIME('ASOF', TIMESTAMP '2013-06-01 12:00:00');
-- endre temporal tid til returnere de som er gyldige nå
EXECUTE DBMS_FLASHBACK.ENABLE_AT_VALID_TIME('CURRENT');
-- endre temporal tid til å returnere alle (også de som ikke er gyldige lengre)
EXECUTE DBMS_FLASHBACK.ENABLE_AT_VALID_TIME('ALL');

Relasjoner mellom tabeller som er versjonert med hensyn på gyldighet

Fremmednøkler kan ikke uten videre brukes alene da man bør ta hensyn til radenes gyldighetsperioder. Dermed må man definere hvilke skranker som skal opprettholdes. For eksempel skal raden som bruker en fremmednøkkel ha samme gyldighetsperiode som raden den refererer til, eller holder det med at den er innenfor perioden? Kan det tenkes at det holder at periodene overlapper. I noen tilfeller kan det tenkes at perioden skal være starte innenfor eller etter. Disse skrankene må defineres og kan realiseres ved hjelp av database triggere. Men det er applikasjonen som må sørge for at man overholder disse skrankene.
 
La oss utvide eksemplet vårt med en ny bostøttetabell og la den inneholde navn, støttebeløp, gyldighetsperiode og registrert_av. La oss anta at formelen for å motta bostøtte i Norge er (husleie - egenandel) x 70% så lenge man bor i Norge.  Gitt at egenandelen har stått fast på 3000 kroner så vil vi kunne angi dette med følgende tabell. Her må vi sørge for at gyldighetsperiodene korresponderer de i adressetabellen. Da kan vi lage følgende rader for Per:
 
NavnStøttebeløpGyldighetRegistrert_av
Per 4410 2000-01-01 – 2008-08-01 S1
Per 11900 2008-08-01 – 2010-04-21 S2
Per 0 2010-04-21 – 9999-12-31 S3

Oppsummering tabellversjonering mhp gyldighetsperiode

I dag er det enkelt å realisere sporing i databaser. De største databaseleverandørene har støtte for tabellversjonering. I denne artikkelen har vi sett på tabellversjonering med hensyn på gyldighetsperiode. Ved oppdatering av databasen lagres nye rader med endrede data, men med ikke-overlappende gyldighetsperiode fra de gamle radene. Aktiv tabell har FLERE versjoner av raden med ikke overlappende gyldighetsperiode og vi tar vare på når og hvor lenge raden var gyldig

Tabellversjonering med hensyn på gyldigehtsperiode er ikke like enkelt å få til som bruksperiode. For å håndtere dette må applikasjonen støtte og skjønne gyldighetsperioder og sørge for å oppdatere gyldighetsperioden på radene etterhvert som de endres. Det databasen har støtte for er å kunne hente ut gyldige raderer ut fra et tidspunkt. Dvs. man slipper å skrive om spørringer, men kan ved å sette et ønsket tidspunkt eller periode få ut data som er gyldig da. Databasene vil automatisk velge ut de radene som er gyldig på det tidspunktet man spør.

Referanser

Skogan, David

Konsulent
david.skogan@acando.no
comments powered by Disqus