Adattáblák kapcsolata historikus lekérdezéssel
2015-01-05T20:28:05+01:00
2015-01-06T17:51:24+01:00
2022-08-09T08:20:39+02:00
TomWhoolp
MS Access 2013; MS SQL 2014 express

Sziasztok!

PCFORUM-on kezdtem, de átirányítottak ide.
Tegyük fel, hogy van egy tábla, mely a munkavállalókat tartalmazza és egy másik tábla mely a munkáltató cégeket.
Ezek egymással össze vannak kapcsolva, a munkavállaló külső kulccsal kapcsolódik a munkáltató tábla elsődleges kulcsához hivatkozás integritással.

Ha az egyik cég átalakul (pl.: kft-ből zrt lesz), de továbbra is ugyanazon emberek dolgoznak immár az új cégnél, akkor hogyan alakítsam ki a táblákat és kapcsolatait, hogy a korábbi állapotok is lekérdezhetők maradjanak?

Megjegyzés:
A MUNKA KFT. és MUNKA ZRT. ugyanazon cégek. Ha új rekordként veszem fel a MUNKA ZRT.-t, akkor minden ehhez tartozó munkavállaló cégének külső kulcsát módosítanom kell az új cégre. Ha átírom a cég nevét, akkor nem marad meg a régi cég neve.

Most tanulgatom az SQL-t, de azért megpróbáltam egy sql queryt is írni.

Köszönöm!
Mutasd a teljes hozzászólást!
Csatolt állomány
create database HIST go ALTER AUTHORIZATION ON DATABASE::HIST TO [sa]; go use HIST go create table Ceg ( Ceg_ID int primary key not null IDENTITY (1, 1), Ceg_NEV varchar(50) not null, Ceg_NEV_CHANGED datetime not null, ) go create table Ceg_NEV_Diff ( Ceg_ID int not null, Ceg_NEV varchar(50) not null, Mettol datetime not null, Meddig datetime ) go CREATE TRIGGER initName ON Ceg AFTER INSERT AS DECLARE @PCeg_ID int,@PMettol datetime, @PCeg_NEV varchar(50) SET @PCeg_ID = (SELECT Ceg_ID FROM inserted) SET @PCeg_NEV = (SELECT Ceg_NEV FROM inserted) SET @PMettol = (SELECT Ceg_NEV_CHANGED FROM inserted) insert into Ceg_NEV_Diff (Ceg_ID,Ceg_NEV,Mettol) values (@PCeg_ID,@PCeg_NEV,@PMettol) GO CREATE TRIGGER updateName ON Ceg AFTER UPDATE AS DECLARE @PCeg_ID int,@PMettol datetime, @PCeg_NEV varchar(50) IF ( UPDATE (Ceg_NEV) and UPDATE (Ceg_NEV_CHANGED)) BEGIN SET @PCeg_ID = (SELECT Ceg_ID FROM inserted) SET @PCeg_NEV = (SELECT Ceg_NEV FROM inserted) SET @PMettol = (SELECT Ceg_NEV_CHANGED FROM inserted) update Ceg_NEV_Diff set Meddig=@PMettol where (Ceg_ID=@PCeg_ID) and (Meddig is null) insert into Ceg_NEV_Diff (Ceg_ID,Ceg_NEV,Mettol) values (@PCeg_ID,@PCeg_NEV,GetDate()) END GO create table Dolgozo ( Ceg_ID int foreign key references Ceg(Ceg_ID), Dolgozo_ID int primary key not null IDENTITY (1, 1), Dolgozo_NEV varchar(50) not null, Dolgozo_BELEPETT datetime not null, Dolgozo_KILEPETT datetime ) go insert into Ceg (Ceg_NEV, Ceg_NEV_CHANGED) values ('Munka Kft.', '1990.01.01') update Ceg set Ceg_NEV='Munka Zrt.',Ceg_NEV_CHANGED='2011.01.01' where Ceg_ID=1 insert into Ceg (Ceg_NEV, Ceg_NEV_CHANGED) values ('Masikmunka Bt.', '2000.01.01') insert into Dolgozo (Ceg_ID, Dolgozo_NEV, Dolgozo_BELEPETT) values ('1','Gipsz Jakab', '2006.01.01') go
Mutasd a teljes hozzászólást!

  • cegid,   egyéb adatok a főtáblában , egy kapcsolótáblában meg cegid , cegnev, mettől, meddig
    Mutasd a teljes hozzászólást!
  • Nem jó, mert itt nem a kapcsolat, hanem a törzs adat változik.

    @kérdező: A historikus adatok tárolása mindig érdekes probléma. A sztenderd válasz az, hogy a historikus táblába be kell vezetned 2 új mezőt: érvényes tól és ig és ezek a mezők az elsődleges kulcs részét képezik. Tehát jelen esetben a cégek táblába kell ezt a 2 mezőt beszúrni. Viszont így minden lekérdezést írhatsz át, hogy használják ezt a 2 új mezőt.

    A másik megoldás az, hogy létrehozol egy külön táblát a historikus adatoknak, amely tartalmazza ezt a 2 mezőt. A fő tábla viszont csak egy érvényes tól mezőt kap. Változtatásnál az előző adatokat egy trigger lementi a historikus táblába. Ez esetben külön historikus lekérdezéseket lehet készíteni, a jelenlegi adatok lekérdezésére szolgáló sql-eket nem kell módosítani. Viszont nem tudsz előre rögzíteni módosításokat.
    Mutasd a teljes hozzászólást!
  • Tehát arról van szó, hogy Gipsz Jakab munkaviszonyban áll egy céggel, melynek megváltozik a cégformája (Kft-ről Zrt.-re), de attól még ugyanott dolgozik.
    Ha felviszem az új cégformát, azt kétféleképpen tehetem meg:
    1. Átírom a régi cég formáját az újra, de így elveszik a régi cégformája (rekord módosulás)
    2. Ha új rekordként veszem fel akkor külön kulcsot kap, ezért azt a Dolgozo táblában is módosítanom kell, de akkor ugyanígy elveszik a korábbi céggel a kapcsolata.

    Ezt szeretném automatizálni, ha a cég adatai megváltoznak, akkor a korábbi állapot is maradjon meg az új mellett (tól-ig dátumokkal), de a dolgoz lekérdezhető legyen, hogy 2006-tól 2010-ig a Munka Kft.-nél, 2011-től a Munka Zrt.nél dolgozik.
    Mutasd a teljes hozzászólást!
  • A kérdésem nem egy eklatáns példa, de nem is ezt akarom megvalósítani. Most dolgozom ki azt az adatmodellt, ahol ilyen problémák merülhetnek fel, de még végig kell gondolnom a megoldásaidat.
    Mutasd a teljes hozzászólást!
  • Szerintem pont ezt írtam a cégtáblában csak a cégid (primary  key) és olyan adatok vannak amik nem változhatnak így a cég neve sincs benne , és az összes olyan adata a cégnek ami változhat külön külön kapcsolótáblákban van időintervallumokkal így az id és az időpont egyértelműen meghatározza hogy mi volt a cég neve 2008-ban , cég formája , telephelye stb.
    Mutasd a teljes hozzászólást!
  • Akkor ide is, amit oda írtam:

    Kétfelé tudsz ilyenkor elindulni. Az egyszerűbbik megoldásban a munkáltató cégeket nyilvántartó táblát kiegészíted egy olyan mezővel, amiben a saját maga egyedi azonosító mezőjére hivatkozik, természetesen ugyanazzal az adattípussal. Ha változik egy cég neve, felveszed egy új sorba és a régi névre hivatkozó mezőbe beírod az előző névhez tartozó azonosítót. Ez után, ha akarod, a dolgozók táblájában is átírhatod a cég azonosítóját, a lényeg, hogy úgy írd meg az SQL-szkripteket, hogy minden vonatkozó cég bekerüljön az eredményekbe. Ennek a megoldásnak van viszont egy nagy hibája: nem tudod visszakeresni, hogy ki melyik cégnél kezdett dolgozni: a régi nevűnél vagy az új nevűnél és a cégeknél sem látod, mikor mi történt a céggel. Ennek feloldására viszont el kell kezdened hisztérikus historikus adatokkal dolgoznod. Szükséged lesz egy olyan táblára, ahol a kezelt egyedekkel (ami lehet dolgozó is vagy cég) történt eseményeket rögzíted: kivel/mivel, mikor, milyen esemény, esetleg ha van az eseményhez valami referencia adat, akkor azt is rögzítheted. E mellett kell egy másik tábla, ahol egyedi azonosítókat rendelsz a lehetségesen bekövetkező eseményekhez: belépés, kilépés, betegállomány, GYES, a cégforma vagy a cégnév változása, stb. Ez a tábla szerencsére rugalmas, ha újfajta esemény történik, csak felveszel egy új rekordot. Ezzel a megoldással már az időbeli változásokat is visszakeresgélheted, akár egész extrém módokon is (pl. volt-e olyan dolgozó egy adott időszak alatt, aki elment GYES-re az egyik cégnél, majd kilépett és egy másik, adatbázisban levő cégnél folytatta a karrierjét). Mint látod, nem a kulcskezelésben van a megoldás lényege, hanem az adatszerkezetben, a kulcskezelés az csak adatokkal való munkát könnyíti meg.
    Mutasd a teljes hozzászólást!
  • A kapcsoló táblákba való átvitelt meg egy insertre és egy updatera aktiválódó triggerrel tudod automatizálni ha változott az érték akkor beszúrod a megfelelő kapcsolótáblába
    Mutasd a teljes hozzászólást!
  • A historikus adatokkal kapcsolatos megoldásodat nem tudom a példámon keresztül levezetni. A te példád nem csak egy cég változásait (eseményeit) kezeli? Ha egy munkaügyi hivatal alkalmazottja "lennék", akkor több cég külön eseményeit kellene rögzítenem.
    Mutasd a teljes hozzászólást!
  • Ez nem ugyanaz a megoldás, amit szabo.zsolt írt (hozzászólásának első felében)?
    Mutasd a teljes hozzászólást!
  • OMG... van belőlem még egy?...

    (De. Az is én vagyok.)
    Mutasd a teljes hozzászólást!
  • Nem tudnád a megoldásod első felét szemléltetni (SQL-lel, vagy másképp)?
    Ceg tábla
    Kulcsok: Ceg_ID (nem inkrementális); ValidFrom; ValidTo

    Dolgozo tábla
    Külső kulcs?

    Mondtam, hogy kezdő vagyok, így nehezebben tudom értelmezni az elmondottakat, minden válaszotokat próbálok most is éppen Access-ben implementálni.
    Mutasd a teljes hozzászólást!
  • Szerintem nem mert nálam egy-egy mező időbeli változása van kapcsolótáblákban rögzítve amikből a lekérdezés egyszerűen megoldható, míg a szabo.zsolt 2 féle megoldást írt az egyikben egy pointer láncoláshoz hasonló módon tárolja ami sqlben nem igazán hatékony és az elsődleges kulccsal is problémák vannak mert a dolgozó aki eddig az 1-s idjű cégben volt a névváltozás után nem lesz tagja az új cégnek, ha csak ott is nem veszed fel új cegidvel. Nekem nagyon kusza megoldásnak tünik
    Mutasd a teljes hozzászólást!
  • Ez a kérdéskör egy fokkal bonyolultabb.
    kulcsszavak amikre keresgessél: audit table, transaction log, temporal database design, bitemporal tables, ilyesmik.

    Könyvecske a témában itt: http://www.cs.arizona.edu/~rts/tdbbook.pdf

    A lényeg nagyjából: kétféle időintervallumot tudsz megkülönböztetni: valid time és transaction time.
    A valid time annyit tesz, hogy a megadott időintervallumban érvényesek a hozzá kapcsolódó adatok.
    A transaction time annyit tesz, hogy mikor vált ismertté, hogy az adat az, ami és ez meddig volt így.

    Sima foreign keyekkel nem fogod tudni megoldani a problémát, mert hiába rakod hozzá az elsődleges kulcshoz az időintervallumok részét vagy egészét, attól még bőven lehetnek átfedések, ami ugye helytelen.

    Második problémaként jelentkezik, amikor ezeknek az időintervallumoknak az egyik határa nem ismert, amit szintén ki kell valahogyan fejezni mezei date mezőkkel.
    Mutasd a teljes hozzászólást!
  • Szemléltetnéd a megoldásod a csatolt példámon keresztül?
    (Mik a kulcsok és hogyan kapcsolódnak)
    Mutasd a teljes hozzászólást!
  • Köszi az anyagot.

    Sejtettem én, hogy nagy fába vágtam a fejszémet.

    Egyáltalán MS Access-ben az ilyen jellegű problémákat meg lehet oldani, vagy felejtsem el és álljak rá csak SQL-re? (... hogy ne a lehetetlent akarjam véghezvinni)
    Mutasd a teljes hozzászólást!
  • Access felejtős kb. minden téren.

    Az elején még jó, de később problémás lesz, mert a szükséges ellenőrzések jórészéhez valamilyen trigger kell.
    Ha most kezded és van időd, akkor szerintem nézz utána, hogy melyik SQL szerver mit támogat ebből az egészből, mert a könyv elég régi. Az oracle cuccok egészen biztosan nyújtanak ehhez valami támogatást, mintha láttam volna valahol valamit erről...

    Annyira azért nem bonyolult.
    A lényeg, hogy minden táblát legalább 2 részre vágsz szét: 1 az aktuális adatok, ezen működnek a standard foreign keyek, esetleg még az időintervallumok is elhagyhatóak innen. A második a múltbéli adatokat tartalmazza. A múltbéli adatokat tartalmazó táblákon - amúgy logikusan - csak insert van.
    Történeti lekérdezéshez - a megoldástól függően - ezeknek a tábláknak az únióját veszed és mindenhol megadod az időpontot amire kiváncsi vagy. Jellemzően erre valamilyen view-t lehet használni.
    Mutasd a teljes hozzászólást!
  • create database HIST go ALTER AUTHORIZATION ON DATABASE::HIST TO [sa]; go use HIST go create table Ceg ( Ceg_ID int primary key not null IDENTITY (1, 1), Ceg_NEV varchar(50) not null, Ceg_NEV_CHANGED datetime not null, ) go create table Ceg_NEV_Diff ( Ceg_ID int not null, Ceg_NEV varchar(50) not null, Mettol datetime not null, Meddig datetime ) go CREATE TRIGGER initName ON Ceg AFTER INSERT AS DECLARE @PCeg_ID int,@PMettol datetime, @PCeg_NEV varchar(50) SET @PCeg_ID = (SELECT Ceg_ID FROM inserted) SET @PCeg_NEV = (SELECT Ceg_NEV FROM inserted) SET @PMettol = (SELECT Ceg_NEV_CHANGED FROM inserted) insert into Ceg_NEV_Diff (Ceg_ID,Ceg_NEV,Mettol) values (@PCeg_ID,@PCeg_NEV,@PMettol) GO CREATE TRIGGER updateName ON Ceg AFTER UPDATE AS DECLARE @PCeg_ID int,@PMettol datetime, @PCeg_NEV varchar(50) IF ( UPDATE (Ceg_NEV) and UPDATE (Ceg_NEV_CHANGED)) BEGIN SET @PCeg_ID = (SELECT Ceg_ID FROM inserted) SET @PCeg_NEV = (SELECT Ceg_NEV FROM inserted) SET @PMettol = (SELECT Ceg_NEV_CHANGED FROM inserted) update Ceg_NEV_Diff set Meddig=@PMettol where (Ceg_ID=@PCeg_ID) and (Meddig is null) insert into Ceg_NEV_Diff (Ceg_ID,Ceg_NEV,Mettol) values (@PCeg_ID,@PCeg_NEV,GetDate()) END GO create table Dolgozo ( Ceg_ID int foreign key references Ceg(Ceg_ID), Dolgozo_ID int primary key not null IDENTITY (1, 1), Dolgozo_NEV varchar(50) not null, Dolgozo_BELEPETT datetime not null, Dolgozo_KILEPETT datetime ) go insert into Ceg (Ceg_NEV, Ceg_NEV_CHANGED) values ('Munka Kft.', '1990.01.01') update Ceg set Ceg_NEV='Munka Zrt.',Ceg_NEV_CHANGED='2011.01.01' where Ceg_ID=1 insert into Ceg (Ceg_NEV, Ceg_NEV_CHANGED) values ('Masikmunka Bt.', '2000.01.01') insert into Dolgozo (Ceg_ID, Dolgozo_NEV, Dolgozo_BELEPETT) values ('1','Gipsz Jakab', '2006.01.01') go
    Mutasd a teljes hozzászólást!
  • Ez helyett:

    insert into Ceg_NEV_Diff (Ceg_ID,Ceg_NEV,Mettol) values (@PCeg_ID,@PCeg_NEV,GetDate())

    Ezt írtam:
    insert into Ceg_NEV_Diff (Ceg_ID,Ceg_NEV,Mettol,Meddig) values (@PCeg_ID,@PCeg_NEV,@PMettol,GetDate())
    Amúgy most már értem ezt a triggerelés és update dolgot.
    Azt hittem, hogy elég csupán kulcsokkal összekapcsolni a táblákat.

    darph is erre hívta fel a figyelmemet.

    Köszönöm mindenkinek 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