在PostreSQL中实现数据版本控制的方法 [英] Ways to implement data versioning in PostreSQL

问题描述

您能否分享您的想法，如何在PostgreSQL中实现数据版本控制。 （我曾就 Cassandra 和MongoDB 。如果您有任何想法，请选择哪个数据库更适合） p>

假设我需要在一个简单的通讯录中对记录进行版本控制。为了简单起见，地址簿记录存储在一个表中，没有关系。我期望以下历史记录：

• 将很少使用


• 一次以时间机器的方式显示它


• 单个记录不会有超过几百个版本。


• 历史记录不会

我正在考虑以下方法：

• 创建一个新的对象表，以使用通讯录表的架构副本存储记录的历史记录，并将时间戳和外键添加到通讯录表中。




• 创建一种无模式表，以存储对通讯录记录的更改。该表包括：AddressBookId，TimeStamp，FieldName，Value。这样，我将只存储对记录的更改，而不必保持历史记录表和地址簿表同步。




• 创建一个表来存储序列化（JSON）通讯录记录或对通讯录记录的更改。该表如下所示：AddressBookId，TimeStamp，Object（varchar）。
  这又是一个较少的架构，因此我不必使历史记录表与地址簿表保持同步。
  （这是在使用CouchDB对简单文档进行版本控制之后建模的）

解决方案

我做的事情类似于您的第二种方法：具有具有实际工作集的表和具有更改的历史记录（时间戳，record_id，property_id，property_value）。这包括创建记录。第三个表描述了属性（id，property_name，property_type），这有助于在应用程序中进行更高级别的数据转换。因此，您还可以非常轻松地跟踪单个属性的更改。

除了时间戳，您还可以使用类似int的方法，即每个record_id的每次更改都增加，因此您有一个实际的版本。

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)

解决方案

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.

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