Preserve old record values in relational DB

Question

What is the best approach for the following situation?

Assume, that we have the following tables:

Companies:

COMPANY_ID
COMPANY_NAME

People:

PERSON_ID
PERSON_NAME
FK_COMPANY_ID

Meetings:

MEETING_ID
MEETING_NAME
MEETING_DATE
FK1_PERSON_ID
FK2_PERSON_ID

We have the following data:

/* Companies */
1, Company #1
2, Company #2
3, Company #3

/* People */
1, Vasya Pupkin, 1
2, Petya Vasechkin, 2

/* Meetings */
1, Meeting #1, 2014-02-01, 1, 2

Now, imagine that the person from Company #1 changes his company to Company #3. This will be of course reflected in the meetings view due to relation through person. And therefore, in the view form of the meeting it will show incorrect data in terms of the past situation (when a person was working in another company).

So, my question is how to preserve the view data for the meeting that has already past?

Currently I see the following solution. Extend the Meetings table as follows:

MEETING_ID
MEETING_NAME
MEETING_DATE
FK1_PERSON_ID
FK2_PERSON_ID
FK1_COMPANY_ID
COMPANY_NAME_1
FK2_COMPANY_ID
COMPANY_NAME_2

So that for historical purposes it will be possible to select the company_name_1 and company_name_2 rather than check actual person's company. And however, this would work even faster than relation, at the same time this moves me away from relational DB, and as this has to be used not just in one place, but in many places of a big project, it turns out that I will have a lot of data duplication.

Therefore, are there any better solutions for this problem?

UPDATE: I see one possible solution in terms of relational DB. We need to introduce a new table:

Employments:

EMPLOYMENT_ID
FK_PERSON_ID
FK_COMPANY_ID
EMPLOYMENT_DATE

Assuming that a person can work only in a one company at the same time, we can know for sure, which company he worked in during the meeting. However, a small issue still exists: the company name (if renamed) will be the last one used. This can be fixed by the similar approach (if needed):

Company_Names:

FK_COMPANY_ID
COMPANY_NAME
ACTUAL_FROM_DATE

Seems that it adds some complexity but flexibility at the same time.

Add another column TERMINATION_DATE, which if null means they are still employed. Trust me - you'll need it, because employment is rarely contiguous, and without it your queries will be nightmares. — Bohemian, Feb 03 '14 at 12:00
@Bohemian thanks I will think of it. Initially I was thinking of NULL for FK_COMPANY_ID would mean unemployment. But apparently you are right. — Denis V, Feb 03 '14 at 14:57
If you are using Postgres you probably want to use a `daterange` column with a constraint to make sure there are no overlapping intervals. — , Dec 21 '14 at 09:17

score 0 · Answer 1 · edited May 23 '17 at 11:49

This is a standard problem that one encounters in real world databases. Your solution looks good. Keep in mind now, that to find out where some works you will need to do something like:

select * from employment where person_id=123 and 
employment_date=(select max(employment_date) 
                 from employment where person_id = 123)

That might be OK, but there are other solutions. For example, you can add a last_known column to employment and then your query for finding where a person works can now be:

select * from employment where person_id=123 and 
last_known=1

There is a question https://stackoverflow.com/a/19144370/4350148 that may also be helpful.

Preserve old record values in relational DB

1 Answers1