7

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:

enter image description here

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:

enter image description here

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?

  • 1
    How do you know having 100 events (I guess you mean event definitions) will cause performance issues? – dezso Nov 1 '16 at 9:08
  • @dezso because of this Planning time: 1027.201 ms Execution time: 6.827 ms (415 rows) – How to change username Nov 1 '16 at 9:58
  • 1
    So you mean you'd have that many tables like event_client_search? Otherwise, when you run the same query the second time, does the planning time decrease? – dezso Nov 1 '16 at 10:07
  • @dezso No, each time I run this query I get same time, about 1sec – How to change username Nov 1 '16 at 11:24
  • @dezso I forgot to answer on your first question, yes in future there's would be about 100 event tables, so it's 100 left joins... – How to change username Nov 1 '16 at 12:22
3

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:ERD

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.

| improve this answer | |
  • what tool did you use to draw the graph above? It's just interesting it looks nice ) – Sarvar Nishonboev Jan 25 '18 at 9:26
  • 1
    @SarvarNishonboyev I used Visio with an ERD stencil that I designed myself to use the James Martin crows foot notation, which is the one I prefer. The stencil uses a custom line style to give it a hand-drawn look. I have a similar one which is regular, straight lines. I've found that the hand-drawn look is good for quick sketches and preliminary designs where I want to convey the impression that the design is preliminary. – Joel Brown Jan 25 '18 at 12:11
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.