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;
 
create table writer_permissions (
  writer      varchar(16) not null,            # foreign key writers.username
  page        varchar(16) not null             # foreign key pages.code
);

drop table if exists keywords;

create table keywords (
  story       int not null,                    # foreign key stories.id
  keyword     varchar(32) not null,
  weight      int not null
);

grant select, insert, update, delete
on content.*
to content@localhost identified by 'password'; 

We need to store a little information about each writer, including a login name and password, in the writers table. We'll store their full names for displaying after each article and for greeting them when they log in.

The pages table contains the page heading for each page on which stories can be displayed. The writer_permissions table implements a many-to-many relationship indicating for which pages a writer can submit stories.

The stories table contains separate fields for headline, story_text, and picture as discussed previously. The created, modified, and published fields are integer fields and will store the Unix timestamp value of the relevant times.

To create the database, run the following command:

					
mysql -u root < create_database.sql