I am designing application for multiple users. Their data (e.g Surname) might change in time. But documents they create should contain their data for moment they created it. For now i am adding new record for any change in user data except those that change password or account expiration. Thus if document record is "signed" with userid e.g. 25 i am getting '25" user data but after he/she changes something , next document will be "signed" with id 26. My concern is that way i am getting alot of "allmost" identical user records. I made unique constraint for fields (userId,Active=true). Is this good approach? Could i make it better ?
What you are looking for is the concept of Event Sourcing (ES). It combines quite well with another great concept - Domain Driven Design (DDD).
In DDD a "thing", which is identified by Id (like User or Account) is called an Entity. State of an Entity changes as a result of processing domain Command (like "ChangeSurnameCommand"). Usually you validate a Command, make some changes to related Entity and persist it to DB. Change itself constitutes an Event (e.g. SurnameChangedEvent).
But instead of storing current state of an Entity in DB, you could store all related Events (e.g. as JSON or XML field). Then an Entity's state - not only current, but for any given moment T - can be calculated as
listOfEvents.foldLeft(InitialState)(applyEvent). I.e. take a list of events, take some initial state, take a function that applies events - and apply them one by one to initial state.
This way you get historical track, can time-travel on an Entity's state, test and debug individual state transitions.
This concept of Event Sourcing comes from business solutions (like accounting) long before computers. But in IT community first pioneers to bring it to us modern developers have been Greg Young and Udi Dahan. You can search YouTube for quite a few great presentations.
Domain-Driven Design was introduced by Eric Evans almost 15 years ago, and got second breath with combination of microservices, functional programming and event sourcing (and closely related CQRS approach).
As for me, ideal event storage solution is yet to be found. By "ideal" I mean distributed and consistent. Direction of thought here is that there can be one kind of events that are not required to be linearizeable, and another kind which are required. Combined with scalability, distribution and robustness it can get quite interesting. But in simple case Postgres (with its TRANSACTION ISOLATION LEVEL LINEARIZEABLE) would suit well.
But prior to thinking of storage I recommend to look for presentations on YouTube and dig into the concept a little bit - you'll see if it suits your needs.
After some research i found that problem i described is called Slowly Changing Dimension (SCD) as described in many sources, like wikipedia: Slowly_changing_dimension and mostly used solutions are:
- Type 0: retain original
- Type 1: overwrite
- Type 2: add new row
- Type 3: add new attribute
- Type 4: add history table
- Type 6: hybrid
My current aproach uses hybrid type solution; interestingly
SQL standard SQL:2011 introduces new feature - Temporal Data Support which deals with "historical data" and allows for queries like:
SELECT * FROM customers AS OF SYSTEM TIME TIMESTAMP '2015-01-22' ;
This is described in postgres wiki:
There is also interestting way to achieve this goal given by iTollu by saving data as series of events, wchich might be later "recalculated" to find state at given moment.