您能否分享您的想法，如何在 PostgreSQL 中实现数据版本控制。 （我问过关于 Cassandra 和 MongoDB 如果您有任何想法哪个数据库更适合，请分享）
假设我需要在一个简单的地址簿中对记录进行版本控制。为了简单起见，地址簿记录存储在一张没有关系的表中。我希望历史：
- 将不经常使用
- 将被一次全部使用，以“时间机器”的方式呈现它，
- 单个记录的版本不会超过几百个。
- 历史不会过期。
我正在考虑以下方法：
-
创建一个新的对象表来存储带有地址簿表架构副本的记录历史记录，并向地址簿表添加时间戳和外键。
-
创建一种无模式表来存储对地址簿记录的更改。该表将包含：AddressBookId、TimeStamp、FieldName、Value。这样，我将只存储对记录的更改，而不必保持历史记录表和地址簿表同步。
-
创建一个表来存储序列化 (JSON) 通讯簿记录或对通讯簿记录的更改。该表如下所示：AddressBookId、TimeStamp、Object (varchar)。
同样，这是较少的模式，因此我不必使历史记录表与地址簿表保持同步。
（这是根据 CouchDB 的简单文档版本控制建模的）
Can you share your thoughts how would you implement data versioning in PostgreSQL. (I've asked similar question regarding Cassandra and MongoDB. If you have any thoughts which db is better for that please share)
Suppose that I need to version records in a simple address book. Address book records are stored in one table without relations for simplicity. I expect that the history:
- will be used infrequently
- will be used all at once to present it in a "time machine" fashion
- there won't be more versions than few hundred to a single record.
- history won't expire.
I'm considering the following approaches:
-
Create a new object table to store history of records with a copy of schema of addressbook table and add timestamp and foreign key to address book table.
-
Create a kind of schema less table to store changes to address book records. Such table would consist of: AddressBookId, TimeStamp, FieldName, Value. This way I would store only changes to the records and I wouldn't have to keep history table and address book table in sync.
-
Create a table to store seralized (JSON) address book records or changes to address book records. Such table would looks as follows: AddressBookId, TimeStamp, Object (varchar).
Again this is schema less so I wouldn't have to keep the history table with address book table in sync.
(This is modelled after Simple Document Versioning with CouchDB)
发布评论
评论（3）
我做了类似于您的第二种方法的操作：拥有包含实际工作集的表和包含更改的历史记录（时间戳、记录 ID、属性 ID、属性值）。这包括创建记录。第三个表描述了属性（id、property_name、property_type），这有助于应用程序中更高层的数据转换。因此您还可以非常轻松地跟踪单个属性的更改。
除了时间戳之外，您还可以使用类似 int 的类型，每个 record_id 的每次更改都会递增，这样您就有了一个实际的版本。
I do something like your second approach: have the table with the actual working set and a history with changes (timestamp, record_id, property_id, property_value). This includes the creation of records. A third table describes the properties (id, property_name, property_type), which helps in data conversion higher up in the application. So you can also track very easily changes of single properties.
Instead of a timestamp you could also have an int-like, wich you increment for every change per record_id, so you have an actual version.
您可以有
start_date和end_date。当
end_date为 NULL 时，它是实际记录。You could have
start_dateandend_date.When
end_dateis NULL, it`s the actual record.我正在对术语表数据进行版本控制，我的方法非常成功地满足了我的需求。基本上，对于需要版本控制的记录，您可以将字段集分为持久字段和版本相关字段，从而创建两个表。第一组中的一些也应该是第一个表的唯一键。
地址
ID [PK]
全名[英国]
生日[英国]
版本
ID [PK]
地址 ID [英国]
时间戳[英国]
地址
通过这种方式，您将获得由全名和生日确定的地址主题（不应通过版本控制更改）以及包含地址的版本化记录。 address_id 应通过外键与 Address:id 相关。对于版本表中的每个条目，您将获得主题 Address:id=address_id 的新版本，并具有特定时间戳，通过这种方式您可以拥有历史参考。
I'm versioning glossary data, and my approach was pretty successful for my needs. Basically, for records you need versioning, you divide the fieldset into persistent fields and version-dependent fields, thus creating two tables. Some of the first set should also be the unique key for the first table.
Address
id [pk]
fullname [uk]
birthday [uk]
Version
id [pk]
address_id [uk]
timestamp [uk]
address
In this fashion, you get an address subjects determined by fullname and birthday (should not change by versioning) and a versioned records containing addresses. address_id should be related to Address:id through foreign key. With each entry in Version table you'll get new version for subject Address:id=address_id with a specific timestamp, in which way you can have a history reference.