| Historical Tables & Maintaining Fkey [message #64421] |
Wed, 26 January 2005 09:31  |
CTNewBie
Messages: 1
Registered: January 2005
|
Junior Member |
|
|
Folks,
We are in the design phase of a physical model. We have Tables that need to maintain Historical data. We introduced effective and end dates on the table. Whenever a change was made to the records on the table, we set the end date on the current record to sysdate (Closed record) and create a new record on the same table with null end date (New Current record).
Say Table A (col1 , col2, eff_date, end_date)
with col1 being primary key
Table B (col21, col22,eff_date, end_date)
where col21 Fkey to TableA.Col1
Now when a change is made on Table A, we create a new record and thereby the child record has now lost the link to the current disposition of its parent. Question is how do I maintain the relationship intact in a situation like this?
here is a thought:
Table A values (val1 , val2, eff_date, end_date)
Table B values (bval1,val1,eff_date, end_date)
Dont create a Fkey between Table A and B. But write a trigger around Table B making sure Val1 always exists in table A. This way, when I can write a query like
select a.*, b.*
from tableA a, tableb b
where a.col1 = b.col21
and a.end_date is null
and b.end_date is null
This ensures we retrieve current disposition of table A and B and maintain history. Does this work?
Thanks
|
|
|
|
| Re: Historical Tables & Maintaining Fkey [message #64423 is a reply to message #64421] |
Wed, 26 January 2005 22:17  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
I'd say that is the way to go.
I don't think though I would use the trigger to check the referential integrity; on deletion of table A it will not work anyway.
I think I would prevent DML on tableB, except when it is done by the trigger on tableA:
Create trigger on tableB that fails if some sys_context variable is not set. Set this variable in the trigger on tableA prior to updating/inserting tableB and unset it afterwards.
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
