Temporal db kapcsolt sorok

Temporal db kapcsolt sorok
2014-11-28T11:37:15+01:00
2014-12-01T12:17:31+01:00
2022-12-01T21:55:44+01:00
sishkebab
Üdv!

Olyan adatbázist tervezek, amiben szükség van egyes adatok időbeli változásának nyilvántartására. Ezért ezekhez az adatokhoz hozzáadtam valid-time jellegű időintervallumot és az elsődleges kulcsot is ennek megfelelően módosítottam. pl.:

ITEM(item_no, descr, {...}, vt_from, vt_to)

Ebben az esetben, ha időben követni akarom ITEM-ben mondjuk a descr attribútum változásait, akkor annyi {item_no, vt_from} párom lesz, ahány módosítást végrehajtottam, ez így helyes, ez volt a cél.

Nagyjából eddig terjed a neten minden információ, amit fel lehet lelni hasonló problémákkal kapcsolatban.
A valóságban azonban nem 1-1 táblával dolgozik az ember fia, így amikor ehhez hozzáadok kapcsolódó táblákat, amikben szintén követni kellene az adat érvényességét időben az egész igen problémássá válik:

SUBTBL1(pkey, ref_item_no, ref_item_to, vt_from, vt_to)

A probléma, hogy ITEM-ben vt_from része az elsődleges kulcsnak, ezért minden változtatás ITEM egy során azt vonja magával, hogy a kapcsolódó SUBTBL1 sorokat újra létre kell hozni minden {item_no, vt_from} párra.
Ha vt_from nem része ITEM elsődleges kulcsának, akkor nem működik az egész.
Ha a kapcsolódó táblában csak item_no-ra hivatkozok, akkor nincs integritás ellenőrzés.

Milyen megoldás létezik erre?
Mutasd a teljes hozzászólást!
Nekem csak egyszer kellett ilyenbe belefolynom, akkor is tudtam "csalni", ugyanis Java project volt és lehetett Hibernate-et használni, aminek van egy Envers nevű audit modulja. Amit leírtál, az gyanúsan hasonlít a két audit stratégiájuk közül a másodikra, a "validity" stratégiára. Mivel ő is módosítja a valid_to oszlopot bizonyos esetekben, az nem lehet a kulcs része. Vagy be kell vezetned egy szintetikus kulcsot, vagy lemondanod a kulcsok használatáról. (Nem tudom, a UNIQUE constraint megengedi-e az oszlopainak a módosítását. Ha megengedi, akkor elsődleges kulcs helyett legalább használhatsz ilyen megszorítást a duplikátumok kivédésére.)

A másik megoldásukat is érdemes átgondolnod. Itt a meglevő sorokat nem módosítja utólag, helyette változási eseményeket (létrehozás, módosítás, törlés) tárol a táblában. Azt, hogy egy adott időpontban mi volt az állás, egy bonyolult lekérdezés adja meg, ami minden illeszkedő sornak a legkésőbbi állapotát adja vissza, kivéve azokat, amiknek a legfrissebb eseménye a törlés. Tehát itt a módosítás az egyszerűbb (nem kell "invalidálni" sorokat), cserébe a lekérdezés sokkal bonyolultabb.

Egyébként milyen gyakran kell régi adatokat előkeresni, és milyen gyakran kell a legfrissebbekkel dolgozni? Ha nem elsősorban auditálási célokra készül az adatbázis, valószínű jobban jársz, ha a legfrissebb állapotot azért eltárolod "hagyományos" táblákban is, a jól megszokott kulcsokkal és megszorításokkal. Igaz, hogy redundáns adattárolás, de a sebessége sokkal jobb lesz, mint egy hosszú audit logból rekonstruálni a legfrissebb állapotot minden művelethez. Arra persze figyelned kell, hogy minden módosítás tükröződjön az audit táblákban is.
Mutasd a teljes hozzászólást!

  • create table mutation (tablaid serial, esemenyid integer, descr  valami,keletkezés timestamp, modositva timestamp  , aktualis enum('TRUE','FALSE') NOT NULL DEFAULT 'TRUE');

    Új eseménynél egy sequencerből vagy uuid() ből generálsz egy eseményid-t és beszúrod a táblába (keletkezes ,modositva az aktuális időpont, aktualis =true )

    Ha időben változik az adat akkor nem upadte van hanem insert. A meglévő azonos esemenyidjű  és  aktualis = true rekordokon before insert triggerrel csinálsz egy update-t aktualis = false értékre majd beszúrásra kerül az új rekord azonos esemenyidvel, keletkezéssel, modosítva= aktuális időpont , új descr,  aktualis =true

    más táblákhoz meg az esemenyid + aktualis=true értékek kapcsolják
    Mutasd a teljes hozzászólást!
  • mutation (tablaid, esemenyid, descr, keletkezés, modositva, aktualis)

    Itt mi az elsődleges kulcs?
    Integritás ellenőrzés hogy van megvalósítva ezzel?

    {esemenyid, aktualis} ilyen formában semmilyen kulcs nem lehet, mert:

    1, ha {esemenyid, aktualis} unique key, akkor minden esemenyid-hez max. két sor tartozhat. ez nyilván nem jó.

    2, {esemenyid, aktualis} elsődleges kulcs nem lehet, mert "...meglévő, azonos esemenyidjű..." + (1)

    Amennyire én tudom foreign key csak másik table elsődleges kulcsa vagy unique indexe lehet.

    Mutasd a teljes hozzászólást!
  • A baj az, hogy nem csak current update van.
    A jó az, hogy a transaction time annyira nem érdekes.
    Utólagos feldolgozáshoz, ami rendszeres, mindenféleképpen fontos, hogy a korábbi adatok az időben megfelelő párjaikkal szerepeljenek. Az én esetemben legtöbbször a transaction time egyenlő a valid time-al. Nyilván a táblák ebben a verzióban beszúrásos módon fognak működni.

    Kis pihenés után két gondolat van a fejemben:
    - szétszedni a táblákat az időben változó és nem változó tulajdonságok mentén. Így az időben nem változó részeket össze lehet kötni egymással és megmarad az integritás.
    - dobni az egészet és egyszerűen kapcsolódó history táblákat létrehozni típusonként. A nyitó példával élve:
    ITEM(item_no, descr, {...})
    SUBTBL1(pkey, ref_item_no, subtbl1_date)
    SUBTBL1_ITEM(ref_item_no, subtbl1_date, descr, {...})
    ennek ugye az a "hátránya", hogy csak az van benne, ami legalább egyszer már használva volt.

    Amiket írtál azoknak meg utánaolvasgatok, köszönöm!
    Mutasd a teljes hozzászólást!
  • Ha az aktual mezőnél be van kapcsolva hogy lehet null akkor úgy módosul amit leírtam hogy false helyett null értéket használunk mert ilyen esetben simán lehet unique kulcs az eseményid + aktual mezőpároson.De anélkül is lehet triggerekkel  a foreign key működését szimulálni
    Mutasd a teljes hozzászólást!
  • Jó, de ennek mi értelme?
    Ugyanezt az enyémmel is meg lehet csinálni 2 felesleges mező bevezetése nélkül, utólagos assert/check/trigger hackekkel integritásellenőrzést szimulálva.

    Az lenne a lényeg, hogy a lehető legkevesebb hack kelljen olyan funkciók vélgrehajtásához, amiket a db amúgy támogat.
    Mutasd a teljes hozzászólást!
  • Végül azt a megoldást választottam, hogy marad az eredeti tábla érvényesség nélkül, ezen van normal insert, update, delete. Megmarag az egyszerű integritás ellenőrzés és elsődleges kulcs.

    ITEM(item_no, descr, {...})

    Emellé készül két másik tábla:

    Ez a current elemeket tartalmazza:

    ITEM_CURRENT(item_no, vt_from)

    Ez pedig a log:
    ITEM_OLD(item_no, descr, {...}, vt_from, vt_to)

    Az utóbbi két tábla ITEM triggereivel van karbantartva.
    A teljes történet az aktuális sorokkal elérhető egy view-on keresztül. A sémát használó program szempontjából többnyire transzparens a cucc.

    Akit érdekel a téma részletesen, annak itt egy link: http://www.cs.arizona.edu/~rts/tdbbook.pdf

    Kösz a segítséget!
    Mutasd a teljes hozzászólást!
Tetszett amit olvastál? Szeretnél a jövőben is értesülni a hasonló érdekességekről?
abcd