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?