关系数据库和版本控制：修订间隔
我一直在考虑如何将数据版本控制应用于我拥有的相对简单的数据库，并认为我应该做类似 Jim T 的帖子，其中存在全局修订号（例如 Subversion 或 Mercurial 中的版本），并且每个数据库记录都有一个有效间隔。
示例：
创建一个人。
|姓名|出生日期|电话|发件人|收件人| |弗雷德|4 月 1 日|555-29384|1 |NULL|更新电话号码。
|姓名|出生日期|电话|发件人|收件人| |弗雷德|4 月 1 日|555-29384|1 |1 | |弗雷德|4 月 1 日|555-43534|2 |NULL|删除弗雷德：
|姓名|出生日期|电话|发件人|收件人| |弗雷德|4 月 1 日|555-29384|1 |1 | |弗雷德|4 月 1 日|555-43534|2 |2 |
这种方法有缺点吗？ 看起来没那么复杂。
我唯一能想到的是，除了与数据独立且无关的自动增量记录号之外，它似乎会对主键产生微妙的影响。 例如，如果您有这样的数据：
Person: (primary key = PersonID which is an autoincrementing integer)
|PersonID|Name|Telephone|
|1 |Fred|555-2938|
|2 |Lois|555-2939|
|3 |Jim |555-1000|
Home: (primary key = HomeID which is an autoincrementing integer)
|HomeID|Address |
|1 |123 Elm St. |
|2 |456 Maple Ave.|
PersonHome: (primary key = person ID and home ID)
|PersonID|HomeID|
|1 |1 |
|2 |1 |
|3 |2 |
那么您不能只添加上面的“From”和“To”字段，因为您破坏了主键的唯一性。 相反，我可能必须做这样的事情（添加适当的索引来替换前面主键的功能）：
Person: (primary key = K which is an autoincrementing integer)
|K|PersonID|Name |Telephone|From|To |
|1|1 |Fred |555-2938 |1 |NULL|
|2|2 |Lois |555-2939 |1 |1 |
|3|3 |Jim |555-1000 |1 |NULL|
|4|4 |Sunshine |555-2000 |1 |2 |
|5|2 |Lois |555-1000 |2 |NULL|
|6|4 |Daisy May|555-2000 |3 |NULL|
|7|5 |Connor | |5 |NULL|
Home: (primary key = K which is an autoincrementing integer)
|K|HomeID|Address |From|To |
|1|1 |123 Elm St. |1 |NULL|
|2|2 |456 Maple Ave.|1 |NULL|
|3|3 |789 Vista Dr. |1 |3 |
|4|3 |104 Vista Dr. |4 |NULL|
PersonHome: (primary key = K which is an autoincrementing integer)
|K|PersonID|HomeID|From|To |
|1|1 |1 |1 |NULL|
|2|2 |1 |1 |1 |
|3|3 |2 |1 |NULL|
|4|4 |3 |1 |NULL|
|5|2 |2 |2 |NULL|
|6|5 |2 |5 |NULL|
Revisions: (comments here for illustration)
|Revision|Comments |
|1 |Initial dataset |
|2 |Lois divorced Fred and moved in with Jim |
|3 |Sunshine changed her name to Daisy May |
|4 |Daisy May's house was renumbered by the fire dept for 911 rules|
|5 |Lois and Jim had a baby named Connor |
I've been thinking about how to apply data versioning to a relatively simple database I have, and figured I should do something like is mentioned in Jim T's post where there are global revision #s (e.g. like in Subversion or Mercurial) and each database record has a validity interval.
Example:
Create a person.
|Name|D.O.B |Telephone|From|To | |Fred|1 april|555-29384|1 |NULL|Update tel no.
|Name|D.O.B |Telephone|From|To | |Fred|1 april|555-29384|1 |1 | |Fred|1 april|555-43534|2 |NULL|Delete fred:
|Name|D.O.B |Telephone|From|To | |Fred|1 april|555-29384|1 |1 | |Fred|1 april|555-43534|2 |2 |
Are there downsides to this approach? It doesn't seem that complicated.
The only thing I can think of, is that it seems like it would have subtle effects on primary keys other than an autoincremented record number which is independent and unrelated to the data. For example, if you had data like this:
Person: (primary key = PersonID which is an autoincrementing integer)
|PersonID|Name|Telephone|
|1 |Fred|555-2938|
|2 |Lois|555-2939|
|3 |Jim |555-1000|
Home: (primary key = HomeID which is an autoincrementing integer)
|HomeID|Address |
|1 |123 Elm St. |
|2 |456 Maple Ave.|
PersonHome: (primary key = person ID and home ID)
|PersonID|HomeID|
|1 |1 |
|2 |1 |
|3 |2 |
then you can't just add the From and To fields above, since you ruin the uniqueness of the primary keys. Instead I would probably have to do something like this (with appropriate indices added to replace the function of the preceding primary keys):
Person: (primary key = K which is an autoincrementing integer)
|K|PersonID|Name |Telephone|From|To |
|1|1 |Fred |555-2938 |1 |NULL|
|2|2 |Lois |555-2939 |1 |1 |
|3|3 |Jim |555-1000 |1 |NULL|
|4|4 |Sunshine |555-2000 |1 |2 |
|5|2 |Lois |555-1000 |2 |NULL|
|6|4 |Daisy May|555-2000 |3 |NULL|
|7|5 |Connor | |5 |NULL|
Home: (primary key = K which is an autoincrementing integer)
|K|HomeID|Address |From|To |
|1|1 |123 Elm St. |1 |NULL|
|2|2 |456 Maple Ave.|1 |NULL|
|3|3 |789 Vista Dr. |1 |3 |
|4|3 |104 Vista Dr. |4 |NULL|
PersonHome: (primary key = K which is an autoincrementing integer)
|K|PersonID|HomeID|From|To |
|1|1 |1 |1 |NULL|
|2|2 |1 |1 |1 |
|3|3 |2 |1 |NULL|
|4|4 |3 |1 |NULL|
|5|2 |2 |2 |NULL|
|6|5 |2 |5 |NULL|
Revisions: (comments here for illustration)
|Revision|Comments |
|1 |Initial dataset |
|2 |Lois divorced Fred and moved in with Jim |
|3 |Sunshine changed her name to Daisy May |
|4 |Daisy May's house was renumbered by the fire dept for 911 rules|
|5 |Lois and Jim had a baby named Connor |
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我自己也在思考同样的问题！
from|to 构造的缺点是，当您想要添加新版本的记录时，需要更新和插入。 我还担心这个构造具有双重含义，它告诉您版本，例如 x | null 表示最近的，它告诉您记录状态，例如 x | x表示删除。
历史智慧建议使用日期 http:// /www.dbforums.com/database-concepts-design/1641734-data-record-versioning-how-implement.html
当您将数据拉入对象模型时，使用日期并不是最好的事情。
最近，双表方法受到青睐 数据库 - 数据版本控制
我对此也不太热衷由于需要维护重复的表。
如果您修改解决方案以具有版本列和状态列，这会起作用吗？ 最高值的版本号是最新的，记录状态是最新版本。
还在思考...
Pondering the same issue myself!
the from|to construct has the disadvantage of requiring an update and an insert when you want to add a new version of the record. I'm also concerned that this construct has dual meaning, it tells you the version, e.g. x | null means most recent, and it tells you the record status, e.g. x | x means deleted.
Historical wisdom suggests using dates http://www.dbforums.com/database-concepts-design/1641734-data-record-versioning-how-implement.html
Working with dates is not the neatest thing to do when you pull your data into your object model.
More recently the two table approach has been favoured Database - Data Versioning
I'm not too keen on this either due to the need to maintain duplicate tables.
If you modified your solution to have a version column and a status column would that work? Highest valued version number is most recent, status of record is in the most recent version.
Still pondering ...