Best practice – logging events (general) and changes (database)

1. Logging database changes as far as inserts/deletes/updates, as far as best practices go, is usually done by a trigger on the main table writing entries into a audit table (one audit table per real table, with identical columsn + when/what/who columns).

2. The list of events as a generic list doesn’t exist. It’s really a function of your application/framework/environment/business needs. As far as best practices, it’s a good idea to decide if your event type list is 100% flat, a 2-level hierarchy (type/subtype – this is usually the best approach) or an N-level hierarchy (much harder/less efficient to implement but incredibly flexible and offers very nice possibilities for proper enterprise event management – I had participated in implementation of all 3 schemes, so I speak from practice BTW).

3. You don’t need 7 generic int fields in 1 table to store event details. Instead go for tag-value-pair table:

   EVENT_TYPES: (event_type, event_subtype, description, subtype_attr1, ...)
   EVENTS: (event_id, event_type, event_subtype, timestamp, attrib1, ...)
   EVENT_DETAILS: (event_id, tag, int_value, varchar_value, float_value).
   

   EVENT_DETAILS can be normalized into EVENT_DETAILS_INT, EVENT_DETAILS_VARCHAR, EVENT_DETAILS_FLOAT, … if you wish but not really required.

   attrib1-atttribN in EVENTS table are generic attributes that apply to all/most events, such as userid, hostname, pid, etc…

   EVENT_TYPES is a table describing assorted event types/subtypes.

   Depending on how you decided bullet point #2, this table may store flat list of types, a list of type/subtype mappings as in my example, or a hierarchy of parent type/child type (you will need 2 tables for that, one for parent/child mapping of types and one for type attributes of each type).

   You may want to have another auxiliary table EVENT_TYPE_ATTRIBUTES mapping event types to valid tags for EVENT_DETAILS.

EXAMPLE:

event: [username] clicked result [result number] [result id] after searching for [search string] and got [number of results] @datetime

This would result in data similar to this (not actual SQL syntax, sue me :):

EVENT_TYPES: (USER_ACTION, USER_CLICK, "User clicked something")
EVENTS: (12345, "USER_ACTION","USER_CLICK", @datetime, "[username]", 
         "app_name", "pid"...) 
EVENT_DETAILS: several rows:
 (12345, "result_number", 33, NULL, NULL) // Or go into EVENT_DETAILS_INT without NULLs? 
 (12345, "result_id", 919292, NULL, NULL)  
 (12345, "search_string", NULL, "how do I log events in DB", NULL)