A better logging design or some SQL magic?

Question

I'm knee deep in modifying some old logging code that i didn't write and wondering what you think of it. This is an event logger written in PHP with MySQL, that logs message like:

Sarah added a user, slick101
Mike deleted a user, slick101
Bob edited a service, Payment

Broken up like so:

Sarah [user_id] added a user [message], slick101 [reference_id, reference_table_name]

Into a table like this:

log
---
id
user_id
reference_id
reference_table_name
message

Please note that the "Bob" and "Payment" in the above example messages are Id's to other tables, not the actual names. A join is needed to get the names.

It looks like the "reference _ table _ name" is for finding the proper names in the correct table, since only the reference _ id is stored. This would probably be good if somehow i could join on a table name that stored in reference_table_name, like so:

select * from log l
join {{reference_table_name}} r on r.id = l.reference_id

I think I see where he was going with this table layout - how much better to have ids for statistics instead of a storing the entire message in a single column (which would require text parsing). Now I'm wondering..

Is there a better way or is it possible to do the make-believe join somehow?

Cheers

Answer 1

To get the join based on the modelling, you'd be looking at a two stage process:

1. Get the table name from LOG for a particular message

2. Use dynamic SQL by constructing the actual query as a string. IE:

   "SELECT l.* FROM LOG l JOIN "+ tableName +" r ON r.id = l.reference_id"

There's not a lot of value to logged deletions because there's no record to join to in order to see what was deleted.

How much history does the application need?

Do you need to know who did what to a value months/years in the past? If records are required, they should be archived & removed from the table. If you don't need all the history, consider using the following audit columns on each table:

• ENTRY_USERID, NOT NULL
• ENTRY_TIMESTAMP, DATE, NOT NULL
• UPDATE_USERID, NOT NULL
• UPDATE_TIMESTAMP, DATE, NOT NULL

These columns allow you to know who created the record & when, and who last successfully updated it and when. I'd create audit tables on a case by case basis, it just depends on what functionality the user needs.