Today is the first installment in the practical side of building your SEO CMS, rather than simply trying to explain what we are going to be building. As with any construction you are looking to build the foundations first, and then move on towards the heights of search engine rankings.
In this article I am going to be discussing database design, and will give you some SQL which you can run at your own risk.
Database design is one of the key areas of your website, to put it simply it is the foundation of your website. Throughout this article we will be making small modifications to the SQL structure to cover the designs. However what do you want from the database?
- Reliability – The last thing any website owner will want is for their website to be unavailable to the end user. And especially from a SEO perspective if the website is unavailable due to the database being poorly designed.
- Speed – Another key thing you want to avoid is long complex queries. If you look at the design of your website properly, all data should come together in the minimum number of queries, as well as the minimum memory hungry joins. And also consider the fact the last thing you want is for a ceartesian product join in any database query you design
- Ease of access – I am quite familiar that a number of you out there will not have worked in any arena’s where complex SQL queries would be used, and therefore it should always be clear to you how the query works.
Also looking at the requirements you have many options with regards to your database design, do you split your pages and categories into two tables? do you then also put all of the header information into a third table due to normalisation of potentially repeated data, However purely to keep this guide workable for even the most novice of web developers, we will keep all of the data in one table.
Therefore if we are using a PHP / MySQL structure (There will be more on why in a later article) you would be looking at requiring the following information:
- PageID: This is a unique reference for each page you can use internally within your system.
- Title: This is to store the page title in
- MetaKeywords: This is to store the page Keywords in
- MetaDescriotion: This is to store the page description in
- Content: This will store the content of the page (this is what makes page a different from page b)
- Filename: Just as if you was creating the page from scratch in frontpage, each file needs a name
- link anchor: This will allow you to customise the anchor text which links to a specific page.
And for your pleasure, here is the DDL which will create a basic table.
CREATE TABLE `pages` ( `PageID` int(10) unsigned NOT NULL auto_increment, `Title` varchar(255) NOT NULL, `MetaKeywords` varchar(255) NOT NULL, `MetaDescription` varchar(255) NOT NULL, `Content` longtext NOT NULL, `Filename` varchar(255) NOT NULL, `LinkAnchor` varchar(255) NOT NULL, PRIMARY KEY (`PageID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
