Data structure for versioned records

The modifed version of the db clone is to have your code table to be (release_id, code, ...) every time there's a new version, you copy all rows from the release_id you want to clone, and apply changes on top of that. Each release_id has a full copy of the codes, so getting a specific version is WHERE release_id = xyz. If you need to compare 2 releases to look for differences, it's just a full outer join. If you want to look how one code changes over time, you're just selecting that code and applying some window functions to weed out rows where there was no change from the prior row.

I had project where the the PM didn't want to look at data modeling tools, but wanted to track changes to tables/columns/constraints over time, and be able to feed sql into some free online diagraming software. We just took snapshots of the important catalog tables, and just slapped each one with a version. It was very straight forward, and disk is cheap.

A middle ground between your individual row version would be to store the data like you are now, and then materialize the results from your row versioned table to look like the results from the clone version once all your changes have been made. That way you're only doing your min/max type stuff in one place, and everyone downstream of that can just select a single version and not have to care about the other logic.

WITH t(id, rev_id, name, ver) AS 
    (VALUES (1,1,'A','v1'), (1,2,'A','v2'), (2,3,'B','v2'),(3,4,'C','v1'),(4, 5, 'D', 'v3'),(1,6, 'A', 'v4'))
SELECT
    view_version
    , id
    , name
    , rev_id
    , ver
    , added_in_ver 
FROM (
    SELECT 
        *
        , rev_id = max(CASE WHEN ver <= view_version THEN rev_id END) OVER (PARTITION BY view_version, id) AS is_lastest
    FROM (
        SELECT
            t.id
            , t.rev_id
            , t.name
            , t.ver
            , min(t.ver) OVER (PARTITION BY t.id ORDER BY t.rev_id) AS added_in_ver
            , v.ver AS view_version
        FROM
            t
            CROSS JOIN (SELECT DISTINCT ver FROM t) v
    )
    WHERE added_in_ver <= view_version
)
WHERE is_lastest
ORDER BY view_version, id;

VIEW_VERSION|ID|NAME|REV_ID|VER|ADDED_IN_VER|
------------+--+----+------+---+------------+
v1          | 1|A   |     1|v1 |v1          |
v1          | 3|C   |     4|v1 |v1          |
v2          | 1|A   |     2|v2 |v1          |
v2          | 2|B   |     3|v2 |v2          |
v2          | 3|C   |     4|v1 |v1          |
v3          | 1|A   |     2|v2 |v1          |
v3          | 2|B   |     3|v2 |v2          |
v3          | 3|C   |     4|v1 |v1          |
v3          | 4|D   |     5|v3 |v3          |
v4          | 1|A   |     6|v4 |v1          |
v4          | 2|B   |     3|v2 |v2          |
v4          | 3|C   |     4|v1 |v1          |
v4          | 4|D   |     5|v3 |v3          |

[removed]