PHP Archive

The Articles Table

The articles table is the heart of the system, storing all articles for retrieval via various utility programs. The articles table needs to store the following pieces of data:

• An index to uniquely identify the article

• The publication date of the article

• The category to which the article is assigned

• The author of the article

• A short title for the article

• The full text of the article

• A field to identify the article as a draft or a final copy

The MySQL create statement for this table is as follows:

CREATE TABLE 'articles* (

*idx* int(10) unsigned NOT NULL auto_increment,

*pubdate* datetime NOT NULL default '0000-00-00 00:00:00',

*cat* int(10) unsigned NOT NULL default '0',

'author* int(10) unsigned NOT NULL default '0',

'title' varchar(80) NOT NULL default '',

'article' text NOT NULL,

'publish' tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (*idx*) ) TYPE=MyISAM;

The category and the author fields are numeric, designed to hold the index corresponding to the category or author entry in their respective tables. This adheres to good database design, as discussed in the "Database Normalization" section of Session 12.

However, this brings up an important design consideration — once created, a category or author cannot be deleted from the database. For example, suppose that the category Sports is assigned to several articles and has an index of 3. If Sports is ever deleted from the categories table, several articles reference invalid category fields. You could spend more time designing the system to handle this eventuality, but for this exercise, assume that categories and authors cannot be deleted.

0 0