保留网页更改历史记录。有点像SO对于修订 [英] Keeping page changes history. A bit like SO does for revisions

问题描述

我有一个CMS系统，可以像这样存储数据：

 条目表
 + --- -  + ------- + ------ + -------- + -------- + 
 | id |标题|文本| index1 | index2 | 
 + ---- + ------- + ------ + -------- + -------- + 
 
条目META表
 + ---- + ---------- + ------- + ------- + 
 | id | entry_id |值| param | 
 + ---- + ---------- + ------- + ------- + 
 
文件表
 + ---- + ---------- + ---------- + 
 | id | entry_id |文件名| 
 + ---- + ---------- + ---------- + 
 
条目到标签表
 + ---- + ---------- + -------- + 
 | id | entry_id | tag_id | 
 + ---- + ---------- + -------- + 
 
标签表
 + ---- + ----- + 
 | id |标签| 
 + ---- + ----- + 
  

实现一个修订系统，有点像SO有。如果我只是为条目表这样做，我计划在一个单独的表中保留对该表的所有更改的副本。因为我必须为至少4个表（TAGS表不需要修改）做这个。这似乎不是一个优雅的解决方案。

请注意， Meta表在 EAV （实体属性值）中建模。

提前感谢。

解决方案

解决方案到类似的问题，我解决它通过将我的表分成两个，一个控制表和一个数据表。控制表将包含主键和对数据表的引用，数据表将包含自动增量修订键和控制表的主键作为外键。

以条目表为例

 条目表
 + ---- + ------- + ------ + -------- + -------- + 
 | id |标题|文本| index1 | index2 | 
 + ---- + ------- + ------ + -------- + -------- + 
  

成为

  b $ b + ---- + ---------- + + ---------- + ---- + -------- + ---- -  + -------- + -------- + 
 | id |修订| |修订| id |标题|文本| index1 | index2 | 
 + ---- + ---------- + + ---------- + ---- + -------- + --- --- + -------- + -------- + 
  

to query

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

而不是更新entries_data表使用insert语句，然后用新的修订条目表。

此系统的优点是只需更改条目表中的修订版本属性即可移至不同修订版本。缺点是你需要更新你的查询。我目前正在将其集成到一个ORM层，所以开发人员不必担心写SQL。我所讨论的另一个想法是有一个集中的修订表，所有的数据表使用。这将允许您使用单个修订版本号描述数据库的状态，类似于subversion修订版号工作原理。

I have a CMS system that stores data across tables like this:

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 |
+----+-----+

I am in trying to implement a revision system, a bit like SO has. If I was just doing it for the Entries Table I was planning to just keep a copy of all changes to that table in a separate table. As I have to do it for at least 4 tables (the TAGS table doesn't need to have revisions) this doesn't seem at all like an elegant solution.

How would you guys do it?

Please notice that the Meta Tables are modeled in EAV (entity-attribute-value).

Thank you in advance.

解决方案

Hi am currently working on solution to similar problem, I am solving it by splitting my tables into two, a control table and a data table. The control table will contain a primary key and reference into the data table, the data table will contain auto increment revision key and the control table's primary key as a foreign key.

taking your entries table as an example

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

becomes

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

to query

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

instead of updating the entries_data table you use an insert statement and then update the entries table's revision with the new revision of the entries table.

The advantage of this system is that you can move to different revisions simply by changing the revision property within the entries table. The disadvantage is you need to update your queries. I am currently integrating this into an ORM layer so the developers don't have worry about writing SQL anyway. Another idea I am toying with is for there to be a centralised revision table which all the data tables use. This would allow you to describe the state of the database with a single revision number, similar to how subversion revision numbers work.

这篇关于保留网页更改历史记录。有点像SO对于修订的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！