Database Versioning

Question

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

I usually come with such a scheme

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

it worked fine, but I wonder if there is a way to make this better. The main problem with this solution: you should always use a subquery to get the latest version.

ie:

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

This makes most queries more complex and also has some performance flaws.

So, it would be nice if you shared your experience with creating a table with a version and what kind and minuses of each method.

thanks

Answer 1

. foobar_history - FK foobar_id. . , , , , 99% , .

, , _history .

Answer 2

, , , . , foobar, foobar_History foobar, History , . , , , .

Answer 3

, , , . , . , , where.

Pro:

• ()
• , .

:

, .

Pro:

• db- ,

:

• 2 ( ), ( + )
• ​​, . , , , "" .

. , . . , . ( ), , .

? , db, . , . , ( + ). - , , . , , .

. , : ,

Answer 4

, , . , .

Answer 5

version_status /. , , -() , - name + pin, () .

TABLE foobar(foobar_id PK, business_key, version, version_status, .....)

SELECT * 
FROM foobar 
WHERE business_key = 'myFoobar3' AND version_status = 'current'

- - , foobar_id . , ? , , , . , , .

Answer 6

Oracle,

* from ( .* , row_number() over ( foobar_id desc) rn foobar a WHERE foobar_id = 2 ), rn = 1

Answer 7

, , .

- , , , . , current_row , , .

, , , , . , , .

If you are in the world of reporting, then there are many other options that I will not indicate here. Details can be found in data warehouse data modeling books.