存储用户活动？ PHP，MySQL和数据库设计

Ok so a user comes to my web application and gets points and the like for activity, sort of similar (but not as complex) as this site. They can vote, comment, submit, favorite, vote for comments, write description etc and so on.

好的,所以用户来到我的Web应用程序并获得活动的积分等,与此站点类似(但不是那么复杂)。他们可以投票,评论,提交,收藏,投票评论,写作描述等等。

At the moment I store a user action in a table against a date like so

目前,我将用户操作存储在表中,而不是像这样的日期

Table user_actions
    action_id       - PK AI int
    user_id         - PK int
    action_type     - varchar(20)
    date_of_action  - datetime

So for example if a user comes along and leaves a comment or votes on a comment, then the rows would look something like this

因此,例如,如果用户出现并在评论上留下评论或投票,那么行将看起来像这样

    action_id       = 4
    user_id         = 25
    action_type     = 'new_comment'
    date_of_action  = '2011-11-21 14:12:12';

    action_id       = 4
    user_id         = 25
    action_type     = 'user_comment_vote'
    date_of_action  = '2011-12-01 14:12:12';

All good I hear you say, but not quite, remember that these rows would reside in the user_actions table which is a different table to the ones in which the comments and user comment votes are stored in.

好的,我听到你说,但不完全记住,这些行将驻留在user_actions表中,该表与存储注释和用户评论投票的表不同。

So how do I know what comment links to what row in the user_actions?

那么我如何知道哪些注释链接到user_actions中的哪一行?

Well I could just link to the unique comment_id in the comments table to a new column, called target_primary_key in the user_actions table?

那么我可以将注释表中的唯一comment_id链接到user_actions表中名为target_primary_key的新列?

Nope. Can't do that because the action could equally have been a user_comment_vote which has a composite key (double key)?

不。不能这样做,因为动作可能同样是一个user_comment_vote,它有一个复合键(双键)?

So the thought I am left with is, do I just add the primary keys in a column and comma deliminate them and let PHP parse it out?

So taking the example above, the lines below show how I would store the target primary keys

所以我留下的想法是,我只是在列中添加主键并用逗号去除它们并让PHP解析它吗?因此,以上面的示例为例,下面的行显示了如何存储目标主键

new_comment
target_primary_keys - 12 // the unique comment_id from the comments table 

user_comment_vote
target_primary_keys - 22,12 // the unique comment_id from the comments table

So basically a user makes an action, the user_actions is updated and so is the specific table, but how do I link the two while still allowing for multiple keys?

所以基本上用户做了一个动作,user_actions被更新,特定的表也是如此,但是如何在仍然允许多个键的同时链接这两个?

Has anyone had experience with storing user activity before?

有没有人有过存储用户活动的经验?

Any thoughts are welcome, no wrong answers here.

欢迎任何想法,这里没有错误的答案。

2 个解决方案

#1

The simplest answer is to just use another table, which can contain multiple matches for any key and allow great indexing options:

最简单的答案是只使用另一个表,它可以包含任何键的多个匹配项,并允许很好的索引选项:

create table users_to_actions (
   user_id int(20) not null,
   action_id int(20) not null,
   action_type varchar(25) not null,
   category_or_other_criteria ...
);

create index(uta_u_a) on users_to_actions(user_id, action_id);

To expand on this a bit, you would then select items by joining them with this table:

要稍微扩展一下,您可以通过将它们与此表连接来选择项目:

select
  *
from
  users_to_actions as uta join comments as c using(action_id)
where
  uta.action_type = 'comment' and user_id = 25
order by
  c.post_date

Or maybe a nested query depending on your needs:

或者可能是嵌套查询,具体取决于您的需求:

  select * from users where user_id in(
      select
         user_id
      from 
         users_to_actions
      where
         uta.action_type = 'comment'
  );

#2

You do not need a user actions table. To calculate the "score" you can run one query over multiple tables and multiply the count of matching comments, ratings etc. with a multiplier (25 points for a comment, 10 for a rating, ...).

您不需要用户操作表。要计算“得分”,您可以在多个表上运行一个查询,并将匹配的评论,评级等的计数乘以乘数(评论为25分,评分为10分,......)。

To speed up your page you can store the total score in an extra table or the user table and refresh the total score with triggers if the score changes.

要加快页面速度,您可以将总分存储在额外的表格或用户表格中,如果分数发生变化,则使用触发器刷新总分数。

If you want to display the number of ratings or comments you can do the same.

如果要显示评级或评论的数量,您可以执行相同的操作。

Get the details from the existing tables and store the total number of comments and ratings in an extra table.

从现有表中获取详细信息,并将注释和评级的总数存储在一个额外的表中。

#1