时间相关字段的数据库设计

I am making a MySQL database and am fairly confident I know how to normalize it. However, there is an issue I am not sure how to deal with.

我正在创建一个MySQL数据库，我很有信心知道如何规范化它。然而，有一个问题我不知道该怎么处理。

Say I have a table

假设我有一张桌子

users
----------
user_id primary key
some_field
some_field2
start_date
user_level

Now, user_level gives the user's level, which can be 1,2,3,4,5 say. But as time passes the user may change levels. Obviously if they change levels I can simply do an UPDATE to the users table. But I want to keep a historical record of the users' past levels

user_level给出用户级别，可以是1 2 3 4 5。但是随着时间的流逝，用户可能会改变等级。显然，如果他们改变了级别，我可以简单地对users表进行更新。但是我想保存用户过去水平的历史记录

For this reason, I am considering a new table called user_level_history

出于这个原因，我正在考虑一个名为user_level_history的新表

user_level_history
--------------
id autoincrement primary key
user_id
level_start_date

and then modify the users table:

然后修改用户表:

users
----------
user_id primary key
some_field
some_field2
start_date
user_level_history_id

Then to get the user's current level I check the

然后，为了获得用户的当前级别，我检查

user_level_history_id = user_level_history.id

And to get the user's history I can SELECT from user_level_history all rows with the user_id and order chronologically.

为了获取用户的历史记录，我可以从user_level_history中按时间顺序选择user_id和order的所有行。

Is this the standard way to do this? I can't imagine I'm the first person to come across this problem.

这是标准的方法吗?我无法想象我是第一个遇到这个问题的人。

One more point: I am imagining less than 5000 users. Would having many, many more users require a different solution?

还有一点:我想象不到5000个用户。有更多的用户需要不同的解决方案吗?

Thanks in advance.

提前谢谢。

2 个解决方案

#1

I think that could be designed like this:
Have a table for level information like value(1,2,3,4,5) , description ...
Have an association table for user_level_history containing user_id, level_id,level_start_date ...
Have a foreign key from level table to user table with the role user-active-level.

我认为可以这样设计:拥有一个表，用于诸如值(1、2、3、4、5)、描述……有一个user_level_history的关联表，其中包含user_id、level_id、level_start_date…具有从级别表到用户表的外键，并具有用户操作级别的角色。

You need to develop a mechanism that when user level is changing, inserting to history table occurs. 时间相关字段的数据库设计

您需要开发一种机制，当用户级别发生变化时，插入到历史表中。

#2

No, you aren't the first. Querying temporal data is a common requirement, especially in data warehouse/data mining.

不，你不是第一个。查询时间数据是一种常见的需求，特别是在数据仓库/数据挖掘中。

The relational data model doesn't have any native, built in support for storing or querying "temporal data".

关系数据模型没有任何本机数据，内置了对存储或查询“时态数据”的支持。

A lot of work has been done; I have a book by C.J.Date et al. that covers the topic decently: "Temporal Data and the Relational Model". I've also come across several white papers.

已经做了很多工作;我有一本C.J.的书。Date等很好地涵盖了这个主题:“时态数据和关系模型”。我也遇到过一些白皮书。

One typical, reasonably simplistic approach to storing a "history" is to have a "current" table (like the one you already have, and then add a "history" table. Whenever a row is changed (inserted,updated,deleted) in the "current" table, you add a row to the "history" table, along with the date the row was changed. (You can store a copy of the pre-change row, or a copy of the post-change row, or both.)

存储“历史”的一种典型的、相当简单的方法是拥有一个“当前”表(就像您已经拥有的表一样)，然后添加一个“历史”表。每当在“当前”表中更改(插入、更新、删除)一行时，您就向“history”表添加一行，以及更改行的日期。(您可以存储预修改行的副本，或更改后的行或两者的副本。)

With this approach, there's no need to add any columns to the "current" table.

使用这种方法，不需要向“当前”表添加任何列。

#1