Track Data Changes

Question

I work on a website focused on a market research database developed in PHP and MySQL. It consists of two large parts: one in which users insert and update their own data (let them say one table T with the user_id field), and the other, in which the website administrator can insert new or update existing records (the same table).

Obviously, in some cases, end users will have their data overridden by the administrator, while in other cases, the administrator enters data updated by end users (this is normal in both directions).

The requirement is to highlight the view / edit forms using (if you can say) blue if the end user was the last to update a specific field or red if the administrator needs to “blame”.

I am looking for an efficient and consistent method to implement this.

So far I have the following options:

• For each entry in table T, add another (char (1)) that says “U if the end user inserted / updated the field or“ A ”if the administrator did this. When the view / edit form is displayed, use this information, to highlight each field accordingly.

• Create a new table H that stores the change history containing something like user_id, field_name, last_update_user_id. Keep table H up to date when fields are updated in the main table T. When a view / edit form is displayed, use this information to highlight each form field accordingly.

What are the pros and cons of these options; can you offer others?

Answer 1

I suppose it just depends on how you look in the future.

Your first approach has the advantage that it is very simple to implement, very simple to update and use, and only slightly increase storage requirements, but it is also an extremely minimal amount of information, rewriting.

, " " , - . , .

, , . , . , - , , . - , .

, , . , , . , , , , , , , /. .

Answer 2

, , ,/ c. , . , . .

Answer 3

- . , . , , , . - , . , , , . , - , , , , ? , , - .