Recommended model for application level event logging

Question

I am going to be storing application level events (user A added thing, user B updated that etc) for both public and private consumption.

Below is an oversimplified model of the tables and their associations.

users
-------
id (PK)

event_type
-------
id (PK)
description

events
-------
id (PK)
user_id (FK)
event_type_id (FK)
created (timestamp)

The events will be reported in a timeline format (newest events at the top) for each user.

I'd also like to add custom uri parameters and the events context (User A updated "My Visa Card").

Am I overlooking any potential pitfalls in this design?

Answer 1

Your basic model is fine. It gives you a standard, normalized intersection (many to many) of events of specific types associated with particular users. Without knowing more about your intentions or requirements, it's difficult to offer any criticism of the model.

I would make these observations:

1. You can use events.id for your PK on the events table if you like. That is probably the way that I would do it. You have a choice, however, to consider making the PK of event the combination of events.user_id and event_type_id. This will be just as good, possibly slightly better, if you will have no other tables directly referencing the events table. The potential advantage has to be considered in light of how you plan to manage the space for this table and how fast events will accumulate. Using events.id as a clustering index could create a hotspot. On the other hand, using the compound key could create long delays in your inserts if you don't manage your page fills properly.

2. You've mentioned that the model is simplified, but you haven't said in what ways. One thing that I would say is that the model as presented gives very little information about events. You know who did what (and when) - but the what is limited to a static description string. Other event logging systems I have seen (and built) include places to record specific details of the object of the event. If I were you I would ask myself questions like:

   • Would it help if there were a text field on the events table that could be filled with specific details of what happened during the event?

   • Would it help to have columns that record which instance of another item were impacted by the action (i.e.: events.object_type and events.object_id)?

   • Are there complex events that have multiple parts which should be grouped into a single logical event? Would having a field that allows multiple events records to be tied together be helpful?

If you can tell us more about how you plan to use the events, from the perspective of how the information is meant to be helpful, I might be able to give you more concrete advice.

Answer 2

One of the applications I maintain has a similar structure but adds to it by having an application_event_type table

ID,VALUE  
0,CREATE  
1,ASSIGN  
3,UPDATE  
2,DELETE  
4,UNASSIGN  
5,APPROVE  
6,MARK_AS_DUPLICATE  

and the master table looks like this

CREATE TABLE APPLICATION_LOGGING  
(  
  ID                  NUMBER(9)                 NOT NULL,   
  CURRENT_USER_ID     NUMBER(9),  
  EVENT_ID            NUMBER(9)                 NOT NULL,  
  ENTRY_DATE          DATE                      NOT NULL,  
  MESSAGE             VARCHAR2(200 CHAR)        NOT NULL,  
  MESSAGE_PARAMETERS  VARCHAR2(2000 CHAR)       NOT NULL  
)  

I found that logging is something that more people are interested in than I would have expected. Managers want to know who did what. For them, a cryptic log entry was not enough. Message and Message_parameters were lengthy text strings with too much information. I had to make a linking table which condensed the message and message paramters into english or french

CREATE TABLE APPLICATION_LOGGING_NEW
(
  ID                        NUMBER(10)         NOT NULL,  
  LOG_ID                    VARCHAR2(50 BYTE)  NOT NULL,  
  EVENT_ID                  NUMBER(10),   
  MESSAGE                   VARCHAR2(500 BYTE) NOT NULL,  
  LOCALE_ID                 NUMBER(10)         DEFAULT 2    NOT NULL,  
  LOG_TYPE                  NUMBER(10)         DEFAULT 1    NOT NULL,   
  DATE_CREATED              TIMESTAMP(6)       DEFAULT CURRENT_TIMESTAMP NOT NULL,  
  CREATED_BY_USER_ID        NUMBER(9)          DEFAULT 355  NOT NULL,
  DATE_LAST_MODIFIED        TIMESTAMP(6)       DEFAULT CURRENT_TIMESTAMP NOT NULL,
  LAST_MODIFIED_BY_USER_ID  NUMBER(9)           DEFAULT 355 NOT NULL  
)  

Another database I work with has extensive logging. Anytime you "look" at something an insert is done. This seemed like a great feature but the logging table is now three times the size of the top five largest tables. Querying and inserting this table have a definite effect on performance.

Logging is a fine art: too much and you can affect performance, not enough and you cannot answer a reasonable question. It is well worth investing the time to find the right balance.