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

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!

Go to Source
Author: Markus Knappen Johansson