Version Control for Content

• A better solution is to use two tables:

-- stuff about an item that doesn't change from version to version
create table content_raw (
        content_id              integer primary key,
        content_type            varchar(100) not null,
        refers_to               references content_raw,
        creation_user           not null references users,
        creation_date           not null date,
        release_time            date,
        expiration_time         date,
        mime_type               varchar(100) not null,
        zip_code                varchar(5)
);

-- stuff about a version of an item
create table content_versions (
        version_id              integer primary key,
        content_id              not null references content_raw,
        version_date            date not null,
        language                char(2) references language_codes,
        one_line_summary        varchar(200) not null,
        body                    blob,
        editorial_status        varchar(30) 
              check (editorial_status in ('submitted','rejected','approved','expired')),
        -- audit the person who made the last change to editorial status
        editor_id               references users,
        editorial_status_date   date
);

• We can then get the latest version with

select * 
        from content_versions
        where content_id = 5657
        and editorial_status = 'approved'
        and version_id = (select max(version_id)
                  from content_versions
                  where content_id = 5657
                  and editorial_status = 'approved')

• Notice that it requires scanning all of content_versions.