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?