1

I am developing a web application with mysql, and I have several tables including user and article.

As I would like to record the user that has updated the selected article, I thought about three possibilities:

  • Add two columns to the table article: ART_USE_ID (to know who has changed) and ART_date (to save the datetime).
  • Create a third table changes with CHA_USE_ID, CHA_ART_ID and CHA_date, which would have an entry every time a user changes the table.
  • Make a combination of both: create a table changes but keep a ART_date to retrieve more quickly the date of the last change.

In my opinion, the third one is redundant, and the first one badly conceptualised (and only the last change is kept). Even though the second one appears to be good for only one table (article), I am afraid it gets more complicated and very slow if changes must be saved for more than 30 tables -- that means the changes table would have 30 foreign key(?!)

What is the best way? What is your opinion about it?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Alex T.
  • 33
  • 1
  • 7
  • Option 2b; a separate "history" table for each table you need to track such data for; rather than one catch all table with optional references to numerous tables. – Uueerdo Jul 07 '17 at 21:11

2 Answers2

1

MySQL has a feature called the Audit Log for this purpose. The idea is that the "audit" which is a series of events for data changes, is written to a log file outside the database. That way you can inspect the log for changes, you can see multiple changes to the same data, you can expire the logs, and so on.

The audit log requires a plugin implementation to do its job. MySQL makes an enterprise audit plugin, but it's not free—you have to be a paying customer to get MySQL Enterprise.

There are a few free alternatives for audit log plugin implementations, too.

You might like to try the Percona Audit Log Plugin. This is an optional plugin that is shipped with Percona Server (a branch of MySQL Community Edition). You just have to enable the plugin.

Documentation for installation and configuration: https://www.percona.com/doc/percona-server/5.7/management/audit_log_plugin.html

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

Do you really want to know each person who modified the article?, if the answer is yes, you must use the second one, i think the best option is the first (taking in count speed, time) and i think you only need to know the last one who modified the article.

Jovani Martinez
  • 79
  • 6