Design activities / events logging (ex: fields update) in RDBMS

Question

I am trying to implement a activity tracking system in Ruby on Rails for an API only application.

I have a table named letter with information as follow :

• id (primary key)
• sender_id (foreign_key on contact)
• receiver_id (foreign_key on user)
• owner_id (foreign_key on user)
• re (string)
• status (enum { ON_HOLD , CLOSED , OPENED })

Every time an update is done (title, ownership or state changes) I want to log it in an activity table which will be display along side the letter on the application.

It should look like GitHub issues, where we can see who did what. Being able to reference users, link to their profile, says that someone change the title from "something" to "another thing", etc.

The thing I want to log have different information layout :

• transfer changes: from, to (both should be foreign_key)
• metadata changes: field_name, old_value, new_value (strings)
• status changes: old_status, new_status (enum )

All of these tables have id, author of the changes, creation date.

I am wondering what would be the best to keep track of this changes.

First idea

I started to create a activity table as follow :

• id (primary key)
• letter_id (foreign_key on letter)
• created_at (datetime)
• user_id (foreign_key on user)
• type (enum { STATUS , TRANSFER , METADATA })
• activity_id (integer pointing on table id)

And created one table for each enum entry in type column and load them manually using the activity_id. But I found it pretty ugly because:

• I can't use joins properly
• it disturbs Rails ORM capabilities
• Come on, I am pretty sure it's the wrong way for RDBMS (no?)

I would end up with this kind of code :

activity = Activity.find(1)
if (activity.type == "STATUS")
  StatusActivity.find(activity.activity_id)
elsif (activity.type == "TRANSFER")
  TransferActivity.find(activity.activity_id)
...

Second idea

Based on the activity layout of the first idea, instead of having on column with the I thought about having additional nullable columns holding a foreign_key toward each enum entries tables :

• id (primary key)
• letter_id (foreign_key on letter)
• created_at (datetime)
• user_id (foreign_key on user)
• type (enum { STATUS , TRANSFER , METADATA })
• status_activity_id
• transfer_activity_id
• metadata_activity_id

Doesn't look great; if I want to add a new kind of activity event, I will have to create an extra column for each row...

It would end up with this kind of code :

activity = Activity.find(1)
if (activity.type == "STATUS")
  activity.status_activity
elsif (activity.type == "TRANSFER")
  activity.transfer_activity
...

Third idea

Create 3 different tables: status_activity, transfer_activity and metadata_activity. Query them separately, sort them by creation date before serializing into JSON.

Despite all of this ideas, I can't figure out what would be great ? So do you have any idea or advices ?

Answer 1

One easy way to do it is to create a new table called history which has an id, a date, perhaps also a user_id to know who made each modification, and then simply a string which contains JSON text explaining what happened. The idea is that you won't really be using the historical fields in relations, probably not even in queries, so they do not have to be real fields.

Another (rather wasteful but fully relational) way to do it is to replace your letter table with a letter_history table, where you maintain all fields of table plus a date and perhaps also a user_id, so you never actually modify any existing rows, you only add new rows with the current date and the user who made the change. Then, you create a view called letter which simply selects the most recent version of each letter.