Database model with a history of changes?

Asked by fffv

It is required to develop a database model (or data warehouse) in which not only the current state would be stored, as in ordinary operational databases, but also the entire history of changes of any attributes of any entity over time in order to know everything on any time slice, as done with aggregated data in data warehouses. But if the text attributes and nothing to aggregate, it is unclear how to build such a model. Something close to what I need, probably, temporal databases. I would like to know references to books, scientific articles, open projects on this topic.


Well, as for the general storage of data with history in storage, you can start with Slowly changing dimensions: wiki, respectively, any book on HD will contain information on the topic and, probably, one of the types will work.
karin adams
and not easier to save the changes made in a separate history database?
mike lawrence
I once dealt with this topic, it was very popular in the nineties. But all the sources have already lost, unfortunately.
Search for keywords "time dimension information systems" - deborah simionato
v ronique b
You can do this as follows: at the very top of the abstraction, make a data layer that will hide all work with versions. This way you can work with data using standard operations without worrying about versions.

Then we have only the essence (for simplicity, let it be one and simple) and it can change. We can choose to keep copies or only changes and calculate the desired state when prompted.

In the first case, we need to keep copies of the entities and keep track of versions, for this it is enough to determine such data in the entity itself and mark the latest version as final.

In the second case, we will need a mechanism for calculating changes between versions and applying them to the entity itself, when requested.

The easiest script. Get the latest version of entity A, edit and save:
1) refer to the service layer (knows nothing about versions) with a request to take entity A,
2) the data layer requests the entity A and adds an item about the latest version to the request,
3) receives an entity and gives it to a service that gives it to us,
4) change the entity and ask the service layer to save it,
5) the data layer marks the new version as the last, and this status is removed from the old one.
6) two entities are saved, but for us it will look like one entity, since we will operate with only one entity.
CauchDB can be suitable. It just stores all changes data. Previous values ​​can be referenced by version number.
gena khodos
text attributes are just not a problem - look towards DVCS where they are quickly and compactly stored diffs.
With other things it is much more difficult in terms of compactness - you have to store them entirely.
C # + sqlite, a few questions :: How materials from & quot; Sandboxes & quot; Add to favorites? :: UTorrent distributes more than the limit? :: Rtorrent does not move files after they are downloaded, if the torrent is not downloaded completely, but only some files? :: Monitoring network traffic
Leave Repply for Database model with a history of changes?
Useful Links