Website Redo - The Database

December 15, 2008 by Curt Siters

Information to be captured

• Site Name
• Site URL
• Information about the site, including the technology used
• Testimonial

Additional information desired

• Creation date
• Modification date
• Status within the system
• Operational status

 In the world of databases there is something called Normalization. It is both a process and an end result. The purpose of normalization is to reduced the amount of data in a database to keep the size of the database as small as possible.

This is achieved by placing redundant data into its own table and creating a table, or column (field) in an existing table, that links one table to another. For example (this is a common example):

You have a database for names and addresses. Addresses consist of street addresses, cities and zips. 

So, you would have a names table that consists of first names, last names and column that links to a street address table.

The street address would consist of the street address and a link to the city table. Etc. 

For this project the site name, site url, site description and testimonial are all unique and may be included in one table. The creation date and the modification date may or may not be unique and will also be included in the main table. As will the two status'.

The technology used for each site will not be unique and may be added to in the future so it will be placed into a table by itself.

The question now is do I include the references from the main table to the technology table in the main table or in a separate table? And do I make it reference to the index of the technology table or just import the name of the technology?

In todays world you can do it any multitude of ways:

• In the main table you can create a column for each of the technologies used. This is a possible solution unless you may be adding more technology and would need to add more columns for each new technology. For this project this is a non-starter.
• In the main table you can create a column for the technology and parse it when it is SELECTED. You can store either the names of the technology or a reference to the name in the technology table. The problem here is that you would need to write extra code to extract the information.
• The next possibility would be to create a table that would be linked to the main table and store the name of the technology (brought in from the technology table.) The problem with this is that it does not fully normalize the database in that more data will be replicated many times over - more times than needs to be.
• The final option would be to add a table into which you you would provide a reference to both the main table and technology table. This would fully normalize the database but would also make it more complicated than it needs to be.

I was originally going to use the last method but decided to back off to the third option - it is the best cross between efficiency and simplicity.

The next thing you want to do is determine what you want for indexes, sometimes known as keys - don't create an index for every column as this will slow the database down. Only create indexes for what you will frequently need to access such as, in this case, sitename, siteportfoliostatus and siteupdate. Sitename is a key because I want to be able to quickly generate an alphabetical list of the sites I've done. Siteportfoliostatus is a key because I only want to show the public certain projects and siteupdate is a key because at somepoint I will want to list the sites by order of when I last worked on them.

Finally what I came up with for the database is as follows (this is the structure dump from MySQL):

-- phpMyAdmin SQL Dump
-- version 2.8.0.1
-- http://www.phpmyadmin.net
--
-- Host: your host.com
-- Generation Time: Dec 14, 2008 at 03:47 PM
-- Server version: 5.0.51
-- PHP Version: 4.4.4
--
-- Database: `portfolio`
--
CREATE DATABASE `portfolio` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `portfolio`;

-- --------------------

--
-- Table structure for table `site`
--

CREATE TABLE `site` (
`siteid` smallint(6) NOT NULL auto_increment ,
`sitename` varchar(200) NOT NULL ,
`siteURL` varchar(200) NOT NULL ,
`sitesnapshotname` varchar(100) NOT NULL ,
`sitetext` text NOT NULL ,
`sitetestimonial` text ,
`sitetestimonialname` varchar(60) default NULL ,
`siteportfoliostatus` tinyint(1) NOT NULL default '0' ,
`sitestatus` tinyint(4) NOT NULL default '0',
`sitedate` date NOT NULL ,
`siteupdate` date NOT NULL ,
PRIMARY KEY (`siteid`),
KEY `sitename` (`sitename`,`sitetestimonialname`,`siteportfoliostatus`,
`siteupdate'),
KEY `siteupdate` (`siteupdate`),
FULLTEXT KEY `sitetext` (`sitetext`,`sitetestimonial`,`sitetestimonialname`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------

--
-- Table structure for table `siteTech`
--

CREATE TABLE `siteTech` (
`sitetechsiteid` smallint(6) NOT NULL ,
`sitetechtechtypeid` tinyint(4) NOT NULL ,
PRIMARY KEY (`sitetechsiteid`),
KEY `sitetechtechtypeid` (`sitetechtechtypeid`)
);

-- --------------------

--
-- Table structure for table `techType`
--

CREATE TABLE `techType` (
`techtypeid` tinyint(4) NOT NULL auto_increment ,
`techtypename` varchar(40) NOT NULL ,
PRIMARY KEY (`techtypeid`)
);

###

Curt Siters is an Independent Associate for Pre-Paid Legal Services, Inc. He is also aYoung Living Essential Oils Independent distributor and publishes articles on YourWebReference and at TheVeryEssence. He also does web work such as website design, website maintenance and SEO for websites.

Join me on any of the following sites: LinkedIn, Twitter, friendfeed, Facebook.