优雅的模式来记录用户的操作 [英] Elegant schema to log users' actions

问题描述

我有一个数据库模式来记录用户在我的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 
  

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

理想情况下，我有一个Log Log表示整个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 
  

问题是，我没有一个简单的方法通知用户有关他们的事情。我很容易得到他们的日志条目，但是我必须查询每个子表来查看具体细节...我可以将日志表的名称存储在日志中，但是这似乎是不合适的。有没有更好的，更关系的方法，这也可以与ORM系统一起使用？

解决方案

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

这个技巧是分配给子类（专用）表的PK的方式。它不是由某种自动编号功能生成的。相反，它是超类（广义）表中的PK的副本，因此是对其的FK引用。因此，如果是车辆，卡车和轿车，则每辆卡车或轿车将在车辆台面上进入，卡车也将在卡车台上进入，其中PK是车辆表中相应的PK的副本。同样的轿车。无论如何，通过简单的连接来弄清楚车辆是卡车还是轿车，通常您也想加入这种查询中的数据。

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?

解决方案

Your case looks like an instance of the Gen-Spec design pattern. Gen-spec is familiar to object oriented programmers through the superclass-subclass hierarchy. Unfortunately, introductions to relational database design tend to skip over how to design tables for the Gen-Spec situation. Fortunately, it’s well understood. A google search on "Relational database generalization specialization" will yield several articles on the subject. Or you can look at the following previous discussion.

The trick is in the way the PK for the subclass (specialized) tables gets assigned. It’s not generated by some sort of autonumber feature. Instead, it’s a copy of the PK in the superclass (generalized) table, and is therefore an FK reference to it.

Thus, if the case were vehicles, trucks and sedans, every truck or sedan would have an entry in the vehicles table, trucks would also have an entry in the trucks table, with a PK that’s a copy of the corresponding PK in the vehicles table. Similarly for sedans. It’s easy to figure out whether a vehicle is a truck or a sedan by just doing joins, and you usually want to join the data in that kind of query anyway.

这篇关于优雅的模式来记录用户的操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！