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 ?
Updating  database records while retaining old values
programming practices;database design
After some research i found that problem i described is called Slowly Changing Dimension (SCD) as described in many sources, like wikipedia:Slowly_changing_dimensionand mostly used solutions are:Type 0: retain originalType 1: overwriteType 2: add new rowType 3: add new attributeType 4: add history tableType 6: hybridMy current aproach uses hybrid type solution; interestinglySQL standard SQL:2011 introduces new feature - Temporal Data Supportwhich 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:Temporal Data & Timetravel in PostgresqlThere is also interestting way to achieve this goal given by iTolluby saving data as series of events, wchich might be later recalculated to find state at given moment.Regards