优雅的模式来记录用户的日志行动

发布于 2024-11-16 21:07:45 字数 687 浏览 3 评论 0原文

我有一个数据库模式来记录用户在我的 web 应用程序中执行的操作：

Log
---
Id
Log_Type_Id
Performed_by_Person_Id
Performed_to_Person_Id
Comment_Id
Story_Id
Photo_Id
etc_Id

Person_Log
----------
Person_Id
Log_Id

这样我可以通知用户日志中的条目以及有关到底发生了什么的详细信息。问题是日志表必须包含每种可能的用户操作类型（他们修改故事或评论，创建故事或评论或照片，更新个人资料等）。对于每个条目来说，几乎所有这些字段都必须为空。

理想情况下，我有整个日志引用的单独日志表，可能类似于：

Log
---
Id
Performed_by_Person_Id

Log_Comment
-----------
Id
Log_Id
Comment_Id

Log_Photo
---------
Id
Log_Id
Photo_Id

Person_Log
----------
Person_Id
Log_Id

问题是我没有一种简单的方法来通知用户与他们相关的事情。我很容易获得它们的日志条目，但随后我必须查询每个“子”表以查看具体信息...我可以将子日志表的名称存储在 Log 中，但这看起来很不优雅。是否有更好、更相关的方法来执行此操作，并且也适用于 ORM 系统？

原文

I have a database schema to log operations users perform in my webapp:

Log
---
Id
Log_Type_Id
Performed_by_Person_Id
Performed_to_Person_Id
Comment_Id
Story_Id
Photo_Id
etc_Id

Person_Log
----------
Person_Id
Log_Id

This way I can notify users of entries in their log with details about what exactly happened. The problem is the Log table has to contain every possible type of user operation (they modified a story or comment, created a story or comment or photo, updated a profile, etc). And almost all of those fields will necessarily be null for each entry.

Ideally I have individual Log tables that the overall Log refers to, maybe something like:

Log
---
Id
Performed_by_Person_Id

Log_Comment
-----------
Id
Log_Id
Comment_Id

Log_Photo
---------
Id
Log_Id
Photo_Id

Person_Log
----------
Person_Id
Log_Id

The problem there is that I then don't have an easy way to notify users of things going on pertaining to them. I easily get the log entry for them, but then I have to query each "child" table to see the specifics... I can store the name of the child Log table in Log, but that seems so inelegant. Is there a better, more relational, way of doing this that also works well with ORM systems?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一直在等你来 2024-11-23 21:07:46

我确实推荐您描述的第二种设计。如果要获取每个日志子类型表的所有列，可以使用 LEFT OUTER JOIN：

SELECT *
FROM Person_Log AS p
INNER JOIN Log AS l ON p.Log_ID = l.Log_ID
LEFT OUTER JOIN Log_Comment AS lc ON l.Log_Type = 'C' AND l.Log_ID = lc.Log_ID
WHERE p.Person_ID = 1234;

在一个查询中对所有日志类型执行此操作不是一个好主意，因为如果给定的日志条目有多个日志条目类型，它会产生笛卡尔积。因此，对每个日志子类型进行单独的查询。

您还可以使用约束，以便确保所有表中只有一个子类型行引用日志中的给定行：

Log
---
Log_Id
Log_Type constrained to ('C', 'P', etc.)
Performed_by_Person_Id
UNIQUE KEY (Log_Id,Log_Type)

Log_Comment
-----------
Log_Id PRIMARY KEY
Log_Type constrained to only 'C'
Comment_Id
FOREIGN KEY (Log_Id,Log_Type) REFERENCES Log(Log_Id,Log_Type)

Log_Photo
---------
Log_Id PRIMARY KEY
Log_Type constrained to only 'P'
Photo_Id
FOREIGN KEY (Log_Id,Log_Type) REFERENCES Log(Log_Id,Log_Type)

回复您的评论：

这与@Walter Mitty 的gen-spec设计基本相同提到。

它还与 Martin Fowler 模式类表继承相关。

如果您希望使用引用完整性来确保只有一个子表的行引用 Log 中的给定行，则每个子表中额外的 Log_Type 列是必需的。

I do recommend the second design you describe. If you want to get all the columns of each log subtype table, you can use a LEFT OUTER JOIN:

SELECT *
FROM Person_Log AS p
INNER JOIN Log AS l ON p.Log_ID = l.Log_ID
LEFT OUTER JOIN Log_Comment AS lc ON l.Log_Type = 'C' AND l.Log_ID = lc.Log_ID
WHERE p.Person_ID = 1234;

It's not a good idea to do this for all log types in one query, because if you have more than one log entry of a given type, it causes a cartesian product. So do a separate query per log subtype.

You can also use constraints so you're sure only one subtype row across all tables references a given row in Log:

Log
---
Log_Id
Log_Type constrained to ('C', 'P', etc.)
Performed_by_Person_Id
UNIQUE KEY (Log_Id,Log_Type)

Log_Comment
-----------
Log_Id PRIMARY KEY
Log_Type constrained to only 'C'
Comment_Id
FOREIGN KEY (Log_Id,Log_Type) REFERENCES Log(Log_Id,Log_Type)

Log_Photo
---------
Log_Id PRIMARY KEY
Log_Type constrained to only 'P'
Photo_Id
FOREIGN KEY (Log_Id,Log_Type) REFERENCES Log(Log_Id,Log_Type)

Re your comment:

This is basically the same as the gen-spec design that @Walter Mitty mentions.

It's also related to a Martin Fowler pattern, Class Table Inheritance.

The extra Log_Type column in each child table is necessary if you want to use referential integrity to ensure only one child table's row references a given row in Log.

回复收藏 0 原文

长亭外，古道边 2024-11-23 21:07:46

我只会有一个包含受影响人员的日志表、一个 actionID 列和 item_id。

然后在您的前端，您可以根据actionID 显示通知，例如actionID 1 可以是照片。所以 item_id 就是 photoID

回复收藏 0 原文

夏日浅笑〃 2024-11-23 21:07:45

您的案例看起来像是 Gen-Spec 设计模式的一个实例。 Gen-spec 通过超类-子类层次结构为面向对象程序员所熟悉。不幸的是，关系数据库设计的介绍往往会跳过如何为 Gen-Spec 情况设计表。幸运的是，它很好理解。谷歌搜索“关系数据库泛化专业化”将产生几篇关于该主题的文章。或者您可以查看以下之前的讨论。

诀窍在于子类（专用）表的 PK 的分配方式。它不是由某种自动编号功能生成的。相反，它是超类（通用）表中 PK 的副本，因此是对其的 FK 引用。

因此，如果情况是车辆、卡车和轿车，则每辆卡车或轿车都会在车辆表中拥有一个条目，卡车也会在卡车表中拥有一个条目，其 PK 是车辆表中相应 PK 的副本。对于轿车来说也是如此。只需进行联接即可轻松确定车辆是卡车还是轿车，并且您通常希望在此类查询中联接数据。

回复收藏 0 原文

可是我不能没有你 2024-11-23 21:07:45

这将是引入通用数据模型的好时机，或者至少是跨数据模型的通用类型系统。这个概念是，一切都有一个条目，甚至是动作、人员、页面、流程等等。当它到位时，您需要一种通用方法来在这些实体之间创建任意关系，从而使它们之间的链接相当容易。您的问题是为什么我提倡更通用的数据模型而不是我们通常使用的超标准化数据模型的例子之一。

我最常用的模型是主题地图（尽管该信息可能不是最容易使用的）为了理解我在说什么），这里不是为每个实体都有一个表，而是一个包含所有实体的表，以及一些额外的表来处理它们之间的典型化和关系。您不必一直这样做，但也许可以专门将其用于您的用例。这是我大约 10 年前写的一篇关于它的文章，以及 Marc de Graauw 的另一篇文章处理特定的 RDBMS 视图，如下出色地。

回到你的问题。使用主题图的示例首先需要表格；

Topic
-----
id
name
type
meta_date_created
meta_date_created_topic_ref
meta_date_updated
meta_date_updated_topic_ref
meta_date_deleted
meta_date_deleted_topic_ref

Assoc (relationship)
--------------------
id
type

Assoc member
------------
id
topic_ref
role_topic_ref

这将为您提供基础知识（但如果您想全面了解，还有大量的东西需要扩展和实现，例如对多种类型的支持、持久识别、本体分组等等，这也是主题图的一部分），以及如果这确实是您想要的，则为您提供 meta_* 字段作为方便的快捷方式（它们有利于快速搜索:)。

每个人都会在“主题”中拥有一个条目，例如；

id: 4572349857
name: Alexander Johannesen
type: 12341234
meta_date_create: {date}
meta_date_create_topic_ref: 5656

为了找出谁创建了该用户，请在“主题”中查找 id“5656”；

id: 5656
name: Billy Bob
type: 12341234

那是什么类型的呢？在“主题”中查找 id“12341234”；

id: 12341234
name: Person

这里的概念基础是系统中的每个“事物”（故意模糊；它可以是您想谈论的任何事物）都有一个条目，包括 actions ；

id: 34598067
name: Add new user
type: 56987  // another topic called 'Action', for example)

通过所有这些，您的日志基本上是通过“Assoc”表在这些实体之间创建关系；

id: 45673
type: 45685678

这就是协会本身。 “id”是什么，并不重要，但类型是（你猜对了）“主题”表中的另一个实体；

id: 45685678
name: Did action

现在，您可以使用记录操作的详细信息填充“Assoc member”表；

id: {whatever}
topic_ref: 5656
role_topic_ref: 12341234

第一个成员是扮演“Person”角色的比利·鲍勃（Billy Bob）。下一个 ;

id: {whatever}
topic_ref: 34598067
role_topic_ref: 56987

在这里，主题“添加新用户”扮演“操作”的角色。您可以将这种关联扩展到您认为需要的任意数量，例如添加预状态、操作的结果、到目前为止的尝试次数、操作发生的位置（例如，如果它是人们可以执行的功能）许多页），等等。为主题表中的那些事物创建实体，为其关系创建实体，您可以根据需要使其复杂化。

所有这些乍一看可能有点刺耳，但它非常灵活，并且您根本不必为将来的扩展而更改数据模型。我使用这个模型构建系统已经很多年了，我对它只有赞扬。如果您想走这条路，主题属性的单独表将遵循协会成员的模型。

人们也许可以为这样的较少表的性能提供理由，但根据我的经验，大多数 RDBMS 都擅长使用内部联接，这是完成这项工作所需的基本工具（所有作为标识符的字段都是明显的索引候选者），并且好处是，这也与 NoSQL 思维方式基本兼容，在您和您的数据、SQL 和后端想要使用的技术机制之间创建足够的抽象。

This would be a good point to introduce a generic data model, or perhaps at least a generic type system across your data model. The concept is that everything has an entry, even actions, people, pages, processes and so forth. When that's in place, you need a generic way of creating arbitrary relations between these entities making it linking between them is fairly easy. Your question is one of those example of why I promote a more generic data model rather than the super-normalized ones we usually use.

The model I use the most is Topic Maps (even though that information may not be the easiest to use to understand what I'm talking about), where instead of having a table for each entity, there's one that holds all, and a few extra to deal with typification and relationships between them. You don't have to go all the way with this, but perhaps use it for your use case specifically. Here's an article I wrote about it almost 10 years ago, and another one by Marc de Graauw that deals with a specific RDBMS view on it, as well.

Back to your question. An example using Topic Maps needs first the tables ;

Topic
-----
id
name
type
meta_date_created
meta_date_created_topic_ref
meta_date_updated
meta_date_updated_topic_ref
meta_date_deleted
meta_date_deleted_topic_ref

Assoc (relationship)
--------------------
id
type

Assoc member
------------
id
topic_ref
role_topic_ref

This will give you the basics (but there's tons of stuff to extend and implement if you want to go full monty, like support for multiple types, persistent identification, ontology grouping, and on and on which is also part of Topic Maps), and give you the meta_* fields as handy short-cuts if that's really all you want (they're good for fast searching :).

Each person will have an entry in 'Topic', example ;

id: 4572349857
name: Alexander Johannesen
type: 12341234
meta_date_create: {date}
meta_date_create_topic_ref: 5656

In order to find out who created this user, look in 'Topic' for id '5656' ;

id: 5656
name: Billy Bob
type: 12341234

What's that type, though? Look in 'Topic' for id '12341234' ;

id: 12341234
name: Person

The conceptual underpinning here is that each 'thing' (deliberately vague; it could be anything you want to talk about) in your system gets an entry, including actions ;

id: 34598067
name: Add new user
type: 56987  // another topic called 'Action', for example)

By all this your log is basically creating relationships between these entities through the 'Assoc' table ;

id: 45673
type: 45685678

That's the association itself. The 'id' is whatever, not important, but the type is (you guessed it) another entity in the 'Topic' table ;

id: 45685678
name: Did action

Now you fill the 'Assoc member' table with the details of logging the action ;

id: {whatever}
topic_ref: 5656
role_topic_ref: 12341234

First member is Billy Bob, who plays the role of 'Person'. Next ;

id: {whatever}
topic_ref: 34598067
role_topic_ref: 56987

Here, the topic 'Add new user' plays the role of 'Action'. You can extend this association with as many items you feel you need, like add in pre-state, the result of the action, number of tries so far, where the action was taking place (for example if its a function people can do on a number of pages), and on and on. Create entities for those things in the Topic table, create entities for their relationships, and you can make this as complex as you want.

All of this may seem a bit jarring at first, but it is incredibly flexible, and you don't have to change your data model at all for future extensions. I've built systems using this model for many years, and I have nothing but praise for it. A separate table for topic properties will follow the model for association members if you want to go down that path.

One could perhaps make a case for the performance of less tables like this, but in my experience most RDBMS are brilliant with inner joins which is the basic tool you need for making this work (all fields that are identifiers are obvious index candidates), and the good thing is that this is also mostly compatible with NoSQL means of thinking, creating a sufficient abstraction between you and your data, and SQL and the technical mechanics the back-end wants to use.

回复收藏 0 原文

~没有更多了~