Specifications

Designing the Database
Listing 26.2 shows the SQL queries used to create the database for the content system. This
listing is part of the file create_database.sql. The file on the CD also contains queries to
populate the database with some sample users and stories.
LISTING 26.2 Excerpt from create_database.sql—SQL File to Set Up the Content
Database
drop database if exists content;
create database content;
use content;
drop table if exists writers;
create table writers (
username varchar(16) primary key,
password varchar(16) not null,
full_name text
);
drop table if exists stories;
create table stories (
id int primary key auto_increment,
writer varchar(16) not null, # foreign key writers.username
page varchar(16) not null, # foreign key pages.code
headline text,
story_text text,
picture text,
created int,
modified int,
published int
);
drop table if exists pages;
create table pages (
code varchar(16) primary key,
description text
);
drop table if exists writer_permissions;
Building a Content Management System
C
HAPTER 26
26
CONTENT
MANAGEMENT
SYSTEMS
597
32 7842 ch26 3/6/01 3:36 PM Page 597