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