在严重标准化的数据库中选择表数据的通用版本控制策略 [英] Generic version control strategy for select table data within a heavily normalized database

问题描述

参考以下示例（但非常简化）的结构（在psuedo SQL中） ），我希望更好地解释一下。

  TABLE StructureName {
 Id GUID PK，
 Name varchar（50）NOT NULL 
} 
 
表结构{
 Id GUID PK，
 ParentId GUID， -  FK到结构
 NameId GUID NOT NULL  -  FK到StructureName 
} 
 
表某些东西{
 Id GUID PK，
 RootStructureId GUID NOT NULL  -  FK to Structure 
} 
  

可以看到，Structure是一个简单的树结构（不用担心子问题的排序）。 StructureName是翻译系统的简化。最后'Something'只是引用树的根结构的一些东西。

这只是许多需要版本化的表之一，但是这个例子大多数情况下。

对结构表的名称和/或树布局的任何更改都有版本要求。

似乎有一些可能性来解决这个问题，比如复制整个结构，但大多数方法导致一个松散的参考诚信。例如，如果一个人遵循这种方法，则必须复制'Something'记录，因为根结构将是一个新记录，并且具有一个新的ID。

目前，我感觉有点不舒服

任何想法都将非常感激。

谢谢

leppie

解决方案

$ b

完整副本：创建结构的副本，但对于每个表格，向PK添加 version_id 和所有FK;

• 亲：容易查询历史记录


• con：大量（复制的冗余数据）

更改副本以及 valid_from / valid_to 数据。

• pro：低数据量已复制


• con：难以查询，因为必须按时间间隔加入






变化：这适用于这两种方案。









• con：正常操作会对更大的表执行操作，从而导致性能影响






审核日志：根据您的实际需求，只需创建如下的审核跟踪即可：

  id，timestamp，changed_table，changed_column，old_value，new_value，changed_by 
  pre> 
 
 
您可以将它扩展为一个完整的表结构：
  transaction，table_change，changed_column 
  
 
 
 
 
 <  
 
 con：如果您需要在给定时间重建一组记录的状态，查询将成为一个噩梦
 
 
 
 
 
我写了有关各种版本控制方法的博客，但请注意：它采用德语。
 
Sorry for the long winded title, but the requirement/problem is rather specific.

With reference to the following sample (but very simplified) structure (in psuedo SQL), I hope to explain it a bit better.
TABLE StructureName {
  Id GUID PK,
  Name varchar(50) NOT NULL
}

TABLE Structure {
  Id GUID PK,
  ParentId GUID,                 -- FK to Structure
  NameId GUID NOT NULL           -- FK to StructureName
}

TABLE Something {
  Id GUID PK,
  RootStructureId GUID NOT NULL  -- FK to Structure
}
As one can see, Structure is a simple tree structure (not worried about ordering of children for the problem). StructureName is a simplification of a translation system. Finally 'Something' is simply something referencing the tree's root structure.

This is just one of many tables that need to be versioned, but this one serves as a good example for most cases.

There is a requirement to version to any changes to the name and/or the tree 'layout' of the Structure table. Previous versions should always be available.

There seems to be a few possibilities to tackle this issue, like copying the entire structure, but most approaches causes one to 'loose' referential integrity. Example if one followed this approach, one would have to make a duplicate of the 'Something' record, given that the root structure will be a new record, and have a new ID.

Other avenues of possible solutions are looking into how Wiki's handle this or go a lot further and look how proper version control systems work.

Currently, I feel a bit clueless how to proceed on this in a generic way.

Any ideas will be greatly appreciated.

Thanks

leppie
解决方案
Some quick ideas: 

Full copy: Create a copy of the structure, but for every table add a version_id column to the PK and all FKs; thus you can create copies of the life data with complete referential integrity.


pro: easy to query the history
con: large amount of (redundant data copied)


Change copy: Only copy the stuff that actually changes, along with valid_from / valid_to data.


pro: low data volum copied
con: hard to query, because one has to join on intervals


Variation: This applies to both schemes. Instead of creating a copy of the structure, you might keept the current record in the same table as the old versions, but tag it as current.


pro: smaller number of tables, easier mixing of history and current information
con: normal operation operates on much bigger tables, which will cause a performance impact


Auditing log: Depending on your actual requirements it be sufficient to just create an audit trail like this:
id,  timestamp,  changed_table,  changed_column,  old_value,  new_value,  changed_by
You might extend that to a full table structure: 
transaction,  table_change,  changed_column



pro: generic, hence easy to implement for a large number of tables
con: if you need to reconstruct the state of a set of records at a given time, querying will become a nightmare


I wrote a blog about various approaches to versioning, but be warned: it's in German.

                        
这篇关于在严重标准化的数据库中选择表数据的通用版本控制策略的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！