数据库 - 设计“事件"桌子

我更倾向于采用这种方法，因为优点似乎远远大于缺点，但我仍然有点不情愿...... 我是否遗漏了什么?您对此有何看法?

谢谢！

@coolgeek:

我稍微做的一件事不同的是维持一个entity_type 表，并在其中使用其 IDobject_type 列(在您的情况下，表格"列).你会想要用 event_type 做同样的事情表.

为了清楚起见，您的意思是我应该添加一个附加表来映射表中允许的事件，并在事件表中使用该表的 PK 而不是 TABLE/<代码>事件对?

@本:

这些都是从现有数据，不是吗?

附加表主要与统计有关，但我的数据不存在，一些示例:

user_ad_stats user_post_stats------------- ---------------user_ad_id (FK) user_post_id (FK)ip ip日期 日期类型(印象深刻，点击)

如果我删除这些表格，我将无法跟踪谁、什么或何时，不知道视图在这里有什么帮助.

我同意它应该是分开的，但更多的是因为它从根本上不同的数据.某人是什么以及某人所做的有两种不同事物.我不认为波动是这样的很重要.

两种方式我都听说过，但我在 MySQL 手册中找不到任何说明任何一种都正确的内容.无论如何，我同意你的观点，它们应该是分开的表格，因为它们代表各种数据(与常规方法相比具有更多描述性的额外好处).

我认为你想念森林是因为树，可以这么说.

你的表的谓词是来自 IP IP 在 DATE 时间的用户 IDEVENTed to TABLE"这似乎合理，但也有问题.

我的意思是不像 EAV 那样糟糕"是所有记录都遵循线性结构并且它们很容易查询，没有层次结构所以所有查询都可以通过简单的 SELECT 完成代码>.

关于你的第二个陈述，我想你在这里理解错了；IP 地址不一定与用户相关联.表结构应该是这样的:

IP 地址 (IP) 做了一些事情(EVENT) 到 PK (ID)日期 (DATE) 上的表 (TABLE).

例如，在我上面示例的最后一行中，它应该读取 IP 217.0.0.1(某些管理员)，在 2010-04-20 03 删除了用户 #2(其最后一个已知 IP 为 127.0.0.2):20:00.

您仍然可以加入用户活动给用户，但你不能实现外键约束.

确实，这是我最关心的问题.但是，我不完全确定这种设计会出现什么问题，而传统关系设计不会出现问题.我可以发现一些警告，但只要与数据库混淆的应用程序知道它在做什么，我想应该不会有任何问题.

在这个论点中重要的另一件事是我将存储更多的事件，每个事件与原始设计相比将增加一倍以上，使用 ARCHIVE 存储非常有意义这里的引擎，唯一的问题是它不支持 FKs(既不支持 UPDATEs 也不支持 DELETEs).

我强烈推荐这种方法.由于您大概对 OLTP 和 OLAP 使用相同的数据库，因此您可以通过添加一些星星和雪花来获得显着的性能优势.

我有一个社交网络应用，目前有 65 桌.我维护一个表来跟踪对象(博客/帖子、论坛/主题、画廊/相册/图像等)的视图，另一个表用于对象推荐，以及第三个表来总结十几个其他表中的插入/更新活动.

我做的稍有不同的一件事是维护一个 entity_type 表，并在 object_type 列(在您的情况下为TABLE"列)中使用其 ID.您可能希望对 event_type 表做同样的事情.

为 Alix 澄清 - 是的，您维护一个对象引用表和一个事件引用表(这些将是您的维度表).您的事实表将包含以下字段:

idobject_id事件编号事件时间IP地址

After reading the tips from this great Nettuts+ article I've come up with a table schema that would separate highly volatile data from other tables subjected to heavy reads and at the same time lower the number of tables needed in the whole database schema, however I'm not sure if this is a good idea since it doesn't follow the rules of normalization and I would like to hear your advice, here is the general idea:

I've four types of users modeled in a Class Table Inheritance structure, in the main "user" table I store data common to all the users (id, username, password, several flags, ...) along with some TIMESTAMP fields (date_created, date_updated, date_activated, date_lastLogin, ...).

To quote the tip #16 from the Nettuts+ article mentioned above:

Example 2: You have a "last_login" field in your table. It updates every time a user logs in to the website. But every update on a table causes the query cache for that table to be flushed. You can put that field into another table to keep updates to your users table to a minimum.

Now it gets even trickier, I need to keep track of some user statistics like

• how many unique times a user profile was seen
• how many unique times a ad from a specific type of user was clicked
• how many unique times a post from a specific type of user was seen
• and so on...

In my fully normalized database this adds up to about 8 to 10 additional tables, it's not a lot but I would like to keep things simple if I could, so I've come up with the following "events" table:

|------|----------------|----------------|---------------------|-----------|
| ID   | TABLE          | EVENT          | DATE                | IP        | 
|------|----------------|----------------|---------------------|-----------|
| 1    | user           | login          | 2010-04-19 00:30:00 | 127.0.0.1 |
|------|----------------|----------------|---------------------|-----------|
| 1    | user           | login          | 2010-04-19 02:30:00 | 127.0.0.1 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | created        | 2010-04-19 00:31:00 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | activated      | 2010-04-19 02:34:00 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | approved       | 2010-04-19 09:30:00 | 217.0.0.1 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | login          | 2010-04-19 12:00:00 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | created        | 2010-04-19 12:30:00 | 127.0.0.1 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | impressed      | 2010-04-19 12:31:00 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | clicked        | 2010-04-19 12:31:01 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | clicked        | 2010-04-19 12:31:02 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | clicked        | 2010-04-19 12:31:03 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | clicked        | 2010-04-19 12:31:04 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | clicked        | 2010-04-19 12:31:05 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | blocked        | 2010-04-20 03:19:00 | 217.0.0.1 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | deleted        | 2010-04-20 03:20:00 | 217.0.0.1 |
|------|----------------|----------------|---------------------|-----------|

Basically the ID refers to the primary key (id) field in the TABLE table, I believe the rest should be pretty straightforward. One thing that I've come to like in this design is that I can keep track of all the user logins instead of just the last one, and thus generate some interesting metrics with that data.

Due to the growing nature of the events table I also thought of making some optimizations, such as:

• #9: Since there is only a finite number of tables and a finite (and predetermined) number of events, the TABLE and EVENTS columns could be setup as ENUMs instead of VARCHARs to save some space.
• #14: Store IPs as UNSIGNED INTs with INET_ATON() instead of VARCHARs.
• Store DATEs as TIMESTAMPs instead of DATETIMEs.
• Use the ARCHIVE (or the CSV?) engine instead of InnoDB / MyISAM.
  • Only INSERTs and SELECTs are supported, and data is compressed on the fly.

Overall, each event would only consume 14 (uncompressed) bytes which is okay for my traffic I guess.

Pros:

• Ability to store more detailed data (such as logins).
• No need to design (and code for) almost a dozen additional tables (dates and statistics).
• Reduces a few columns per table and keeps volatile data separated.

Cons:

• Non-relational (still not as bad as EAV):
  • SELECT * FROM events WHERE id = 2 AND table = 'user' ORDER BY date DESC();
• 6 bytes overhead per event (ID, TABLE and EVENT).

I'm more inclined to go with this approach since the pros seem to far outweigh the cons, but I'm still a little bit reluctant... Am I missing something? What are your thoughts on this?

Thanks!

@coolgeek:

One thing that I do slightly differently is to maintain an entity_type table, and use its ID in the object_type column (in your case, the 'TABLE' column). You would want to do the same thing with an event_type table.

Just to be clear, you mean I should add an additional table that maps which events are allowed in a table and use the PK of that table in the events table instead of having a TABLE / EVENT pair?

@ben:

These are all statistics derived from existing data, aren't they?

The additional tables are mostly related to statistics but I the data doesn't already exists, some examples:

user_ad_stats                          user_post_stats
-------------                          ---------------
user_ad_id (FK)                        user_post_id (FK)
ip                                     ip
date                                   date
type (impressed, clicked)

If I drop these tables I've no way to keep track of who, what or when, not sure how views can help here.

I agree that it ought to be separate, but more because it's fundamentally different data. What someone is and what someone does are two different things. I don't think volatility is so important.

I've heard it both ways and I couldn't find anything in the MySQL manual that states that either one is right. Anyway, I agree with you that they should be separated tables because they represent kinds of data (with the added benefit of being more descriptive than a regular approach).

I think you're missing the forest for the trees, so to speak.

The predicate for your table would be "User ID from IP IP at time DATE EVENTed to TABLE" which seems reasonable, but there are issues.

What I meant for "not as bad as EAV" is that all records follow a linear structure and they are pretty easy to query, there is no hierarchical structure so all queries can be done with a simple SELECT.

Regarding your second statement, I think you understood me wrong here; the IP address is not necessarily associated with the user. The table structure should read something like this:

IP address (IP) did something (EVENT) to the PK (ID) of the table (TABLE) on date (DATE).

For instance, in the last row of my example above it should read that IP 217.0.0.1 (some admin), deleted the user #2 (whose last known IP is 127.0.0.2) at 2010-04-20 03:20:00.

You can still join, say, user events to users, but you can't implement a foreign key constraint.

Indeed, that's my main concern. However I'm not totally sure what can go wrong with this design that couldn't go wrong with a traditional relational design. I can spot some caveats but as long as the app messing with the database knows what it is doing I guess there shouldn't be any problems.

One other thing that counts in this argument is that I will be storing much more events, and each event will more than double compared to the original design, it makes perfect sense to use the ARCHIVE storage engine here, the only thing is it doesn't support FKs (neither UPDATEs or DELETEs).

I highly recommend this approach. Since you're presumably using the same database for OLTP and OLAP, you can gain significant performance benefits by adding in some stars and snowflakes.

I have a social networking app that is currently at 65 tables. I maintain a single table to track object (blog/post, forum/thread, gallery/album/image, etc) views, another for object recommends, and a third table to summarize insert/update activity in a dozen other tables.

One thing that I do slightly differently is to maintain an entity_type table, and use its ID in the object_type column (in your case, the 'TABLE' column). You would want to do the same thing with an event_type table.

Clarifying for Alix - Yes, you maintain a reference table for objects, and a reference table for events (these would be your dimension tables). Your fact table would have the following fields:

id
object_id
event_id
event_time
ip_address