Clever Geek Handbook

Saving old data modified by the user

I have a table users

that has the following fields: userid, phone, and address

. Since this is user data, I allow the user to change it whenever he wants. The problem is, I would like to track these changes and keep the old data. Here are some of the ideas I've looked at:

  • adding new data to old data and using a separator such as a pipe. When you fetch a field, I would check for the existence of that delimiter, and if it does, we get the characters after it as new data. (feels cumbersome and doesn't feel good)

  • another table setting changes

    with the following fields: userid, fieldname, fieldcontent

    . When / if the user changes the data (any data), I log the event in this separate table under the user id userid as well as the name / id of the field and the old content of the field, then now I can overwrite its old data in users

    with the new one. If I want to find all the changes made by this user, I would search the table changes

    with my user ID. The problem is that I am mixing all data changes (all fields) into one table, so the field fieldcontent

    in changes

    must be text to accommodate different types of fields. This still seems like better than the first idea, but still not sure if I am doing the right thing.

What other ideas or known guidelines exist for storing old data?

Thank you in advance

+2


source to share


3 answers


Whatever you do, don't do the first.

Change table is the best approach. It is also called an audit or history table. However, I would not use key value pairs. Instead, make history behind an appropriate table. You can do this in your application code or through database triggers. Basically, whenever an insert, update, or delete occurs, you record what happened and what data was changed.

Table user:



  • ID
  • Username
  • E-mail address
  • telephone
  • address

User_history table:

  • ID
  • change_type (I, U, or D for insert, update, or delete)
  • user_id (FK user.id)
  • E-mail address
  • telephone
  • address
  • date / time of change
  • optional, also save who changed the entry
+3


source


A very simple way that we used to track such changes is as follows:

users_history` 
    userid 
    changenumber smallint not null
    changedate datetime not null
    changeaddr varchar(32) not null
    phone NULL,
    address NULL

    primary key on (userid, linenumber)

Every time you insert or UPDATE a record in the table users

, just INSERT a new record in the users_history table. changenumber

starts at 1 and increases from there. changedate

and changeaddr

can be used to track when and where.

If the value of the field has not changed, feel free to put NULL in the corresponding field in the table users_history

.

At the end of the day, your application doesn't need to modify or store cumbersome historical data in a table users

, but you have it all if it's at your fingertips.

Edit:



This keeps the old data. See the following example, where the user started with a given address and phone number, then updated the address after 4 days and updated the phone after 5 days. You have everything.

Current user record:

100                            |  234-567-8901   |   123 Sesame Street


Sample History Table

100   |  1  | 2009-10-01 12:00 |  123-456-7890   |   555 Johnson Street
100   |  2  | 2009-10-05 13:00 |  NULL           |   123 Sesame Street
100   |  3  | 2009-10-10 15:00 |  234-567-8901   |   NULL
+1


source


The easiest way to implement this would be to have another table just for history purposes - a snapshot. You don't need to reflect all fields, just

change_id // row id (just for easy management later on if you need to delete specific row, otherwise its not really necessary)
user_id // Original user id
change_time // time of change
data // serialized data before change.
0


source






More articles:


All Articles