Updating/moving a record to exist in the past

[aqr-leonea]

Context
I'm using Reladomo to manage bi-temporal data. One time dimension is for audit purposes (transaction time). The other time dimension is for business purposes (valid time). A primary key is being used that contains a unique identifier for each entity. Each entity also has a "name" column stored in the database.

Scenario

1. You insert a record that is valid from present time to infinity with id=5 (primary key) and name="A"
2. You realize that the entity existed in the past but it had a different name (name = "B")

What is the proper way to correct history on the business time dimension (valid dates)?

Approach 1 (This one does not work. Reladomo does not seem to support changing the valid from date?)

1. Find dto where id = 5 in present time
2. Set the validFrom date backwards in history to show that this entity existed in the past
3. Update the name to equal "B" from past date until present time

Approach 2

1. Call insert again and pass an entity that contains the primary key (id = 5), name = "B", validFrom = "some past date", validTo = "present/current time"

Questions

• What is the recommended way for updating history to show that the entity existed in the past?
• Is there anyway to slide the validFrom date backwards?
• Is there any danger in inserting doing several inserts with the same primary key? I noticed that there is nothing preventing me from inserting the entity again with id=5 and name="B" where the validTo date is greater then the valid from date on the first insert. This causes the dtos to overlap and there to exist two rows with id = 5 at a single point in time.

[mohrezaei]

• Approach 2 is generally what you want. Please also note the existence of the insertUntil() method.
• Moving validFrom back doesn't make sense because it destroys audit history: you didn't know that this entity existed before (even if the name was the same) and that fact has to be preserved. In other words, there have to be two rows with two different audit spans.
• You have to do your inserts correctly to avoid overlap. Generally, you'd want to do all the work in a single transaction: read the existing record, decide on the correct end point, insert. DB indices prevent simple overlaps, but they can't deal with complex ones.