Detecting Change

[Jeewhizz]

Just want to have a sounding off topic on the best way of doign this. I have a table with about 20 fields, all which is filled in by a company's employees...

Reports are automatically created, and what they want, is items to appear in red if they've been altered since the initial signup....

How would you do this? I'm thinking having another table, with just the field name and linking that to the employee ID. Could also stick a timestamp in that as well... then when it comes to the reports, it'll check the other table to see if its listed...

but that's gonna be a helluva lot of queries for a table with about 20 fields, and at least 100 employees (about 2000 queries!)

Anyone got any thoughts?

Cheers

Jee

[raf]

Euh, shouldn't this be in your forum?

Anyway, i've only seen 'rowlevel' implementations of this (a timestamp + userID last updater). I think that, if it realy is that important, you'd better just create a timefield for each relevant column (which you of course update if the column is changed and that get a name like columnnameupd <edit>+ possably set to Null after the report was generated (if they are generated only once)</edit>).
I've alway's heard that the number of column's doesn't weigh so much on the db-performance (the number of records, joining etc has a heigher impact). So you're recordset will be a bit bigger and you'll need a few extra comparisons inside your loop to generate the report

[mordred]

2000 queries.... that's too much...

Quick idea:
Have one table that stores the initial setup records. Have another table that is an exact duplicate of the first table, and only in this one the updates take place. When you select the record, you only need one query, which selects the current fields from the "updated table" and the result of an IF() function that compares if the field has been changed, depending on the field values of the "initial table" which must be joined for this query.
With that you'd only need 100 queries.

Additional feature: You can reset the record if that's ever needed...

[Jeewhizz]

Originally posted by mordred
2000 queries.... that's too much...

Quick idea:
Have one table that stores the initial setup records. Have another table that is an exact duplicate of the first table, and only in this one the updates take place. When you select the record, you only need one query, which selects the current fields from the "updated table" and the result of an IF() function that compares if the field has been changed, depending on the field values of the "initial table" which must be joined for this query.
With that you'd only need 100 queries.

Additional feature: You can reset the record if that's ever needed...

Ahh.. that sounds good... i'll have to have a little play... thing is i need to know if a particular ield for a row has changed, not that entire row.... I'm thinking of just querying each table, pulling results into two arrays, and comparing them :)

i.e.
$table1 and $table 2 are arrays


for($i=0;$i<count($table1);$I++)
{
if($table1[$i]!==$table2[$i])
{
$table2[$i] = "<font color=\"red\">".$table2[i]."</font>";
}
}


Look ok? I'll use ofreach i think - i'm not sure ;)

Jee

[michael.hd]

Another way could be this..

Add an extra column to each table. Say we call it 'changes',

in your script to update tables - get it to add the name(or number) of the changed columns to a string stored in the 'changes' column.
Then, when retrieving a row, the comma delimited text in the changes column can be accessed to tell you what has changed.

No joins etc required so faster queries, slightly slower updates.

This is all assuming that the date of a change, or the actual details changed, are not required.

If you need to store the changes so that you can see what the column value used to be (and when it was changed)

Try this...
for each table have an archive_table
When you update a record -
(1) copy the original data into the archive table (this can have an additional timestamp column,
(2) update the data
(3) add the numbers(names) of the changed columns to the 'changes' column.

Every query of the table will be able to say which columns have ever been updated, and if the user needs more info it can be pulled from the archive table - you can even use it to rollback data.
If user x changes data malliciously (i can't spell), all changes and time of change will be recorded, and can be undone.

Depending on the number of rows in the table and the frequency of changes it might be acceptable to store archived rows in the original table (using the timestamp as a flag to indicate old data) - then no joins are needed at all.