SQL历史表设计 [英] SQL history table design

问题描述

我需要设计一个历史表，以跟踪在编辑时在特定记录上更改的多个值。

示例：

向用户显示编辑记录的页面。

标题：Mr.

姓名：Joe

Tele：555-1234

DOB：1900-10-10

如果用户更改了我需要跟踪旧的值并记录新的值。

我想到使用这样的表：

历史
---------------

id

modifiedUserogle
modifiedDate

tableName

recordId

oldValue

newValue└

这样做的一个问题是，每次编辑都会有多个条目。
我正在考虑使用另一个表来对它们进行分组，但是你仍然有同样的问题。

我还在考虑保留

任何想法？

解决方案

您应该定义您感兴趣的效率类型：您可以具有存储空间的效率，记录历史所需的工作效率（交易成本），或以特定方式查询记录历史记录的时间效率。

我注意到您在建议的历史记录表中有一个表名称，这意味着要记录多个表的历史记录，这将排除在历史记录表中存储记录的精确副本的选项，除非您跟踪的所有表总是具有相同的结构。 p>

如果单独处理列，即每个历史记录只记录一个列值，则必须设计一个能够准确表示每个列的多态数据类型

如果存储空间的效率是您的主要关注点，那么我会将历史分解成多个表。这意味着将新的列值表链接到编辑事件表和列定义表。编辑事件表将记录用户和时间戳，列定义表将记录表，列和数据类型。正如@njk所说，你不需要旧的列值，因为你总是可以查询以前的编辑，得到旧的值。这种方法将节省空间的主要原因是，一般来说，用户将编辑一小部分可用字段。

如果查询的效率是您的主要关注点，我将为您要跟踪的每个表设置一个历史表，并为每个历史表添加一个用户和时间戳字段。这在编辑的交易成本方面也应该是有效的。

I need to design a history table to keep track of multiple values that were changed on a specific record when edited.

Example:
The user is presented with a page to edit the record.

Title: Mr.
Name: Joe
Tele: 555-1234
DOB: 1900-10-10

If a user changes any of these values I need to keep track of the old values and record the new ones.

I thought of using a table like this:

History
---------------

id
modifiedUser
modifiedDate
tableName
recordId
oldValue
newValue

One problem with this is that it will have multiple entries for each edit. I was thinking about having another table to group them but you still have the same problem.

I was also thinking about keeping a copy of the row in the history table but that doesn't seem efficient either.

Any ideas?

Thanks!

解决方案

You should define what type of efficiency you're interested in: you can have efficiency of storage space, efficiency of effort required to record the history (transaction cost), or efficiency of time to query for the history of a record in a specific way.

I notice you have a table name in your proposed history table, this implies an intention to record the history of more than one table, which would rule out the option of storing an exact copy of the record in your history table unless all of the tables you're tracking will always have the same structure.

If you deal with columns separately, i.e. you record only one column value for each history record, you'll have to devise a polymorphic data type that is capable of accurately representing every column value you'll encounter.

If efficiency of storage space is your main concern, then I would break the history into multiple tables. This would mean having new column value table linked to both an edit event table and a column definition table. The edit event table would record the user and time stamp, the column definition table would record the table, column, and data type. As @njk noted, you don't need the old column value because you can always query for the previous edit to get the old value. The main reason this approach would be expected to save space is the assumption that, generally speaking, users will be editing a small subset of the available fields.

If efficiency of querying is your main concern, I would set up a history table for every table you're tracking and add a user and time stamp field to each history table. This should also be efficient in terms of transaction cost for an edit.

这篇关于SQL历史表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！