数据库版本控制 [英] Database versioning

问题描述

我已经做了几个项目（CMS和EC系统），需要有一些数据版本。

I have made few projects (CMS and EC system) that required to have some data versioned.

通常我会使用这种模式。

Usually I come with that kind of schema

+--------------+
+ foobar       +
+--------------+
+ foobar_id    +
+ version      +
+--------------+

它工作很好，但我想知道是否有一个更好的方式做到。解决方案的主要问题，你必须总是使用子查询获取最新版本。

it worked great but I am wondering if there is a better to way do it. The main problem with that solution you have to always use subquery to get the latest version.

ie：

SELECT * FROM foobar WHERE foobar_id = 2 and version = (SELECT MAX(version) FROM foobar f2 WHERE f2 = 2)

查询更复杂，也有一些性能缺点。

This render most of the queries more complicate and also have some performance drawbacks.

因此，如果你分享创建版本表的经验和每种方法的利弊，这将是很好的。

So it would be nice if you share your experience creating versioned table and what kind of pro and cons of each method.

感谢

推荐答案

我更喜欢在另一个表格中。我会使foobar_history或类似的东西，并做一个FK到foobar_id。这将阻止您不得不一起使用子查询。这有额外的优点，不会污染你的主要数据表与大量的历史数据，你可能不希望看到99％的时间你访问它。

I prefer to have historical data in another table. I would make foobar_history or something similar and make a FK to foobar_id. This will stop you from having to use a subquery all together. This has the added advantage of not polluting your primary data table with the tons of historical data you probably don't want to see 99% of the time you're accessing it.

您可能想要更新此数据的触发器，因为它需要您将当前数据复制到_history，然后进行更新。

You will likely want to make a trigger for updating this data though, as it would require you to copy the current data in to _history and then do the update.

这篇关于数据库版本控制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！