Database Records - Effective Start and End Dates and Foreign Keys

Question

Does anyone have any experience having worked with datasets where records become valid and invalid based on an effective start and end date? The issue is when records in these tables have foreign keys to other tables that also have effective start and end dates

Seems you have to end up creating new records for just about every table to allow the foreign key updates while maintaing the history.

Example:

--- Pet ---
Dog named jasper, owner is Jack from jan -> jun and sells to Ken from Jun - > end of time

--- Owner ---
Jack lives at House A from jan -> feb, then House B from feb -> end of time
Ken lives at House C from feb -> september, House D from sept -> end of time

so if a report for jasper in june, owner will be jack living at House B. Same query run for september will be Ken in House D.

How is the best way to model this relationship? And how to perform 'updates' to the data to maintain the appropriate relationships?

Answer 1

The approach we're using for such problems is to not hold the ownership of the elements in the same table but in a mapping table which includes the timelines.

Table DOGS
 - ID
 - NAME

Table OWNER
 - ID
 - NAME

Table LOCATION
 - ID
 - HOUSE_NAME

Table DOGS_OWNERSHIP:
 - DOG_ID (FK->DOGS.ID)
 - OWNER_ID (FK->OWNER.ID)
 - START_TIME
 - END_TIME

Table OWNER_LOCATION
 - OWNER_ID (FK->OWNER.ID)
 - LOCATION_ID (FK->LOCATION.ID)
 - START_TIME
 - END_TIME

That way you can maintain the ownership, location and the respective timelines completely in the two tables.

If an ownership or location changes, you update the END_TIME of the current ownership/location in the respective timeline-table and add a new entry starting from the current time and the new end-time (end-of-time).

You also would like to add some constraints to make sure, that there aren't any overlapping mappings, but that's up to your business-logic and preferences.

Answer 2

"Does anyone have any experience having worked with datasets where records become valid and invalid based on an effective start and end date?"

Yes I have (but that's almost 15 years ago).

Good news : your problem (which is to manage temporal data) is solvable.

Bad news : your problem is not solvable with any SQL system that exists today, and is unlikely to become solvable with any SQL system that will come to existence in the future.

For a foundational treatment of the problem, I can recommend a reading of "Temporal Data and the Relational Model" by Lorentzos, Darwen and Date.

For a prototype implementation of their ideas, I can recommend SIRA_PRISE.