I'm faced with a small challenge when designing a system events table. Take the following design:
create table user (
id integer,
username varchar(255),
email varchar(255),
password varchar(32)
);
create table post (
id integer,
title varchar(255),
content text
);
create table video (
id integer,
title varchar(255),
s3_url varchar(255)
);
create table image (
id integer,
title varchar(255),
s3_url varchar(255)
);
I want to be able to keep track of dynamic events that happen during the application usage. For example:
create table event (
user integer references user(id),
action varchar(255),
target_table varchar(255),
target_id integer,
created_at timestamp not null default now()
);
insert into event(1, "viewed", "video", 42)
insert into event(1, "liked", "video", 42)
insert into event(1, "bought", "video", 42)
insert into event(1, "bought", "image", 22)
Is that a valid design? Or is there a better way of doing this?
Best Answer
No, for three reasons:
actions
are the same (or will remain the same) for all entitiesThe easiest solution is to have individual event tables for each entity and individual actions for each entity. So
ImageAction
,ImageEvent
, etc.Another option would be a
UserEvent
entity where you have a subtype for each entity,PostEvent
,VideoEvent
, etc.There's a little more work to be done creating triggers to enforce the exclusive subtypes (here this discriminator is
EventEntityTypeCd
, but you should get the general idea.A few other notes:
Id
columns should be named for the context within they exist.UserId
,PostId
, etc.Id
just leads to trouble down the line.Ids
supplant existing keys, they are not keys. You will need unique constraints on your tables to maintain data quality.