How to Use a Database to Manage HTML5 and CSS3 Content

If you want to make your own data based CMS to manage HTML5 and CSS3 content, you need to move from storing data in files to storing in a relational database. Each page in a content management system is often the same structure, and only the data is different. What happens if you move away from text files altogether and store all the content in a database?

The data structure might be defined like this in SQL:

DROP TABLE IF EXISTS cmsPage;
CREATE TABLE cmsPage (
 cmsPageID INTEGER PRIMARY KEY AUTO_INCREMENT,
 title VARCHAR(30)
);
DROP TABLE IF EXISTS cmsBlock;
CREATE TABLE cmsBlock (
 cmsBlockID INTEGER PRIMARY KEY AUTO_INCREMENT,
 blockTypeID INTEGER,
 title VARCHAR(50),
 content TEXT,
 pageID INTEGER
);
DROP TABLE IF EXISTS blockType;
CREATE TABLE blockType (
 blockTypeID INTEGER PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(30)
);
DROP VIEW IF EXISTS pageView;
CREATE VIEW pageView AS
 SELECT
 blockType.name as 'block',
 cmsBlock.title as 'title',
 cmsBlock.content as 'content',
 cmsBlock.pageID as 'pageID',
 cmsPage.title as 'page'
 FROM
 cmsBlock, blockType, cmsPage
 WHERE
 cmsBlock.blockTypeID = blockType.blockTypeID;
INSERT INTO cmsPage VALUES (
 null,
 'main page'
);
INSERT into blockType VALUES (null, 'head');
INSERT into blockType VALUES (null, 'menu');
INSERT into blockType VALUES (null, 'content1');
INSERT into blockType VALUES (null, 'content2');
INSERT into blockType VALUES (null, 'footer');
INSERT INTO cmsBlock VALUES (
 null,
 1,
 'it's a binary thing',
 null,
 1
);
INSERT INTO cmsBlock VALUES (
 null,
 2,
 'menu',
 '
   <ul>
   <li><a href = "dbCMS.php?pageID=1">one</a></li>
   <li><a href = "dbCMS.php?pageID=2">two</a></li>
   <li><a href = "dbCMS.php?pageID=1">three</a></li>
  </ul>
 ',
 1
);
INSERT INTO cmsBlock VALUES (
 null,
 3,
 'Book I - Creating the HTML Foundation',
 '
<ol>
 <li>Sound HTML Foundations</li>
 <li>It's All About Validation</li>
 <li>Choosing your Tools</li>
 <li>Managing Information with Lists and Tables</li>
 <li>Making Connections with Links</li>
 <li>Adding Images</li>
 <li>Creating forms</li>
</ol>
 ',
 1
);
INSERT INTO cmsBlock VALUES (
 null,
 4,
 'Book II - Styling with CSS',
 '
<ol>
 <li>Coloring Your World</li>
 <li>Styling Text</li>
 <li>Selectors, Class, and Style</li>
 <li>Borders and Backgrounds</li>
 <li>Levels of CSS</li>
</ol>
 ',
 1
);
INSERT INTO cmsBlock VALUES (
 null,
 5,
 null,
 'see <a href = "http://www.aharrisbooks.net">aharrisbooks.net</a> for more information',
 1
);

This structure has three tables and a view:

• The cmsPage table: Represents the data about a page, which currently isn’t much. A fuller version might put menu information in the page data so that the page would “know” where it lives in a menu structure.

• The cmsBlock table: Represents a block of information. Each block is the element that would be in a miniature HTML page in other systems. This table is the key table in this structure because most of the content in the CMS is stored in this table.

• The blockType table: Lists the block types. This simple table describes the various block types.

• The pageView view: Ties together all the other information. After all the data is loaded, the pageView view ties it all together.