Tarlen Tarlen - 4 months ago 14
SQL Question

Super polymorphic event schema

I want to be able to track every action a user takes on my site.

An action can originate from a

visitor
or a
user
(both of which are human).

An action can affect a subject (
visitor
or a
user
)

An action can have an
object
, which can be any of the other database tables

Some examples:

User A (actor)
assigns
User B (subject)
to
conversation (object)


User A (actor)
creates
team (object)


User B (actor)
moved
Visitor (subject)
to
group C (object)


In my application, I want a feed of all events, and for each event, show exactly what actor, subject (if any) and object it refers to

I am thinking something like

create table actors ( -- contains as many rows as there are people
int ID,
)

create table roles ( -- roles like for both human and object roles such as: Visitor, Team, User, Conversation, Group
int ID,
nvarchar(max) Name
)

create table actors_roles ( -- associates people with roles
int Actor_ID, -- FK to actors.ID
int Role_ID -- FK to roles.ID
)

create table objects ( -- associates objects with roles
int ID,
)

create table object_roles ( -- associates objects with roles
int Object_ID, -- FK to object.ID
int Role_ID -- FK to roles.ID
)

create table tEvent (
int ID,
int Type_ID,
int Actor_ID, -- FK to actors.ID
int Subject_ID -- FK to actors.ID
int Object_ID -- FK to objects.ID
)


Besides these tables, every record in
roles
will have a corresponding, separate table maintaining all the data related to the object with a foreign key.

I'd love to get some feedback on this structure and if it is scaleable, or perhaps, there is a better way to accomplish this?

Credit to
Daniel A. Thompson
for pushing me in this direction

Answer

Based on your requirements, I'd propose the following schema:

-- roles for both human and object roles such as:
-- Visitor, Team, User, Conversation, Group
CREATE TABLE tRole ( 
   int ID,
   nvarchar(max) Name
)

-- contains as many rows as there are people
CREATE TABLE tActor ( 
   int ID,
   int Role_ID -- FK to tRole.ID
)

-- contains as many rows as there are objects 
CREATE TABLE tObject ( 
   int ID,
   int Role_ID -- FK to tRole.ID
)

CREATE TABLE tEvent (
   int ID,
   int Type_ID,
   int Actor_ID, -- FK to tActor.ID
   int Subject_ID -- FK to tActor.ID
   int Object_ID -- FK to tObject.ID
)