Tracking Database Changes

[hackalive]

Hi Guys,

 

I have a database table and I want to be able to track all the changes to it, much like wikipedia you can see previous versions.

 

I have many fields within the table, extract/example below.

 

TBL

id (PK)

name

address

 

So I want to track any and all changes to any of the fields

 

Whats the best way to achieve this?

 

Let me know if you need any more info.

Edited August 24, 2013 by hackalive

[ignace]

If you use Doctrine you can create your database schema in code. Schema updates can then be performed through code.

[hackalive]

I dont want to update the schema.

 

I was to track changes of a recod/row.

 

So if I update the name, I should be able to see previous names and revisions and by whom. Same as like with wikipedia articles (kind of).

[ignace]

table (.., version)

table_versions (.., version)

In table version is default 1 when you update the row you copy it to the *_versions table. You update the table, and increment the version on the 'table' table. You might wanna lock the row/table before doing this to avoid multiple people overriding each others changes. Edited August 24, 2013 by ignace

[jcbones]

Or, you could never update, only insert. Returning the last row inserted, until you need to see what was revised.

[hackalive]

Ok, any other suggestions?

[trq]

Nope, that about covers it.

[AdrianBV]

not really ... you have one other option ... another table that will record every change. For example:

 

your master table is: id, name, address, phone

 

you could make a second table with the following structure: id, id_changed, changed_var, prev_value, current_value, changed_date, ip or user_id ( etc )

 

and for every update of a row you could insert a line in the second table like this:

 

2, address, Lake City, Lake City 3, 2013-03-03 12:32:23, 232.23.223.23

 

for every new row added in the master table you could do the same but leave empty the prev_value ( or maybe add a TINYINT in the second table to set as 1 or 0 if it's a new row or a update )

 

 

If there are a lot of changes the second table could get big but if you really want to track everything this is the ONLY way you can.

 

With this implementation you can see every modification to each id or even the whole table at a precise moment in time.

 

If you have any other questions feel free to ask.

Edited August 25, 2013 by AdrianBV

[jazzman1]

Basically, I would say the same as AdrianBV's post above.

if these updates not happened every minute, hour or day, you could create a second table which may look as the master one or just replicate the columns name that you need to use for yourself.

For example:

CREATE TABLE replicate_master (id, author_name, author_text)

Then, whatever you need to update the table, you could run the following query:
 

REPLACE INTO `replicate_master` SELECT `author_name`, `author_text` FROM `master_table` GROUP BY `author_name`

Later, create a cron job, which it runs on a schedule basis and execute this query from the shell using the command-line client with the "-e" flag.

It would be something like:
 

/usr/bin/mysql -h "db_server" -u "db_name" -p "db_pass" --database="db_name" -e "REPLACE INTO `replicate_master` SELECT `author_name`, `author_text` FROM `master_table` GROUP BY `author_name`"

If these updates happen every minute or less you can execute this "REPLACE INTO" statement before someone try to update some values in this table.
 

REPLACE INTO `replicate_master` SELECT `author_name`, `author_text` FROM `master_table` WHERE `author_name` = '$author_name' GROUP BY `author_name`

[ignace]

I think it's great to see two people decide to repeat what I said.

 

 

table (.., version)
table_versions (.., version)

In table version is default 1 when you update the row you copy it to the *_versions table. You update the table, and increment the version on the 'table' table. You might wanna lock the row/table before doing this to avoid multiple people overriding each others changes.

 

[jazzman1]

He-he.......yeah....but with examples