Database model with change history?

Question

Database

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

Answer 1

Well, as for storing data with a history in storages in general, you can start with Slowly changing dimensions: wiki , respectively, any book on data storage will contain information on the topic and, probably, one of the types will do.

Answer 2

Isn't it easier to save the changes made to a separate history database?

Answer 3

I once dealt with this topic, it was very popular in the nineties. But now I have lost all the sources, unfortunately.

Answer 4

You can do it 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.
Further, 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 requested.
In the first case, we need to store copies of entities and keep track of versions, for this it is enough to define such data in the entity itself and mark the latest version as final.
In the second case, a mechanism will be needed to calculate changes between versions and actually apply them to the entity upon request.
The simplest scenario. Take the latest version of entity A, change and save:
1) we turn to the service layer (knows nothing about versions) with a request to take entity A,
2) the data layer requests entity A and adds a clause about the latest version to the request,
3) receives the entity and gives it to the service that gives it to us,
4) we change the entity and ask the service layer to save it,
5) the data layer marks the new version as the latest, and this status is removed from the old one.
6) two entities are saved, but for us it will look like one entity, because we will operate with only one entity.

Answer 5

CauchDB might be suitable. It just stores all changes data. Previous values ​​can be referred to by version number.

Answer 6

text attributes are just not a problem - look towards DVCS where they are quickly and compactly stored by diffs.
With others it is much more difficult in terms of compactness - you have to store it entirely.