Database versioning

Question

I have made few projects (CMS and EC system) that required to have some data versioned.

Usually I come with that kind of schema

+--------------+
+ foobar       +
+--------------+
+ foobar_id    +
+ version      +
+--------------+

it worked great but I am wondering if there is a better to way do it. The main problem with that solution you have to always use subquery to get the latest version.

i.e.:

SELECT * FROM foobar WHERE foobar_id = 2 and version = (SELECT MAX(version) FROM foobar f2 WHERE f2 = 2)

This render most of the queries more complicate and also have some performance drawbacks.

So it would be nice if you share your experience creating versioned table and what kind of pro and cons of each method.

Thanks

Answer 1

I prefer to have historical data in another table. I would make foobar_history or something similar and make a FK to foobar_id. This will stop you from having to use a subquery all together. This has the added advantage of not polluting your primary data table with the tons of historical data you probably don't want to see 99% of the time you're accessing it.

You will likely want to make a trigger for updating this data though, as it would require you to copy the current data in to _history and then do the update.