如何结构化数据库中的活动？ [英] How to structure table Activities in a database?

问题描述

我有一个cakephp用mysql数据库写的网站。
进入我的网站我想跟踪每个用户的活动，例如（像这个网站）如果用户插入一个产品我想把这个活动放入我的数据库。
我有两种方式：
1）一个名为Activities的表：
- id
- user_id
- title
- text
- 类型（活动类型：评论，发布编辑）

2）活动更多的表
- table activities_comment
- table activities_post
- table activities_badges

问题是当我去一个用户的页面活动时，我可以有不同类型的活动，我不知道这个解决方案更好的是因为一个评论有一个标题和一个评论，一个帖子只有一个文本，一个徽章有一个外部的id到它的表（例如）ecc ...
帮助我请

$ b $我不熟悉CakePHP，但从纯粹的数据库角度看，你的数据模型应该看起来类似于这样：

符号表示类别（aka。继承，子类，子类型，泛化等级等）。请查看 ERwin方法指南更多信息

通常有3种策略来实现该类别：

1. 单表中的所有类型。这需要很多NULL，并且需要使用CHECK来确保单独的子类型不会被不适当地混合。


2. 单独表中的所有具体类型（不包括基础，在您的情况下为ACTIVITY） ，这意味着必须在所有子表中重复常见的字段和关系。


3. 所有类型在单独的表（包括基数）中。这种实现需要更多的加入，但是是灵活和干净的。这应该是您的默认值，除非有强烈的理由反对。

I have a site written in cakephp with a mysql database. Into my site I want to track the activities of every users, for example (like this site) if a user insert a product I want to put this activity into my database. I have 2 ways: 1) One table called Activities with: - id - user_id - title - text - type (the type of activity: comment, post edit)

2) more table differenced by activities - table activities_comment - table activities_post - table activities_badges

The problem is when I go to the page activities of a user I can have different type of activities and I don't know which of this solution is better because a comment has a title and a comment, a post has only a text, a badge has an external id to its table (for example) ecc... Help me please

解决方案

I'm not familiar with CakePHP, but from purely database perspective your data model should probably look similar to this:

The symbol denotes category (aka. inheritance, subclass, subtype, generalization hierarchy etc.). Take a look at "Subtype Relationships" in ERwin Methods Guide for more info.

There are generally 3 strategies for implementing the category:

1. All types in single table. This requires a lot of NULLs and requires CHECKs to make sure separate subtypes are not inappropriately "intermingled".
2. All concrete types in separate tables (excluding the base, which is ACTIVITY in your case), which means common fields and relationships must be repeated in all child tables.
3. All types in separate tables (including the base). This implementation requires a little more JOINing, but is flexible and clean. It should be your default, unless there are strong reasons against it.

这篇关于如何结构化数据库中的活动？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！