I have a users table. I want to keep track of all the changes I have done, name, phone etc. I've thought to create an extra table changes that will store the id of the previous and the next version of the user. I keep all the users record. Is this a good approach?
I've also thought to use a separate table for the history see below
Or, to use a single table and track its version from the timestamp or the id (since it's increment), but I consider that as a poor solution.
Your design has a big disadvantage: Getting just the current values (which you will work with most of the time) is an expensive operation that requires a join.
Your system would be much faster if you have a table with the current data and an extra table for historical information, consisting of all columns of users and a valid_until timestamp column, with a primary key over (user_id,valid_until).
One issue that you did not tell relates to the frequency of changes in the various attributes (columns) which object USER (table users) has.
Suppose you have a temporal table which has P columns of which only K, where K is much smaller than P, are usually subject to change. Then having everything on the same table or having only two tables (current records, historical records) will generate more new records than would be necessary.
Would it be possible to split attributes into different tables and having at least two temporal columns (transaction_validfrom,transaction_validto)?
I have to admit that some joins would be necessary to combine columns from different tables into one row but generally those tables having splitted attributes are smaller sou you win in I/O.
Assuming... .you dont need to version more than one table .you don't need to query the table by date
you can create a primary table and a detail table. the primary table will store the user ids and the user detail table will store all the information. The approach I'm showing below gets tedious quickly for large schemas, and very tedious for many to many relationships.
To use this database approach, on inserts, ie new user, a user and user detail are created. To update a user, you create a new userDetail. To query for a user entity, you get the most recent userDetail for a given userId.
If the user detail has an address entity that also needs to be under version control, you need to add a primary and detail table for the address entity as well. This approach gives you a lot of tables. I'm running a modified version of this where the user detail has a start_date and end_date that get updated so that I can easily query for what records were with at date x. (I'm running a very small db) for performant large databases, this is probably a horrible idea.
see the wiki for temporal validity for a bit more insight into what you may be trying to do.
here's an example of data history gone mad using this approach
In short, I'd recommend this approach if used sparingly. I'm kind of noob though so I'm going to put a disclaimer that a noob reccomends this approach as okay. Let me know if you need any clarification.
