intro |  html |  css |  design |  scripting |  dhtml |  server 

intro
html
css
design
scripting
dhtml
server

Designing & Creating the Guestbook Database

This page covers:
   Designing the Database
   Creating the Database in MySQL

Designing the Database

Here are the fields we will use in the database:

Name Column Name Data Type Restrictions
Id gbId INT NOT NULL, AUTO INCREMENT
Name gbName VARCHAR(50) -
Email gbEmail VARCHAR(100) -
Comment gbComment TEXT NOT NULL
Date gbDateAdded DATETIME NOT NULL

For Column Names, I use a standard I frequently use in real life - the "gb" prefix just stands for 'guestbook' and is followed by the field name. For more information on correct naming of tables and columns, visit the mysql.com manual.

Name and Email are optional fields because we decided users could anonymously post to the guestbook. Comment is a NOT NULL field because there's no point in saving the guestbook entry if it is empty. Date is NOT NULL because all entries should be timestamped. Validation needs to happen on the front end (in this case, we'll be using javascript for validation) because even though we specify a field to be NOT NULL in MySQL, the database doesn't enforce that data integrity.

We will also need to decide what type each column with be. MySQL supports multiple types falling into 3 general categories: numeric types, date & time types, and string (character) types. For simplicity's sake, we will only be using the character type, most notably the variable length character type (VARCHAR) since most fields will be of different lengths. The VARCHAR type can have a length of 1-255 characters. That might be too small for our comments field, so for that field, we will use the TEXT type which allows 65535 characters (2^16 -1).

If you do not understand data types, view this brief overview on data types for help.

Creating the Database in MySQL

In MySQL, you should create a new database (or if you already have an existing database for your site, you can just create a new table). I already have an existing database so I'll just create a new table:


create table guestbook (
	gbId INT NOT NULL AUTO_INCREMENT,
	gbName VARCHAR(50),
	gbEmail VARCHAR(100),
	gbComment TEXT NOT NULL,
	gbDateAdded DATETIME NOT NULL,
	PRIMARY KEY(gbId));
	

Then run the script and create the table.

 
hardCoder.com © 1999-2011. all rights reserved. // site created and maintained by kathy ahn