|
MySQL Schema Example For A Content Management System
The following MySQL™ schema is used to drive some of my web sites. Each page is assigned a section number (snum) and a page number (pnum). The first page in each section is assigned a page number of zero, while other pages in that section are assigned a higher numeric value. Utilizing the section number and page number, links to other pages in the currently selected section can be easily queried. The link column contains text used in the A HREF tag, while the heading column is used for both the A HREF title argument and the page title. The content column holds the web page content (text, HTML, or PHP code). If content contains PHP code, then the corresponding eval_content column is set to 1 (and evaluated using PHP when the page is rendered). Columns are available for various META tags are configured, and pages can be set as hidden if they're not ready to be publicly displayed. Pages can be flagged as deleted without actually removing the record from the database. The mli column facilitates making a page available only if the user is currently logged in. Pages requiring a secure https connection can be flagged using the https column. The nav column facilitates overriding the default navigation display, and is seldom used. The changefreq and priority page are used when generating Google Site Maps. The spider column facilitates changing the default META robots tag to indicate noindex and nofollow arguments. The html_1 and html_2 tags (if non-empty) facilitate a mechanism for displaying additional HTML TABLE's in the page header. Page headings and content are configured for MySQL™ fulltext searching. For PHP driven web sites, always set register_globals OFF.
--
-- Table structure for table `web_content`
--
CREATE TABLE `web_content` (
`id` smallint(5) NOT NULL auto_increment,
`snum` smallint(5) unsigned NOT NULL default '0',
`pnum` smallint(5) unsigned NOT NULL default '0',
`link` varchar(30) NOT NULL default '',
`heading` varchar(80) NOT NULL default '',
`content` mediumtext NOT NULL,
`eval_content` tinyint(1) unsigned NOT NULL default '0',
`meta_title` varchar(255) NOT NULL default '',
`meta_desc` text NOT NULL,
`meta_key` text NOT NULL,
`hidden` tinyint(1) unsigned NOT NULL default '0',
`deleted` tinyint(1) unsigned NOT NULL default '0',
`mli` tinyint(1) unsigned NOT NULL default '0',
`https` tinyint(1) unsigned NOT NULL default '0',
`nav` tinyint(1) unsigned NOT NULL default '0',
`changefreq` enum('always','daily','hourly','monthly','never','weekly','yearly') NOT NULL default 'monthly',
`priority` enum('0.0','0.1','0.2','0.3','0.4','0.5','0.6','0.7','0.8','0.9','1.0') NOT NULL default '0.5',
`spider` tinyint(1) unsigned NOT NULL default '1',
`html_1` text NOT NULL,
`html_2` text NOT NULL,
`posted` datetime NOT NULL default '0000-00-00 00:00:00',
`updated` timestamp(14) NOT NULL,
`PRIMARY KEY (`id`),
`KEY `snum` (`snum`),
`KEY `pnum` (`pnum`),
`KEY `hidden` (`hidden`),
`KEY `mli` (`mli`),
`KEY `deleted` (`deleted`),
`FULLTEXT KEY `search_index` (`heading`,`content`)
) TYPE=MyISAM;
PHP Script Example - FULLTEXT Search of MySQL Database Table
MySQL Cheat Sheet
|
