Design a table with dynamic relationships in PostgreSQL

database-designpostgresql

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:

  1. You cannot have foreign key references to multiple tables
  2. It assumes the actions are the same (or will remain the same) for all entities
  3. It assumes the same key structure for all entities and will not permit any deviation from that

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

CREATE TABLE EventEntityType
(
  EventEntityTypeCd  CHAR(1)      NOT NULL
 ,Name               VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_EventEntityType PRIMARY KEY (EventEntityTypeCd)
 ,CONSTRAINT AK_EventEntityType UNIQUE (Name)
)
;

CREATE TABLE UserAction
(
  ActionCd  CHAR(1)      NOT NULL
 ,Name      VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_UserAction PRIMARY KEY (ActionCd)
 ,CONSTRAINT AK_UserAction UNIQUE (Name)
)
;

CREATE TABLE EventEntityTypeAction
(
  EventEntityTypeCd  CHAR(1)  NOT NULL
 ,ActionCd           CHAR(1)  NOT NULL
 ,CONSTRAINT FK_Action_Valid_For_EventEntityType FOREIGN KEY (EventEntityTypeCd) REFERENCES EventEntityType (EventEntityTypeCd)
 ,CONSTRAINT FK_EventEntityType_Subject_To_Action FOREIGN KEY (ActionCd) REFERENCES UserAction (ActionCd)
 ,CONSTRAINT PK_EventEntityTypeAction PRIMARY KEY (EventEntityTypeCd, ActionCd)
)
;

CREATE TABLE UserEvent
(
  UserId             INT        NOT NULL
 ,EventTs            TIMESTAMP  NOT NULL
 ,EventEntityTypeCd  CHAR(1)    NOT NULL
 ,ActionCd           CHAR(1)    NOT NULL
 ,CONSTRAINT FK_UserEvent_Created_By_User FOREIGN KEY (UserId) REFERENCES User (Id)
 ,CONSTRAINT FK_UserEvent_Is_EventEntityTypeAction FOREIGN KEY (EventEntityTypeCd, ActionCd) REFERENCES EntityEventTypeAction (EventEntityTypeCd, ActionCd)
 ,CONSTRAINT PK_UserEvent PRIMARY KEY (UserId, EventTs)
)
;

CREATE TABLE PostEvent
(
  UserId   INT        NOT NULL
 ,EventTs  TIMESTAMP  NOT NULL
 ,PostId   INT        NOT NULL
 ,CONSTRAINT FK_PostEvent_Is_UserEvent FOREIGN KEY (UserId, EventTs) REFERENCES UserEvent (UserId, EventTs)
 ,CONSTRAINT FK_PostEvent_Applies_To_Post FOREIGN KEY (PostId) REFERENCES Post (Id)
 ,CONSTRAINT PK_PostEvent PRIMARY KEY (UserId, EventTs)
)
;

CREATE TABLE VideoEvent
(
  UserId   INT        NOT NULL
 ,EventTs  TIMESTAMP  NOT NULL
 ,VideoId  INT        NOT NULL
 ,CONSTRAINT FK_VideoEvent_Is_UserEvent FOREIGN KEY (UserId, EventTs) REFERENCES UserEvent (UserId, EventTs)
 ,CONSTRAINT FK_VideoEvent_Applies_To_Video FOREIGN KEY (VideoId) REFERENCES Video (Id)
 ,CONSTRAINT PK_VideoEvent PRIMARY KEY (UserId, EventTs)
)
;

CREATE TABLE ImageEvent
(
  UserId   INT        NOT NULL
 ,EventTs  TIMESTAMP  NOT NULL
 ,ImageId  INT        NOT NULL
 ,CONSTRAINT FK_ImageEvent_Is_UserEvent FOREIGN KEY (UserId, EventTs) REFERENCES UserEvent (UserId, EventTs)
 ,CONSTRAINT FK_ImageEvent_Applies_To_Image FOREIGN KEY (ImageId) REFERENCES Image (Id)
 ,CONSTRAINT PK_ImageEvent PRIMARY KEY (UserId, EventTs)
)
;

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:

  1. Id columns should be named for the context within they exist. UserId, PostId, etc. Id just leads to trouble down the line.
  2. You don't have any actual keys in your current design. Ids supplant existing keys, they are not keys. You will need unique constraints on your tables to maintain data quality.
  3. I hope it's just there as a placeholder, but store passwords with a salt and probably in another table entirely.
Related Question