Database design - maintaining versions of an object

Question

Suppose I've a Person object. This Person object in turn is having name, address, 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 t0 address1 was associated with personA. And at t1 (t1 > 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:

• t < t0 (in this case the address is not present)
• t0 < t < t1 (in this case the address is address1)
• t > t1 (in this case the address is address2)

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.

My 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 start and end to check the validity of actual row for person_id 1.

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.

Answer 1

As you have correctly identified that this is a "problem" with relational data: Modifying related entries is changing history - so to speak.

The question is: How to preserve identity?

Say you have a typical e-commerce scenario where you have

• a customer
• an order
• items of the order

So there has to be an identity preserved such that

Martha Miller issued Order 12345 and ordered 123456789 Nike shoes

And this identity has to be preserved,

• even when Martha Miller marries at a later point in time and is called Martha Smith from then on
• and moved from New York to Los Angeles
• and item 123456789 is now used for Nike shirts instead of Nike shoes.

One modelling strategy would be using multiple representations for the identical customer.

Say address with id 1 is Martha Miller and address with id 12345 is the same person, but now called Martha Smith and the customer is referenced via the same address_customer_id of say af5aa5df-ad4b-42fa-97ed-e25e8cad1962 such that a customer can have several address entries. And everytime the adress is changed there will be a new entry in the adress table for the customer. And as long as the adress isn't changed the current one is used. And after a change the new one is used and the former one is preserved. So for each point in time, there is exactly one identity of this customer with this data did this order.

The same goes btw. for the items.

Another strategy would simply be denormalization. Since it is historical data and you are only going to read it you will face none of the anomalies which to avoid you usually normalize your data.

Then there is the possibility of leveraging JSON as a datatype in most modern RDBMSses, which allows other fancy options like storing the whole order as a document.

Answer 2

In addition to the excellent answer of Thomas, I'd like to add that there are native DBMS solutions that support this need.

If your DBMS doesn't support temporal data, you may also be interested in having a closer look at the needs and making a distinction between managing of time dependent data and ensuring an audit trail of historic data.

Native implementation techniques

Some RDBMS support natively fully time dependent data, almost exactly as you imagined. In the SQL world, ANSI SQL 2011 introduced system versioned temporal tables:

• SQL server 2016 support this feature. In-depth information can be found here.
• Oracle supports it since 12c. An more detailed introduction here.
• Other RDBMS engines support it as well, such as MariaDB supports, and in the meantime some others.

Time dependent data

Some data is known to be valid for a limited time. We know that the value to be used depends on the relevant date. Typical examples are a person's addresses or product prices. There is in general a clear start date for a new value.

You may then:

• have a main entity for time independent data. The extreme case could be to have only the primary key (and may be the date of creation of the record).
• have a separate entity for time dependent data. The key could be the primary key of the main entity and the start date. A nullable end date could be used to find easily the current value
• sometimes, a closer look allows to identify separate entities behind a group od data. Typically, the address could end up in a separate adress entity with a purpose (e.g. correspondence, delivery and invoice) and a period of validity of its own.

Historic data

Historic data corresponds to a different need. This is for data that is assumed to be permanent, but can change for exceptional reasons. We are therefore mostly interested in the current valid value. Only exceptionally are we interested in the historical value (e.g. for audit purpose, or for legal issues).

An example could be the date of birth. In general it is not supposed to change. But a clerical error might result in this date being corrected at a later stage. If this date was used to give access to age based content (e.g. PG13 games), it could be useful to be able to trace back such changes in case of litigation.

This can be implemented as follows:

• The full user-accessible data is considered as time dependent (see solutions for 1). THe queries are by default looking for the empty end-date. The advantage is that you can easily browse through previous versions.
• The full data is considered as permanent without time dependency, but a separate table is used to archive the historical values in case these are needed. It could be an ugly (but convenient) time dependent clone table. It could also be a lean (uggly but effective) change-log table, with the name of the field, the old and new value, the date of the change and the user who made the change (a worldwide ERP market leader uses this technique to log changes to any critical field. It uses a change header to record the user and the date of change, and several related change items with the table-name, field-name and the changed data)