Starting With MySQL - MySQL

First of all we have to build the table that will hold all our pages. Throughout this tutorial I will use page and database record interchangeably because the pages of this content management system are stored as database records.

CREATE TABLE `tblpages` (
  `id` int(11) NOT NULL auto_increment,
  `parentid` int(11) default NULL,
  `title` varchar(200) default NULL,
  `body` text,
  `createddate` datetime default NULL,
  `updatedate` datetime default NULL,
  `status` char(1) default NULL,
  `hierarchy` varchar(255) default NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
  ) TYPE=MyISAM

I will walk you through what the fields are for and what they will help us to accomplish.

The ID is going to be the unique key for each page. You will be able to call a specific page with the id number.

ParentID is going to allow us to do the site map and breadcrumb trail and also the side menu. This field stores the ID number of the page that is it's parent. It is hierarchical data in that it represents a parent child relationship. Lets say a page with an id of 3 has a page off of it with an ID of 4. Page 3 would be the parent and page 4 would be the child.

The Title field will be used in several places. First it can be used on the top of every page as (you can probably guess this one) a title. It will also be used in the menu and the breadcrumb list, so when you are entering titles, you don't want them to be too long.

The Body is for whatever you want to put on the page.

The CreatedDate will store the date and time the page was originally created.

The UpdateDate will get updated every time you modify the page. We will use this field in the site map so that people will readily be able to see how up to date your web site is.

Status is used to delete pages. The value will either be A for active and D for deleted. All the queries to display a page will check to see if the page is active.

The Hierarchy field is build to minimize the server overhead and make the whole page easier to present. All the pages will be organized hierarchically starting with the home page. Instead of running some complex queries every time the page loads, we are going to create a list of the hierarchy when the page is created. We will use the page ID. Once created the Hierarchy will look something like this "1~3~5~12". What that means is that the page with ID equal to 1 (the home page) has a child page off of it with an ID of 3, which has a child page off of it with an ID of 5, which has a child page off of it with an ID of 12.