Suppose I've a
Person object. This
Person object in turn is having
accountNumber and many other fields.
Now my requirement is that I want to maintain multiple version for each person in such a way that if I'm asked to give what's the state of any person at a given point in time, I can "re-construct"/"fetch" that state.
To give an example, let's consider that at time
address1 was associated with
personA. And at
t0), this person's address is changed to
address2. Now I need to store both these information. My business need is that I can be asked what's the person's address at any point in time
t where t can be:
t0(in this case the address is not present)
t1(in this case the address is
t1(in this case the address is
I want to model this in Database.
I don't want to use temporal tables because it's my business need and temporals are used for reporting and auditing purpose. (Or in worst case debugging an issue)
I want to keep things simple and because of that reason, I'm inserting the complete
Person object into DB even in case of smallest change.
person table looks something like this:
| person_id | name | address | phone_number | account_number | start | end | | --------- + ------- + -------------- + ------------ + -------------- + ----- + --- | | 1 | personA | address text 1 | 123456789 | 741852963 | t0 | t1 | | 1 | personA | address text 2 | 123456789 | 741852963 | t1 | - |
Note that the
person_id in this case is not unique in this table (and therefore it can't be a primary key in this table), because for a single person there can be multiple edits and so same
person_id can repeate.
My business case is solved in a way that I can identify using
end to check the validity of actual row for
The problem that I'm facing is I can use
person_id as the reference key in other tables. Which I want to.
A unique auto-incremental column can be added (say
row_id) that will uniquely determine every row in
person table and that can be used as Primary key in this table and reference key in other tables. I don't want to do that since every other table will then have to refer to this table to see if any two
row_ids are corresponding to the same
person_id or not.
One solution that comes to my mind is that I can have a separate table
person_unique_ids that will have a single column and will just store the unique ids of person. The
person table will have the
person_id which will be a reference to
person_unique_ids.person_id and this way in my other tables also I can use this id without worrying about different ids for a single person being propogated in other tables.
But the above solution suggests to me that when inserting a new person, I should first insert an entry in
person_unique_ids and get the newly inserted entry and use this to insert it in my main
person table that will actually hold all the data.
This is leading to concurrency issues and I'm forced to synchronize the complete insert that's becoming a bottleneck for me.
Can anybody suggest an approach to this problem where I don't have a separate table, and still I'm able to solve all the historical data?
Note that in my actual use-case, I don't have person object. Rather its way too complex object. I've used
Person just to mimic my use-case.