超级多态事件模式(Super polymorphic event schema)

超级多态事件模式(Super polymorphic event schema)

我希望能够跟踪用户在我的网站上执行的每个操作。

动作可以源自visitor或user （两者都是人）。

动作可以影响主题（ visitor或user ）

一个动作可以有一个object ，可以是任何其他数据库表

一些例子：

User A (actor)将User B (subject) User A (actor)分配给conversation (object)

User A (actor)创建team (object)

User B (actor)将Visitor (subject) User B (actor)移动到group C (object)

在我的应用程序中，我想要所有事件的提要，并且对于每个事件，准确显示它引用的actor，subject（如果有）和对象

我在想类似的东西

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 )

除了这些表之外， roles每个记录都有一个相应的独立表，用外键保存与该对象相关的所有数据。

我希望得到一些关于这种结构的反馈，如果它是可扩展的，或者也许，有更好的方法来实现这一目标？

感谢Daniel A. Thompson （ Daniel A. Thompson推动我走向这个方向

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

最满意答案

根据您的要求，我建议以下架构：

-- 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 )

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 )