How to model database for system with historical relationships (many-to-many and one-to-many)

Question

I'm working on a system with a main entity, let's say "Members", these members have relationships to other entities and it's important to keep the history to be able to query and know the relationships at a given time in history.

We mainly have two kind of relationships:

• Company (most exist and can only be on company at a given time)
• Tags (optional, multiple tags at any given time)

After looking at this I was thinking that I could model the relationships with dates on the "relation table" something like this:

Tag:
- Id
- Text

Company
- Id 
- Name

Member:
- Id
- Name

Member2Tag
- Id
- MemberId
- TagId
- Added (DateTime)
- Removed (Nullable DateTime)

Member2Company
- Id
- MemberId
- CompanyId
- Added (DateTime)
- Removed (Nullable DateTime)

This way I've managed to query for relationships at any given time in history, but I started thinking about this and felt a little unsure if the "Removed"-column is really needed, I guess it is since I can't remove the relation-row (need to keep history).

I just wanted some feedback on the design, what do you think? Have someone else faced a similar problem and used another solution?

All the best!

Answer 1

Your design seems pretty good in in my opinion.

I'd just add that:

For the Member2Company table, specifically

• Create a trigger to set the Remove column from the previously active member-company relationship so there are never two active ones (with null value in Removed)
• The same trigger should make sure the date range doesn't overlap any previous one, for example that the Added date to be inserted (or updated) falls between the data rage of any other row for the same member and company.
• The same trigger should prevent the existence of time "gaps" between periods for the same member-company given the fact that that relationship "must exist", i.e. is a mandatory business rule and should not be time periods unaccounted for.
• It can be three different triggers if the RDBMS supports "chained" triggers, i.e. triggers that will run only after the previous one finished. Oracle supports that in triggers.

Answer 2

You will need the both the Added and the Removed attributes:

• For Member -> Company you could not have any gaps between two consecutive assignments if you would keep only the start date. Morover, it is easier to find the active links by havein two dates and selecting those with Remove being null.
• Form Company -> Member you could not easily find all the active members if you would have only one date, since you would have to analyse the ordered list member by member to find the ones that count. I t would be especially inefficient. Again, it's very easy to find all the active members with the two dates.
• The same applies to tags. The argumet is even stronger, because more than one tag can be active at any given time, whereas solution with a signle date require a strict sequentiality of the links.

Now in real life, companies change their name from time to time. Members may also change their name. If you want to maintain the identity of these entities (e.g. not remove a link and add a new company and a new link):

• You may want for each entity keep the start validity date of the current name, and in a separate table magage the history (i.e. id, old_name, from, to). This would allow you to state that Ms.Y was member of Company X for 10 years, but if needed, you could also say that company X was walled company Z before. And you could even deduce the full history, comining attribute history with link history.

While this is not directly related to your question, you may be interested in the following So answers:

• Maintaining object versions in a database (essentially about attributes history).
• Keeping historical relationship in a graph (it's a little more general than your relationship history, but it's a similar problem);