Internet Application Dev Lectures <- IAD Lectures <- Sean Forman <- You Are Here

Sean Forman
Dept. of Mathematics and Computer Science
Saint Joseph's University
Philadelphia, PA
Topic: Content Management
Props needed:
Note: this lecture contains freely-available, copyrighted material from Internet Application workbook by Philip Greenspun, Eve Andersson, and Andrew Grumet.

Types of Content

Articles
Comments on articles
News
Comments on News
Discussion (question and answer)
Photos
User data regarding potential mates

Store them Separate or Together

We have many types of data should they be stored together or separately.
Separate: they have different aspects
- Comments refer to something
- Articles undergo multiple revisions
- News may expire
- Discussions have a back and forth.
Together: prevents six different sets of scripts
- Approval
- Deletion
- Updates
- Insertions

A Simple Data Model

A very simple data model for articles.

These examples are for a content site, so they will vary depending on the type of site that you have. An example of a site like this would be The New Yorker, but with comments on the articles.

create table articles (
	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
);

Must be aware of the data types. MySQL data types.

What if users aren't allowed to see articles until they have been approved?

create table articles (
	article_id		integer primary key,
	creation_user		not null references users,
	creation_date		not null date,
	language		char(2) references language_codes,
	mime_type		varchar(100) not null,
	one_line_summary	varchar(200) not null,
	body			clob,
	editorial_status	varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

Approval with views

This way all of your programmers will have to remember to search only for articles approved.
Someone will forget.
Can get around this with views.

Views are a way to add a level of abstraction to the database.

create table articles_raw (
	article_id		integer primary key,
	...
	editorial_status	varchar(30)
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

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

select * from articles_approved;

instead of

select * from articles_raw where status='approved';

Designers and application engineers then interact with the view rather than the table and they never have access to the unapproved articles.
Could even encode this on a permissions level.
Different scripts are coding as different users, so some software will have access to some tables and some won't.
Admin user, User user, etc.

Comments

create table comments_on_articles_raw (
	comment_id		integer primary key,
	-- on what article is this a comment?
	refers_to		not null references articles,
	creation_user		not null references users,
	creation_date		not null date,
	language		char(2) references language_codes,
	mime_type		varchar(100) not null,
	one_line_summary	varchar(200) not null,
	body			clob,
	editorial_status	varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

create view comments_on_articles_approved 
as
select * 
from comments_on_articles_raw
where editorial_status = 'approved';

This table differs from the articles table only in a single column: refers_to. How about combining the two:

create table content_raw (
	content_id		integer primary key,
	-- if not NULL, this row represents a comment
	refers_to		references content_raw,
	-- 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,
	one_line_summary	varchar(200) not null,
	-- the entire article; 4 GB limit
	body			clob,
	editorial_status	varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

-- if we want to be able to write some scripts without having to think
-- about the fact that different content types are merged

create view articles_approved 
as
select * 
from content_raw
where refers_to is null
and editorial_status = 'approved';

create view comments_on_articles_approved 
as
select * 
from content_raw
where refers_to is not null
and editorial_status = 'approved';

News Information

News is different from articles.
Often topical or date-sensitive.
Press Releases
News Stories
These generally don't have ongoing value.

For press releases, we may want to time its release.

create table content_raw (
	content_id		integer primary key,
	refers_to		references content_raw,
	creation_user		not null references users,
	creation_date		not null date,
  	release_time		date,	-- NULL means "immediate"
	expiration_time		date,	-- NULL means "never expires"
	language		char(2) references language_codes,
	mime_type		varchar(100) not null,
	one_line_summary	varchar(200) not null,
	body			clob,
	editorial_status	varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

How do we know what is news and what is an article, since we could do the same for articles.

create table content_raw (
	content_id		integer primary key,
	content_type		varchar(100) not null,

	refers_to		references content,
	creation_user		not null references users,
	creation_date		not null date,
	release_time		date,
	expiration_time		date,
	language		char(2) references language_codes,
	mime_type		varchar(100) not null,
	one_line_summary	varchar(200) not null,
	body			clob,
	editorial_status	varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

create view news_current_and_approved
as
select *
from content_raw 
where content_type = 'news'
and (release_time is null or sysdate >= release_time)
and (expiration_time is null or sysdate  <= expiration_time)
and editorial_status = 'approved';

The inclusion of NULL in the above view shows that we have to be careful with how NULL is handled in relationships. sysdate > NULL would return FALSE.

Discussion

The general idea works well for discussion, but has some additional issues.
How many forums?
How will threads work?
We'll flesh these out in the discussion section.

Why Not Flat Files

Could just create flat files of html.
Some people could use notepad.
Some people could use MS Frontpage.

Why Not Flat Files, part II

Problems
Writing over each other's files.
Editor, author, designer, information architect may all have input.

Why Not Flat Files, part III

Modularity.
If you have 1000 pages, you will have to change the logo in 1000 places.
Sell a new ad location on the site.
For a consistent look and feel that is easy to update, you will need to generate each page from a small set of scripts.
To its end point you should build each page from a DB with a script.

About this document ...

This document was generated using the LaTeX2HTML translator Version 2002 (1.62)

The command line arguments were:
latex2html lecture_5 -split 0 -init_file ./.latex2html-initOne -t 'Content Management '

The translation was initiated by Sean Forman on 2005-09-14