Schema design for user activity logging

Question

I'm designing a schema to support logging of user activity, where users must be able to search:

• Across all events (events of any type), with datetime range, by username;
• Across events of one type, with same as above and additionally with parameters of that module.

I created this schema:

And designed queries to search:

• across all events:

  SELECT extract(epoch from log_time) * 1000,
    u.username,
    CASE WHEN l.event_type IN (0, 2) THEN e.template
      WHEN l.event_type = 1 THEN format(e.template, ecs.field)
      WHEN l.event_type = 3 THEN format(e.template, ess.field)
      WHEN l.event_type = 4 THEN format(e.template, ect.ip, ect.port)
    END
  FROM logs l
  JOIN users u ON u.id = l.user_id
  JOIN event_def e ON e.id = l.event_type
  LEFT JOIN event_client_search ecs ON ecs.log_id = l.id
  LEFT JOIN event_switch_search ess ON ess.log_id = l.id
  LEFT JOIN event_cable_test ect ON ect.log_id = l.id
  

• across same-type events:

  SELECT extract(epoch from log_time) * 1000,
      u.username,
      format(e.template, ect.ip, ect.port)
  FROM logs l
  JOIN users u ON u.id = l.user_id
  JOIN event_def e ON e.id = l.event_type
  JOIN event_cable_test ect ON ect.log_id = l.id
  

This is what the event_def table looks like:

 id |                template
----+----------------------------------------
  0 | Logged in
  2 | Logged out
  1 | Searched in clients %s
  3 | Searched in switches %s
  4 | Cable test switch %s port %s

But what I didn't like, when I have about 100 events (now I have about 50, but haven't implemented them yet), it is going to be a performance issue.

So I thought to merge events with same parameters, like this:

I don't think that will help a lot (it'll cut the number of events by half at most), maybe I'm thinking in the wrong direction?

Answer 1

One of the assumptions that I would ask you to reconsider is whether you really want to have different tables for each event type, or if rather these event types are rows in a table instead of distinct tables.

If you had an EVENT_TYPE table containing your list of event types (about 100 of these) you could take the columns from your current various event tables and make them rows in an EVENT_PARAMETER table.

This type of design would turn your schema changes into data changes when you add new event types. That would save you code maintenance issues and also avoid making your queries more complex and potentially slower.

The instances of events would similarly be kept in two tables, for example a LOG table and a LOG_DETAIL table.

LOG would be an intersection between user and event type.

LOG_DETAIL would be an intersection between log and event parameter.

Your data model might look something like this:

Note that one objection some people may have to this approach is that you end up saving the log parameter values in a string format, rather than in a native format. This is clearly a trade-off. You have to ask yourself is it a good trade-off for your situation.

Answer 2

In case you're open for non-relational approach, you may end up with a more clean schema:

table event_log
id | occurred_at         | event_type      | attributes
1  | 2016-01-01 00:00:00 | user.registered | {"user.id": 1, "user.role": "admin"}}
2  | 2016-01-01 00:01:00 | user.exploded   | {"user.id": 1}

table attribute_search_index
event_id | attribute_name | attribute_value
1        | user.id        | 1
1        | user.role      | admin
2        | user.id        | 1

table event_format (optional)
event_type      | format
user.registered | User {user.id} has registered with role {user.role}
user.exploded   | User {user.id} has exploded

Such schema will allow you to search events at a cost of one extra join for every parameter:

 SELECT e.* FROM event_log AS e
   INNER JOIN attribute_search_index AS p1 
     ON p1.event_id = e.id 
       AND p1.attribute_name = 'user.id' 
       AND p1.attribute_value = '1'
   INNER JOIN attribute_search_index AS p2 
     ON p2.event_id = e.id 
       AND p2.attribute_name = 'user.role' 
       AND p2.attribute_value = 'admin'
 WHERE e.type = 'user.registered'

This, however, introduces some caveats as well:

• It is not clear if JSON attributes field can be bounded and, if it'll end up outside of the row, it may impact performance (however, chances are that you won't need more than 256 characters for this field)
• While nested attributes are easily flattened into x.y.z notation, array handling is something unclear. However again, you may simply flatten it into set of records for search index, i.e. if user.role is [developer, engineer], you may create two records for search index, (<id>, 'user.role', 'developer') and (<id>, 'user.role', 'engineer').
• Integers, floats, booleans, nulls are treated as strings. This is not an issue in most cases.

The third table is copying event_def, but it is totally useless in reality. Chances are that if logs will be looked over by an engineer, he won't need formatting at all, and if they will be presented to end user, they will come through an application, and it is much much easier to compile formatting definitions straight into application.

Answer 3

Can you explore a graph database for it? Every user is an entity whose activity is a series of actions [connected node], can even maintain backtracking.

Even you can maintain the chronology easily.

Good read: https://snowplowanalytics.com/blog/2014/07/31/using-graph-databases-to-perform-pathing-analysis-initial-experimentation-with-neo4j/