Content Management

• Your webapp will have: articles, comments on articles, news, comments on news, questions, answers. How many tables do you need?

create table articles_raw (
        article_id              integer primary key,
        -- who contributed this and when
        creation_user           not null references users,
        creation_date           not null date,
        -- what language is this in?
        -- visit http://www.w3.org/International/O-charset-lang
        -- to see the allowable 2-character codes (en is English, ja is Japanese)
        language                char(2) references language_codes,
        -- could be text/html or text/plain or some sort of XML document
        mime_type               varchar(100) not null,
        -- will hold the title in most cases
        one_line_summary        varchar(200) not null,
        -- the entire article; 4 GB limit
        body                    clob
        --articles go thru an editorial process
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
    );

• Maybe you don't trust that people will always remember to add where editorial_status == 'approved', in this case create a view:

create view articles_approved 
        as
        select * 
        from articles_raw
        where editorial_status = 'approved';