保留页面更改历史记录。有点像SO对修订版所做的操作。

Question

我有一个CMS系统，它像这样跨表存储数据：

Entries Table
+----+-------+------+--------+--------+
| id | title | text | index1 | index2 |
+----+-------+------+--------+--------+

Entries META Table
+----+----------+-------+-------+
| id | entry_id | value | param |
+----+----------+-------+-------+

Files Table
+----+----------+----------+
| id | entry_id | filename |
+----+----------+----------+

Entries-to-Tags Table
+----+----------+--------+
| id | entry_id | tag_id |
+----+----------+--------+

Tags Table
+----+-----+
| id | tag |
+----+-----+

我正在尝试实现一个修订系统，有点像SO。如果我只是针对Entries Table做这件事，我计划只在单独的表中保留该表所有更改的副本。由于我必须为至少4个表执行此操作（TAGS表不需要进行修订），因此这似乎完全不是一种优雅的解决方案。

你们会如何处理？

请注意，元表采用EAV（实体-属性-值）建模。

提前感谢您。

- Frankie

Answer 1

你好，我目前正在解决类似问题的方案，我的解决方法是将表拆分为两个部分：控制表和数据表。控制表中包含主键以及对数据表的引用；数据表中包含自增修订键以及控制表主键作为外键。

以你的输入表为例：

Entries Table
+----+-------+------+--------+--------+
| id | title | text | index1 | index2 |
+----+-------+------+--------+--------+

变成

entries             entries_data
+----+----------+   +----------+----+--------+------+--------+--------+
| id | revision |   | revision | id |  title | text | index1 | index2 |
+----+----------+   +----------+----+--------+------+--------+--------+

查询

select * from entries join entries_data on entries.revision = entries_data.revision;

不要更新entries_data表，而是使用插入语句，然后使用新的entries表版本更新entries表的修订版本。

这个系统的优点是，您可以通过更改entries表中的修订属性轻松地移动到不同的修订版本。缺点是您需要更新查询。我目前正在将其集成到ORM层中，因此开发人员无需担心编写SQL。我正在考虑的另一个想法是有一个集中的修订表，所有数据表都使用它。这将允许您使用单个修订号描述数据库的状态，类似于subversion修订号的工作方式。

- ejrowley

---

为什么有人给ejrowley的答案点了踩？我还在寻找最佳解决方案，并会给予应有的荣誉。但这个方案看起来可行，不是吗？ - Frankie

尽管你的系统不完全符合我的要求，但我认为Edward Williams的Media Wiki表格方案更接近于性能和可用性方面的答案；我相信你的答案与Edward的答案在原理上是一致的，只是在方案设计上更好，所以我接受了它。谢谢！ - Frankie

Answer 2

请看这个问题：如何在数据库中对记录进行版本控制 为什么不为每个表单独创建一个history_table（就像链接问题中的被接受答案所述）？该表格只需具有原始表格PK和修订号的复合主键。毕竟，您仍然需要将数据存储在某个地方。

- Edward Williams

---

你的答案非常有趣，特别是 Media Wiki 数据库方案。它创建了一个额外的层级 表 -> 修订键 -> 修订数据，使得 数据表 尽可能小，同时提供修订历史记录，并能够像 Stack Overflow 一样在修订之间进行导航。我仍然想知道是否有更加优雅的存储方式。 - Frankie

Answer 3

在我们的一个项目中，我们采取了以下方式：

Entries Table
+----+-----------+---------+
| id | date_from | date_to |
+----+--------_--+---------+

EntryProperties Table
+----------+-----------+-------+------+--------+--------+
| entry_id | date_from | title | text | index1 | index2 |
+----------+-----------+-------+------+--------+--------+

有点复杂，但仍然可以跟踪完整对象的生命周期。因此，对于查询活动实体，我们采用了以下方法：

SELECT 
entry_id, title, text, index1, index2
FROM
Entities INNER JOIN EntityProperties
ON Entities.id = EntityProperties.entity_id
AND Entities.date_to IS NULL
AND EntityProperties.date_to IS NULL

唯一的问题是当实体被移除（所以我们在那里放了一个date_to），然后由管理员恢复。使用给定的方案，没有办法追踪这种技巧。

总的来说，像这样任何尝试的缺点是显而易见的-你必须编写大量的TSQL，而非版本化的数据库将会使用类似于“select A join B”的东西。

- Vitaly