How to store (in MySQL) multiple updates in one "activity" of an activity log?

Question

What I'm doing > I am designing a table in my MySQL database that stores the activity of users in my web app. Activities are things like:

• Logging in
• Adding a record
• Editing a record (with multiple things to edit i.e. title, person, date)
• Deleting a record
• Copying a record
• Email sent
• Email received
• Import from an external resource
• Sync with external resource
• Add a user
• Delete a user
• And so on

The problem > activities like editing a record can have multiple recordings for one update i.e. editing the title, date and person at the same time.

I need > I'd like the flexibility to be able to display descriptions like

John Doe changed the date from 1/1/14 to 2/3/14 and the title from "old title" to "new title"

I've research previous answers but not been able to find a solution that handles this type of activity log. My proposed solution is based on some ideas in this article.

I propose > Here's what I am thinking about building

One table called activity_logs

(int) id
(varchar) type (record, user, etc.)
(int) object_id (record_id, user_id, etc.)
(varchar) display_name (user's name, record title, etc.)
(varchar) verb (delete, add, update, copy, login, logout, etc.)
(text) data (see below)
(datetime) created_at
(int) created_by

In the case of updating a record I would store detail of the activity in the data field in the following JSON as an example:

{
  "date": { "from": "2014-01-01 00:00:00", "to": "2014-03-04 00:00:00"},
  "title": { "from": "old title", "to": "new title"}
}

In the case of display name, if a record is deleted I can still display "John Doe deleted 'name of record'" (as the record won't exist anymore)

My concerns > I'm concerned that if I build it this way it might give me issues down the line when I come to extend it (issues that I am not aware of yet). This is actually a refactor of existing functionality that I've not built right in the first place...

So my questions are > what are the issues with building an activity log this way? And is there a better practice than above?

Answer 1

If you want to treat multiple concurrent actions as a single "activity", you need two tables: action and activity. The activity table would contain the common elements, such as the user and time. The action table would contain fields specific to the action, such as the type of action (delete, update, etc.) and a foreign key to activity.id. You can then use a join to get all the actions associated with a particular activity.

Activity table:

id INT AUTO_INCREMENT
user_id INT Foreign key references Users.id
time DATETIME

Action table could be something like:

id INT AUTO_INCREMENT
activity_id INT Foreign Key references Activity.id
verb VARCHAR
target VARCHAR