mysql - 数据库: Making a Log of actions,如何处理各种引用？

Question

希望大家新年快乐。

所以，我的问题是，制作操作日志的最佳方式是什么。让我用一个例子来解释它，假设我们有这些实体:

用户

friend (用户是另一个用户的 friend ，多对多关系)

消息(一个用户可以向另一个用户发送消息)

Group(一个用户可以在不同的组中)

游戏(一个游戏可以和不同的玩家一起玩，有一些信息，比如游戏日期。这会产生两个故事，games 和 games_users，后者存储用户和游戏之间的关系)

现在，我想做一个日志，例如:

1. 用户 A(用户链接)交了新 friend 用户 B(用户链接)

2. 用户 A(链接到用户)、B(链接到用户)和 C(链接到用户)玩了一个游戏(链接到游戏)

3. 用户 C(链接到用户)加入了组 D(链接到组)

因此，我想制作一个灵活的日志，它可以存储任意数量的引用以及对不同实体(例如用户和游戏)的引用。

我知道有两种方法，但它们都有一个或多个问题:

1. 记录一个 Action 时，我直接存储我想要的纯文本(即:只有 1 个字符字段，它将存储“用户 C 加入了一个组”)。但是，这种方式存在一个问题，该文本需要翻译成其他语言，我不能为每种语言设置一个字段。

2. 有一个主表log，其中每一行代表一个日志操作和一个代码，所以我知道那是哪个操作，即:一个用户加入了一个组，x 个用户玩了一个游戏.然后我为每个需要的外键类型创建了另一个表，所以我有 log_user、log_group 和 log_game 例如，log_user，其中一个字段引用 log，另一个字段引用 user。这样我就可以让多个用户执行同一个日志操作。问题:相当复杂，并且可能会导致大量开销，这取决于我必须查询多个表的日志操作。这是否正确，会不会太占用 CPU 资源？

因此，我乐于接受新想法和集思广益。解决此类问题的最佳方法是什么？在此先感谢，我希望我已经清楚地解释了它。如有任何问题，请提问。

编辑:我决定开始悬赏，因为我对收到的答案不是很满意。如果需要，将作出任何澄清。谢谢

我想要与 facebook/orkut/社交网络“ friend 更新”非常相似的东西。这将显示给用户。

Answer 1

下面是我会怎么做。在您看到架构后，我在底部有更多评论。

日志

LogID - 唯一的日志 ID

Time - 事件的日期/时间

日志类型 - 字符串或 ID

(旁注，我会在这里使用一个 id，这样你就可以使用下面显示的消息表，但是如果你想要快速 n dirty，你可以只为每个日志时间使用一个唯一的字符串(例如“游戏开始”，“消息已发送”等)

LogActor

LogID - 外键

LogActorType - 字符串或 ID(如上所述，如果是 ID，您将需要一个查找表)

LogActorID - 这是表中类型的唯一 ID，例如用户、组、游戏

序列 - 这是 Actor 的顺序。

日志消息

LogType - 外部 key

消息 - 长字符串 (varchar(max)?)

Language - string(5) 这样你就可以关闭不同的语言，例如“US-en”

示例数据(使用你的 3 个例子)

日志

ID  Time   LogType 
1   1/1/10 1
2   1/1/10 2
3   1/1/10 3

日志Actor

LogID LogActorType LogActorID Sequence
1     User         1          1
1     User         2          2
2     User         1          1
2     User         2          2
2     User         2          3
2     Game         1          4
3     User         3          1
3     Group        1          2

日志消息

LogType Message 
1       {0} Made a new friend {1}
2       {0}, {1}, {2} played a game ({3})
3       {0} joined a group ({1})

用户

ID Name
1  User A
2  User B
3  User C

游戏

ID Name
1  Name of game

组

ID Name
1  Name of group

下面是这个设计的优点。

• 很容易扩展

• 它处理多语言问题独立于 Actor

• 它是 self 记录的，LogMessage 表解释准确您存储的数据应该是什么说。

关于它的一些坏处。

• 您必须进行一些复杂的处理才能读取消息。

• 您不能只查看数据库就知道发生了什么。

根据我的经验，这种设计的好处多于坏处。为了让我快速查看日志，我做了一个 View (我不将其用于应用程序代码)，当我需要通过后台查看发生了什么时，我可以查看该 View 结束。

如果您有任何问题，请告诉我。

更新 - 一些示例查询

我的所有示例都在 sqlserver 2005+ 中，如果您希望我针对其他版本，请告诉我。

查看LogActor表(有很多方法可以做到这一点，最好的方法取决于很多事情，包括数据分布、用例等)这里有两个:

一)

SELECT 
  LogId,
  COLLESCE(U.Name,Ga.Name,Go.Name) AS Name,
  Sequence
FROM LogActor A
LEFT JOIN User U ON A.LogActorID = U.[ID] AND LogActorType = "User"
LEFT JOIN Game Ga ON A.LogActorID = Ga.[ID] AND LogActorType = "Game"
LEFT JOIN Group Go ON A.LogActorID = Go.[ID] AND LogActorType = "Group"
ORDER BY LogID, Sequence

二)

SELECT 
  LogId,
  U.Name AS Name,
  Sequence
FROM LogActor A
INNER JOIN User U ON A.LogActorID = U.[ID] AND LogActorType = "User"
UNION ALL
SELECT 
  LogId,
  Ga.Name AS Name,
  Sequence
FROM LogActor A
INNER JOIN Game Ga ON A.LogActorID = Ga.[ID] AND LogActorType = "Game"
UNION ALL
SELECT 
  LogId,
  Go.Name AS Name,
  Sequence
FROM LogActor A
INNER JOIN Group Go ON A.LogActorID = Go.[ID] AND LogActorType = "Group"
ORDER BY LogID, Sequence

总的来说，我认为 a) 比 b) 好，例如，如果您缺少一个 actor，则类型 a) 将包含它(使用空名称)。但是 b) 更易于维护(因为 UNION ALL 语句使其更加模块化。)还有其他方法可以做到这一点(例如 CTE、 View 等)。我倾向于像 b) 那样做，从我所看到的情况来看，如果不是最佳实践，这似乎至少是标准实践。

因此，日志中的最后 10 个项目看起来像这样:

SELECT 
  LogId,
  M.Message,
  COLLESCE(U.Name,Ga.Name,Go.Name) AS Name,
  Time,
  A.Sequence
FROM Log
LEFT JOIN LogActor A ON Log.LogID = A.LogID
LEFT JOIN User U ON A.LogActorID = U.[ID] AND LogActorType = "User"
LEFT JOIN Game Ga ON A.LogActorID = Ga.[ID] AND LogActorType = "Game"
LEFT JOIN Group Go ON A.LogActorID = Go.[ID] AND LogActorType = "Group"
LEFT JOIN LogMessage M ON Log.LogType = M.LogMessage
WHERE LogID IN (SELECT Top 10 LogID FROM Log ORDER BY Date DESC)
ORDER BY Date, LogID, A.Sequence

注意 - 如您所见，选择一个日期的所有日志项比选择最后一个 X 更容易，因为为此我们需要一个(可能非常快的)子查询。