Database PK-FK design for future-effective-date entries?
- by Scott Balmos
Ultimately I'm going to convert this into a Hibernate/JPA design. But I wanted to start out from purely a database perspective. We have various tables containing data that is future-effective-dated. Take an employee table with the following pseudo-definition:
employee
id INT AUTO_INCREMENT
... data fields ...
effectiveFrom DATE
effectiveTo DATE
employee_reviews
id INT AUTO_INCREMENT
employee_id INT FK employee.id
Very simplistic. But let's say Employee A has id = 1, effectiveFrom = 1/1/2011, effectiveTo = 1/1/2099. That employee is going to be changing jobs in the future, which would in theory create a new row, id = 2 with effectiveFrom = 7/1/2011, effectiveTo = 1/1/2099, and id = 1's effectiveTo updated to 6/30/2011. But now, my program would have to go through any table that has a FK relationship to employee every night, and update those FK to reference the newly-effective employee entry.
I have seen various postings in both pure SQL and Hibernate forums that I should have a separate employee_versions table, which is where I would have all effective-dated data stored, resulting in the updated pseudo-definition below:
employee
id INT AUTO_INCREMENT
employee_versions
id INT AUTO_INCREMENT
employee_id INT FK employee.id
... data fields ...
effectiveFrom DATE
effectiveTo DATE
employee_reviews
id INT AUTO_INCREMENT
employee_id INT FK employee.id
Then to get any actual data, one would have to actually select from employee_versions with the proper employee_id and date range. This feels rather unnatural to have this secondary "versions" table for each versioned entity.
Anyone have any opinions, suggestions from your own prior work, etc? Like I said, I'm taking this purely from a general SQL design standpoint first before layering in Hibernate on top. Thanks!
