Tracking changes on a table

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?

Update

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).

StackExchange Database Administrators Q#55083, answer score: 3