数据库：做一个日志的动作，如何处理各种引用？ [英] Databases: Making a Log of actions, how to handle various references?

问题描述

希望你们都有一个新的一年。

所以，我的问题是，什么是最好的方式做日志的行动。让我用一个例子解释一下，假设我们有这些实体：

用户

朋友（用户是另一个用户的朋友，多对多的关系）

消息（用户可以向其他用户发送消息）

c>（用户可以在不同的组中）

游戏有一些信息，比如游戏的日期，这会导致两个故事，游戏和games_users，后者存储用户和游戏之间的关系）

现在，我想创建日志，例如：

1. 用户A（指向用户的链接）创建了一个新朋友， ）




2. 用户A（指向用户的链接），B（指向用户的链接）和C（指向用户的链接） p>




3. 使用者C（连结至使用者）加入群组D（连结至群组）

所以，我想创建一个灵活的日志，它可以存储尽可能多的引用，并且引用不同的实体（例如用户和游戏）。

我知道这样做的两种方法，但它们都有一个或多个问题：

1. 当记录动作时，我直接存储我想要的纯文本（即：只有1个字段，它将存储用户C加入组）。但是，这样有一个问题，这个文本需要翻译成其他语言，我不能为每种语言都有一个字段。




2. 表 log ，其中每行代表一个日志动作和一个代码，所以我知道哪个动作是，即：一个用户加入一个组，x个用户玩游戏。然后我为每个外键类型需要另一个表，所以我会有 log_user ， log_group code> log_game 例如， log_user 的字段引用 log 引用 user 。这样我可以有多个用户进行相同的日志操作。问题：相当复杂，可能导致大量开销，因为根据日志操作，我不得不查询到多个表。这是正确的，是否太CPU密集？

因此，我接受新的想法和头脑风暴。这种问题的最佳方法是什么？
提前感谢，我希望我已经清楚地解释了它。如果有任何问题，请问。

编辑：我决定开始赏金，因为我对我收到的答案不满意。如果需要，将做任何澄清。感谢

我想要一个非常类似于facebook / orkut /社交网络朋友更新的东西。这将显示给用户。

解决方案

以下是我将如何做。
在看到模式后，我在底部有一些注释。

日志

LogID - 唯一的日志ID

时间 - 事件的日期/时间

LogType - 字符串或ID

（侧面注释，我会在这里使用一个id，所以你可以使用下面的消息表，但如果你想快速n脏，你可以每个日志时间只有一个唯一的字符串（例如Game Started，Message Sent等）

LogActor b
$ b

LogID - 外部键

LogActorType - 字符串或ID（如上所述，如果ID需要查找表）

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

LogType - exernal键

p>

消息 - 长字符串（varchar（max）？）

语言 - 字符串例如US-en

示例数据
（使用您的3个示例）

日志

  ID时间LogType 
 1 1/1/10 1 
 2 1/1/10 2 
 3 1/1/10 3 
  

LogActor

  LogID LogActorType LogActorID序列
 1用户1 1 
 1用户2 2 
 2用户1 1 
 2用户2 2 
 2用户2 3 
 2游戏1 4 
 3用户3 1 
 3组1 2 
  

LogMessage

  LogType消息
 1 {0}朋友{1} 
 2 {0}，{1}，{2}玩过一场游戏（{3}）
 3 {0}加入了一个团队（{1}）
  

用户

  
 1用户A 
 2用户B 
 3用户C 
  

游戏

  ID名称
 1游戏名称
  

组

  ID名称
 1组名称
  

这里是关于这个设计的好东西。

• 这是很容易扩展




• 它处理多语言问题
  




• 这是自我记录，
  LogMessage表正确地解释了
  什么是存储数据
  。

有些不好的地方。

• 您必须执行一些复杂的处理才能阅读邮件。




• 您不能只看DB，

根据我的经验，这种设计的好处超过了坏的位。我做了什么，让我做一个快速n脏看看日志是一个视图（我不使用的应用程序代码），我可以看看，当我需要看到通过后面发生了什么

$ b

如果您有任何问题，请通知我。

strong>

我所有的例子都在sqlserver 2005+中，如果有不同的版本你想要我的目标，请告诉我。

查看LogActor表
（有很多方法可以做到这一点，最好取决于很多事情，包括数据分布，用例等）
下面是两个： / p>

a）

  SELECT 
 LogId，
 COLLESCE（U.Name，Ga.Name，Go.Name）AS名称，
序列
 FROM LogActor A 
 LEFT JOIN用户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 =群组
 ORDER BY LogID，序列
  

b）

  SELECT 
 LogId，
 U.Name AS名称，
序列
 FROM LogActor A 
 INNER JOIN用户U ON A.LogActorID = U. [ID] AND LogActorType =User
 UNION ALL 
 SELECT 
 LogId，
 Ga.Name AS名称，
序列
 FROM LogActor A 
 INNER JOIN游戏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，views等）。我倾向于做b）和从我所看到的，似乎是至少标准的做法，如果不是最佳实践。

所以，最后10日志中的项目将如下所示：

  SELECT 
 LogId，
 M.Message，
 COLLESCE（U.Name，Ga.Name，Go.Name）AS名称，
时间，
 A.序列
 FROM Log 
 LEFT JOIN LogActor A ON日志。 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 
  

$ b b

NB - 正如你所看到的，从一个日期比选择所有日志项目更容易，因为我们需要一个（可能非常快）子查询。

hope you all had a happy new year.

So, my question is, what's the best way to make a log of actions. Let me explain it with a example, suppose we have these entities:

User

Friend (User is a friend of another User, many to many relationship)

Message (An user can message another user)

Group (An user can be in various groups)

Game (A game can be played with various players, has some info like date of the game. this results in two tales, games and games_users, the latter stores a relationship between user and a game)

Now, I wanted to make a log, for example:

1. User A (link to user) made a new friend, User B (link to user)

2. User A (link to user), B (link to user) and C (link to user) played a game (link to game)

3. User C (link to user) joined a group D (link to group)

So, I wanted to make a flexible log, that could store as many references as I wanted and references to different entities (user and game for example).

I know two ways of doing this, but they all have one or more problems:

1. When logging an action I directly store the pure text I want (i.e: only 1 char field, which would store 'User C joined a group'). But, there is a problem this way, this text needs to be translated to other languages and I can not have a field for each language.

2. Having a main table log, which each rows represent a log action and a code so I know which action is that, i.e: an user joined a group, x users played a game. I then have another table for each of the foreign key types needed, so I'd have log_user, log_group and log_game For example, log_user with a field referencing log and another referencing user. This way I can have multiple users for a same log action. Problems: rather complex and could result in substantial overhead as depending of the log action I'd have to query to multiple tables. Is this correct, would it be too cpu-intensive?

So, I'm open to new ideas and brainstorming. What's the best approach for this kind of problem? Thanks in advance, I hope I have explained it in a clear way. If there is any question, please ask.

Edit: I decided to start a bounty as I'm not really happy with the answers I have received. Will make any clarifications if needed. Thanks

I want something very similar to facebook/orkut/social networks "friend updates". This will be displayed to users.

解决方案

The following is how I would do it. I have some more comments at the bottom after you have seen the schema.

Log

LogID - unique log ID

Time - date/time of event

LogType - String or ID

(side comment, I would go with an id here so you can use a message table shown below, but if you want quick n dirty you can just just a unique string for each log time (eg "Game Started", "Message Sent", etc)

LogActor

LogID - external key

LogActorType - String or ID (as above, if ID you will need a lookup table)

LogActorID - This is a unique id to the table for the type eg User, Group, Game

Sequence - this is an ordering of the actors.

LogMessage

LogType - exernal key

Message - long string (varchar(max)?)

Language - string(5) so you can key off different language eg "US-en"

Example Data (using your 3 examples)

Log

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

LogActor

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

LogMessage

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

User

ID Name
1  User A
2  User B
3  User C

Game

ID Name
1  Name of game

Group

ID Name
1  Name of group

So here are the nice things about this design.

• It is very easy to extend

• It handles multi-language issues independent of the actors

• It is self documenting, the LogMessage table explains exactly what the data you are storing should say.

Some bad things about it.

• You have to do some complicated processing to read the messages.

• You can't just look at the DB and see what has happened.

In my experience the good parts of this kind of a design outweigh the bad bits. What I have done to allow me to do a quick n dirty look at the log is make a view (which I don't use for the application code) that I can look at when I need to see what is going on via the back end.

Let me know if you have questions.

Update - Some example queries

All of my examples are in sqlserver 2005+, let me know if there is a different version you want me to target.

View the LogActor table (There are a number of ways to do this, the best depends on many things including data distribution, use cases, etc) Here are two:

a)

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

b)

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

In general I think a) is better than b) For example if you are missing an actor type a) will include it (with a null name). However b) is easier to maintain (because the UNION ALL statements make it more modular.) There are other ways to do this (eg CTE, views, etc). I'm inclined to doing it like b) and from what I've seen that seems to be at least standard practice if not best practice.

So, the last 10 items in the log would looks something like this:

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

NB - As you can see, it is easier to select all log items from a date than the last X, because we need a (probably very fast) sub-query for this.

这篇关于数据库：做一个日志的动作，如何处理各种引用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！