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!