PK-FK database for future entries?

Question

Ultimately, I'm going to convert this to a Hibernate / JPA project. But I wanted to start with a pure database. We have different tables containing data that are dated in the future. Take the 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 simplified. But let them say that Employee A has id = 1, effectiveFrom = 1/1/2011, effectiveTo = 1/1/2099. This employee will change work in the future, which theoretically will create a new line, id = 2 with efficiency From = 7/1/2011, effectiveTo = 1/1/2099 and id = 1 effectiveTo updated to 6/30/2011. But now my program will have to go through any table that has an FK connection with an employee every night, and update these FK to refer to a new effective employee entry.

I have seen various publications on pure SQL and Hibernate forums that should have a separate employee_versions table in which I would contain all the data with effective dates, resulting in an updated pseudo-definition:

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, in order to get any actual data, one would actually have to choose from employee_versions with the corresponding employee_id number and date. It looks rather unnatural to have this secondary "version" table for each object with a version.

Anyone have any opinions, suggestions from your own previous work, etc.? As I said, I take it purely from the general point of view of SQL design, first of all, before layering in Hibernate from above. Thanks!

Answer 1

This employee will change work in the future, which theoretically will create a new (working) line

Why? What is the point? Your employee subject no longer represents an employee; it represents some abstraction concept of a person in position.

I believe that it would be wiser to separate the entity that changes when an employee “changes work” - position - into a separate table, so you don’t get any dirty concept, where in fact one physical person has several rows of employee .

I don’t understand why you think that it seems “unnatural” to choose from an additional table - you will separate something that has a plurality (human position) from something unique (employee).

Answer 2

You need to decide whether you are developing a database to support operations or a data warehouse to support reports. If this is the second, your design at the beginning is very similar to the slowly changing size of Kimbal Type 2. Traditionally, you would like your operating database to represent the latest version of your employee and provide it with some kind of business key (employee number, SSN and etc.). The data can then be uploaded to the data warehouse, where each individual record in the EMPLOYEE dimension will have a surrogate key and effective / end dates. Facts, such as reviews, will be associated with entries in the EMPLOYEE dimension based on a business key and date / time. For example, you can differentiate the reviews of employee A when he was a junior technician, after his transition to the position of senior engineer.

Answer 3

For this kind of thing, we usually have one logical field called active, which allows us to simplify the query for the last record used.

Answer 4

To expand on the matte b answer , your discussion of the problem area makes it pretty clear that what your design calls is a position table. Employee reviews continue to be relevant for this employee even after they move to a new position. In addition, in every corporation I have experienced, the concept of employee ownership is related to their entire history in the company, and not just to their current position.

In general, it’s good practice to look at any complex updates needed as a sign that the design is about to change.

Answer 5

There is an entity that corresponds to a unique person:

  EMPLOYEE eeid PK firstname surname dateofbirth dateofhire dateoftermination etc 

and there is an object that corresponds to the position (s) held by the employee:

  EMPLOYEEPOSITION id pk eeid FK references EMPLOYEE(eeid) title reportsto FK references EMPLOYEE(eeid) startdate not null usually enddate allows null 

The question of how to ensure that EMPLOYEE positions can overlap is usually not addressed by creating multiple EMPLOYEE records. Inserts / updates for EMPLOYEEPOSITION usually look at the startdate / enddate columns for each of the EE positions and, depending on which rule is in effect (for example, overlays are allowed / denied), commit or roll back the operation.

All EE items can be found using eeid.

Usually you do not specify an end date in an EE record until you need it. If EE is a contract employee, I would have entered into a contract term as COOPERATION.

You can likewise treat any object that exists in a many-to-one relationship, EMPLOYEE.

Answer 6

Search for temporary databases . Your data is temporary, although dates may be in the future. Presumably, the future oriented data that you are inserting now will still have the same shape and meaning when the entry into force takes effect.