Revisions: algorithm and data structure

Question

Hi, I need ideas for structuring and processing data with revisions. For example, I have a database of objects (e.g. cars). Each object has a number of properties, which can be arbitrary, so there's no a set schema to describe these objects. These objects are probably saved as key-value pairs.

Now I need to change property of an object. I don't want to completely rewrite it - I want to be able to go back and see history of changes to these properties, that's why I want to add new property and keep the old one (so I guess a timestamp would do the job of telling which property is the latest).

At the same time I want to be able to get info about any object in a snap, with only latest versions of each of the properties.

Any ideas what would be the best approach? At least please point me in the right direction. Thanks!

Answer 1

One possibility is to have a object and property table (which you may have already). The create the association table containing ID ObjectId PropertyId Value Date_Added/version_number (as per your choice if you want to use timestamp or sequence column)

As per your problem you would always add to association table and never update it.

When you want to get a snapshot of latest object properties, you need to do a DISTINCT query for properties ordered by date_added/version number. For a given property checking history is straightforward as well.

I hope this helps