将截至日期的对象图存储在数据库中
我将尽力保持这个问题数据库不可知，但我有一个有趣的问题需要解决，我想我应该开放发言以征求建议和反馈。
我需要能够从提要源下载数据并将其存储在某种数据库中，数据需要合并到现有数据中，并且我需要能够查询任何给定日期的数据。这是我想谈论的粗体部分。
从本质上讲，这个问题归结为我需要将对象图保存到 OLTP 数据库并能够临时查询它。
在一个表的简单情况下，这个问题非常简单，您有一个日期范围指示记录的有效时间跨度，然后传入一个截止范围并仅选择对该时间点有效的行。当您有不止一张桌子时，问题就会出现。
我们以有两个表 Order-*Item 的情况为例。
当我们查询订单时，我们可以将截至日期的更改应用到项目表中。一切都很好，但是如果我们想修改订单怎么办？现在我们需要复制订单行，设置日期范围，以便将新行上的有效起始时间和新行上的有效终止时间设置为现在。我们还必须复制这些项目，或者如果我们更改模型，则复制对项目的引用。
即使在这个简单的情况下，事情也开始变得复杂。
我的问题更加严重，因为我有一个自引用对象图，因此要使用上述模型，您将拥有 Order-*Item-*Order。
你会怎么办？当您需要行和临时查询的版本控制时，如何构建数据库？
I'm going to try to keep this question database agnostic, but I have an interesting problem that I need to tackle and I thought I'd open up the floor for suggestions and feedback.
I need to be able to download data from a feed source and store it in a database of some kind, the data needs to be merged into the existing data and I need to able to query for the data as of any given date. It's the part in bold that I'd like to talk about.
Essentially what this problem boils down to is that I need to persist an object graph to an OLTP database and be able to query it temporally.
In the simple case of one table this problem is very simple, you have a date range indicating the valid time span for the record and then you pass in an as of range and only select rows that are valid for this point in time. The issues rise when you have more than one table.
Let's take the case of having two tables, Order-*Item.
When we query for an order we can apply the same as of date changes to the item table. All is well, but what happens if we want to modify an order? Now we need to copy the order row, set the date ranges so the valid from on the new row and the valid to on the new row is set to now. We also have to copy the items, or if we change our model copy the references to the items.
Even in this simple case things are starting to get complicated.
My problem is exacerbated because I have a self-referential object graph, so to use the above model you'd have Order-*Item-*Order.
What would you do? How do you structure your databases when you need versioning of rows and temporal queries?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
回到过去，用 SQL 开发面向时间的数据库应用程序是最好的时态数据库的信息源。该书于 1999 年出版，版权已归作者所有，链接可指向该书的 PDF 版本。请在此处查看他的更多出版物以及压缩内容的链接CDROM 的。
Back in the day, Developing Time-Oriented Database Applications in SQL was the best source of info for temporal databases. Published in 1999, the copyright has reverted to the author, and the link goes to his PDF version of the book. Look here for more of his publications, and for a link to the compressed content of the CDROM.