Every temporal table in Babase has a Sys_Period column, used to
record when each row of each table was changed in any way. When
a row in a babase table is
updated or deleted, the "old" version is saved in the table's
corresponding "history" table.
When added to the history table, the exclusive upper bound in
the Sys_Period is set to the
current_timestamp. That is, the date and
time of the UPDATE or
DELETE that moved that version of the row
from the table in babase to
the history table in babase_history.
Thus, the data in babase
can be continually updated, but earlier versions remain
available. See the below example.
It should be emphasized that all details provided here are purely fictional. Specific names, dates, and times are used to avoid ambiguity; they do not refer to any real data, events, or personnel.
Suppose that all tables in Babase were populated with many years' worth of data before the Sys_Period column was added to each of them on 08 Sep 2010, at 07:06:05, and that the babase_history schema and its history tables were created just a few seconds later.
Further, suppose there's an individual named TIM.
Long before any tables became temporal, TIM was recorded
in MATUREDATES as having matured "ON"
2003-02-01:
select * from maturedates where sname='TIM';
sname ¦ matured ¦ mstatus ¦ sys_period
−−−−−−+−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−--−
TIM ¦ 2003-02-01 ¦ O ¦ ["2010-09-08 07:06:05",)
Note that the beginning of the Sys_Period is the time that the column was added, not when this row was added to the table many years earlier. The system does not and cannot say anything about changes in a table before we began recording its history.
Having just been created, the MATUREDATES_HISTORY table in babase_history is empty, and will remain empty until any rows in MATUREDATES are updated or deleted.
On 10 Oct 2010, a data manager realized that there was a typo during data entry, and the year of TIM's Matured should actually be 2002. Upon realizing the mistake, at 10:10:10 she updated the MATUREDATES row with the correct date.
select * from maturedates where sname='TIM';
sname ¦ matured ¦ mstatus ¦ sys_period
−−−−−−+−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−
TIM ¦ 2002-02-01 ¦ O ¦ ["2010-10-10 10:10:10",)
The "old" version of the row, in which TIM matured in 2003, is no longer in MATUREDATES. However, it is retained in babase_history for future recall:
SELECT * FROM babase_history.maturedates_history where sname = 'TIM';
sname ¦ matured ¦ mstatus ¦ sys_period
−−−−−−+−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−-
TIM ¦ 2003-02-01 ¦ O ¦ ["2010-09-08 07:06:05","2010-10-10 10:10:10")
Note that the Sys_Period for this "old" version now has an (exclusive) end — the time that the row was updated — and that the Sys_Period of the "current" version in MATUREDATES begins (inclusive) at that same time.
A short time after maturing, TIM migrated to a
nonstudy group and observers lost the ability to identify
him. Several years later (November 2011) he returned to a
study group, but observers didn't recognize him. He was
presumed to be a new, never-before-seen male, and was given
a new name: JIM. In February 2012, when this "new
immigrant" was recorded in the database, JIM was recorded as
having matured "BY" the date that he appeared,
2011-11-01:
select * from maturedates where sname='JIM';
sname ¦ matured ¦ mstatus ¦ sys_period
−−−−−−+−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−--−
JIM ¦ 2011-11-01 ¦ B ¦ ["2012-02-22 22:22:22",)
Having just been added to MATUREDATES, "JIM" does not yet have any rows in MATUREDATES_HISTORY.
Years later, genetic analyses showed that TIM and JIM were the same individual. Having two rows in MATUREDATES for a single individual doesn't make sense, so something needs to be corrected:
select * from maturedates where sname in ('JIM', 'TIM');
sname ¦ matured ¦ mstatus ¦ sys_period
−−−−−−+−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−--−
JIM ¦ 2011-11-01 ¦ B ¦ ["2012-02-22 22:22:22",)
TIM ¦ 2002-02-01 ¦ O ¦ ["2010-10-10 10:10:10",)
At this point, MATUREDATES_HISTORY still only has the one row with TIM's old Matured.
SELECT * FROM babase_history.maturedates_history where sname in ('JIM', 'TIM');
sname ¦ matured ¦ mstatus ¦ sys_period
−−−−−−+−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−-
TIM ¦ 2003-02-01 ¦ O ¦ ["2010-09-08 07:06:05","2010-10-10 10:10:10")
There are a few different ways to resolve the situation with TIM/JIM. The two most likely options are explored below.
All of JIM's data in Babase could be merged into the data for TIM. In MATUREDATES, TIM's maturity "ON" 2002 is more informative than JIM's maturity "BY" 2011, so JIM's row would simply need to be removed.
Following the 7 Jun 2018 05:03:09 deletion of JIM's row, JIM's and TIM's data in the two tables will look like this:
select * from maturedates where sname in ('JIM', 'TIM');
sname ¦ matured ¦ mstatus ¦ sys_period
−−−−−−+−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−--−
TIM ¦ 2002-02-01 ¦ O ¦ ["2010-10-10 10:10:10",)
SELECT * FROM babase_history.maturedates_history where sname in ('JIM', 'TIM');
sname ¦ matured ¦ mstatus ¦ sys_period
−−−−−−+−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−-
TIM ¦ 2003-02-01 ¦ O ¦ ["2010-09-08 07:06:05","2010-10-10 10:10:10")
JIM ¦ 2011-11-01 ¦ B ¦ ["2012-02-22 22:22:22","2018-06-07 05:03:09")
As the more recently-used ID, it may be preferable to keep the name, JIM. All of TIM's data in Babase would thus be merged with the data for JIM. As mentioned above, TIM's maturity "ON" 2002 is more informative than JIM's maturity "BY" 2011, so JIM's row would need to update its Matured and Mstatus to match those of TIM. Also, TIM's row would need to be removed.
Following the 7 Jun 2018 05:03:09 update of JIM's row and deletion of TIM's, JIM's and TIM's data in the two tables will look like this:
select * from maturedates where sname in ('JIM', 'TIM');
sname ¦ matured ¦ mstatus ¦ sys_period
−−−−−−+−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−--−
JIM ¦ 2002-02-01 ¦ O ¦ ["2018-06-07 05:03:09",)
SELECT * FROM babase_history.maturedates_history where sname in ('JIM', 'TIM');
sname ¦ matured ¦ mstatus ¦ sys_period
−−−−−−+−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−-
TIM ¦ 2003-02-01 ¦ O ¦ ["2010-09-08 07:06:05","2010-10-10 10:10:10")
TIM ¦ 2002-02-01 ¦ O ¦ ["2010-10-10 10:10:10","2018-06-07 05:03:09")
JIM ¦ 2011-11-01 ¦ B ¦ ["2012-02-22 22:22:22","2018-06-07 05:03:09")