Recommended model for application level event logging

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?

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:

-
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.

-
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.

StackExchange Database Administrators Q#7622, answer score: 5