Version Control for Content

• Multiple people working on the same document can lead to a losing a lot of data:
  1. Ada and Bob get a local copy.
  2. Ada edits and uploads.
  3. Bob edits and uploads.
• Best solution is to keep all copies around, to facilitate reversions and merging.

create table content_raw (
        -- the combination of these two is the key
        content_id              integer primary key,
        version_number          integer,

        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,
        -- some of our content is geographically specific 
        zip_code                varchar(5),
        -- a lot of our readers will appreciate Spanish versions
        language                char(2) references language_codes,
        mime_type               varchar(100) not null,
        one_line_summary        varchar(200) not null,
        -- let's use BLOB in case this is a Microsoft Word doc or JPEG
        -- a BLOB can also hold HTML or plain text
        body                    blob,
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
        primary key (content_id, version_number)
);

• To get all versions we would have to use the clumsy:

-- note the use of MAX on VARCHAR column; this works just fine

select content_id, max(zip_code)
        from content_raw
        where content_id = 5657
        group by content_id