如何使用ORM设置修订版/历史记录跟踪? [英] How do you setup Post Revisions/History Tracking with ORM?

问题描述

我试图弄清楚如何为帖子和其他内容设置修订系统.我认为这意味着需要通过ORM使用任何基本的belongs_to/has_one/has_many/has_many_th(任何优秀的ORM都应支持).

I am trying to figure out how to setup a revisions system for posts and other content. I figured that would mean it would need to work with a basic belongs_to/has_one/has_many/has_many_though ORM (any good ORM should support this).

我当时以为我可以有一些表(带有匹配的模型)

I was thinking a that I could have some tables like (with matching models)

[[POST]] (has_many (text) through (revisions)
id
title

[[Revisions]] (belongs_to posts/text)
id
post_id
text_id
date

[[TEXT]]
id
body
user_id

在哪里可以通过修订表加入以获得最新的TEXT正文.但是我对所有的工作方式还是很迷惑的.有没有人设置像这样的东西?

Where I could join THROUGH the revisions table to get the latest TEXT body. But I'm kind of foggy on how it will all work. Has anyone setup something like this?

基本上，我需要能够加载文章并请求最新的内容条目.

Basically, I need to be able to load an article and request the latest content entry.

// Get the post row
$post = new Model_Post($id);
// Get the latest revision (JOIN through revisions to TEXT) and print that body.
$post->text->body;

能够及时回溯到以前的修订并删除修订，这也将是一个很大的帮助.

Having the ability to shuffle back in time to previous revisions and removing revisions would also be a big help.

无论如何，这些只是我认为某种历史跟踪将如何工作的想法.我愿意接受任何形式的跟踪，我只想知道最佳实践是什么.

At any rate, these are just ideas of how I think that some kind of history tracking would work. I'm open to any form of tracking I just want to know what the best-practice is.

:

似乎向前看，两个表似乎最有意义.由于我计划存储两个 text 副本，因此这也将有助于节省空间.第一个表posts将存储当前修订版本的数据，以便快速读取而无需任何连接.帖子body将是匹配修订的text字段的值-但通过markdown/bbcode/tidy/etc处理.这样一来，我就可以保留原始文本(以进行下一次编辑)，而不必将文本存储在一个修订行中两次(或者每次显示时都必须重新解析).

It seems that moving forward, two tables seems to make the most sense. Since I plan to store two copies of text this will also help to save space. The first table posts will store the data of the current revision for fast reads without any joins. The posts body will be the value of the matching revision's text field - but processed through markdown/bbcode/tidy/etc. This will allow me to retain the original text (for the next edit) without having to store that text twice in one revision row (or having to re-parse it each time I display it).

因此，提取将对ORM友好.然后，对于创建/更新，我将不得不分别处理修订，然后仅使用新的当前修订值更新post对象.

So fetching will be be ORM friendly. Then for creates/updates I will have to handle revisions separately and then just update the post object with the new current revision values.

  CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `published` tinyint(1) unsigned DEFAULT NULL,
  `allow_comments` tinyint(1) unsigned DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `body` text NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `published` (`published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `postsrevisions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `is_current` tinyint(1) unsigned DEFAULT NULL,
  `date` datetime NOT NULL,
  `title` varchar(100) NOT NULL,
  `text` text NOT NULL,
  `image` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `post_id` (`post_id`),
  KEY `user_id` (`user_id`),
  KEY `is_current` (`is_current`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

推荐答案

您的Revisions表模拟了Posts和Text之间的多对多关系.这可能不是您想要的，除非Text中的给定行可以提供Posts中多个行的内容.大多数CMS架构都不是这样.

Your Revisions table as you have shown it models a many-to-many relationship between Posts and Text. This is probably not what you want, unless a given row in Text may provide the content for multiple rows in Posts. This is not how most CMS architectures work.

您当然不需要三个表.我不知道为什么您认为3NF需要此功能. 3NF的要点是，属性不应依赖于非键属性，这并不是说您应该不必要地拆分成多个表.

You certainly don't need three tables. I have no idea why you think this is needed for 3NF. The point of 3NF is that an attribute should not depend on a non-key attribute, it doesn't say you should split into multiple tables needlessly.

因此，您可能只需要两个表之间的一对多关系:Posts和Revisions.也就是说，对于每个帖子，可以有多个修订，但是给定的修订仅适用于一个帖子.其他人提出了两种查找当前帖子的方法:

So you might only need a one-to-many relationship between two tables: Posts and Revisions. That is, for each post, there can be multiple revisions, but a given revision applies to only one post. Others have suggested two alternatives for finding the current post:

• Revisions中的标志列，用于记录当前修订.更改当前修订很简单，只需将标志在所需修订中更改为true，然后将标志更改为先前的当前修订即可.

• A flag column in Revisions to note the current revision. Changing the current revision is as simple as changing the flag to true in the desired revision and to false to the formerly current revision.

Posts中给定帖子当前版本的外键.这甚至更简单，因为您可以在一个更新中而不是两个更新中更改当前修订.但是，循环外键引用可能会导致与备份和备份有关的问题.恢复，级联更新等.

A foreign key in Posts to the revision that is current for the given post. This is even simpler, because you can change the current revision in one update instead of two. But circular foreign key references can cause problems vis-a-vis backup & restore, cascading updates, etc.

您甚至可以使用单个表来实现修订系统:

You could even implement the revision system using a single table:

CREATE TABLE PostRevisions (
  post_revision_id SERIAL PRIMARY KEY,
  post_id INT NOT NULL,
  is_current TINYINT NULL,
  date DATE,
  title VARCHAR(80) NOT NULL,
  text TEXT NOT NULL,
  UNIQUE KEY (post_id, is_current)
);

我不确定每次修订时都存储title是重复的，因为标题可以和文本一样多地修改，不是吗?

I'm not sure it's duplication to store the title with each revision, because the title could be revised as much as the text, couldn't it?

列is_current应该为1或NULL.唯一约束不计算NULL，因此is_current为1的行只能包含一行，而is_current为NULL的行数不受限制.

The column is_current should be either 1 or NULL. A unique constraint doesn't count NULLs, so you can have only one row where is_current is 1 and an unlimited number of rows where it's NULL.

这确实需要更新两行以使修订为最新版本，但是通过将模型简化为单个表，您会获得一些简化.当您使用ORM时，这是一个很大的优势.

This does require updating two rows to make a revision current, but you gain some simplicity by reducing the model to a single table. This is a great advantage when you're using an ORM.

您可以创建视图以简化查询当前帖子的常见情况:

You can create a view to simplify the common case of querying current posts:

CREATE VIEW Posts AS SELECT * FROM PostRevisions WHERE is_current = 1;

---

更新::关于您的更新问题:我同意适当的关系设计将鼓励使用两个表，以便您可以为该帖子的所有修订版添加Post不变式的一些属性.但是大多数ORM工具都假定一个实体存在于单个表中，而ORM笨拙地将多个表中的行连接起来以构成给定的实体.因此，我想说，如果优先考虑使用ORM，则应将帖子和修订存储在单个表中.牺牲一点关系正确性以支持ORM范式的假设.

---

update: Re your updated question: I agree that proper relational design would encourage two tables so that you could make a few attributes of a Post invariant for all that post's revisions. But most ORM tools assume an entity exists in a single table, and ORM's are clumsy at joining rows from multiple tables to constitute a given entity. So I would say if using an ORM is a priority, you should store the posts and revisions in a single table. Sacrifice a little bit of relational correctness to support the assumptions of the ORM paradigm.

另一个建议是考虑维度建模.这是一所旨在支持OLAP和数据仓库的数据库设计学院.它明智地使用了非规范化，因此您通常可以在 Star Schema 中组织数据.主要实体(事实表")由单个表表示，因此这对于以ORM为中心的应用程序设计将是一个胜利.

Another suggestion is to consider Dimensional Modeling. This is a school of database design to support OLAP and data warehousing. It uses denormalization judiciously, so you can usually organize data in a Star Schema. The main entity (the "Fact Table") is represented by a single table, so this would be a win for an ORM-centric application design.

这篇关于如何使用ORM设置修订版/历史记录跟踪?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！