6

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?

history

Update

I've also thought to use a separate table for the history see below

separate history

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.

enter image description here

  • Check my answer here to see if it helps. The concept is similar to use a trigger for auditing. – Kin Shah Dec 16 '13 at 14:06
  • @Kin The concept is to find a better schema for my design. Thnx for the reference I'm looking in to it although a lot of things in there look very strange to me. I don't care about trigger, I can store in the application layer. – giannis christofakis Dec 17 '13 at 21:15
3

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

  • Don't you think an intersection table is necessary? – giannis christofakis Dec 16 '13 at 10:34
  • 1
    No. You only had to include that in your updated design because you generated a new ID for the history. That is unnecessary. – Twinkles Dec 16 '13 at 11:02
  • The relationship between userinfo and userinfo_history should be 1:n, not m:n. – Twinkles Dec 16 '13 at 11:13
3

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.

0

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.

primary table and detail table

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

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.