MySQL Database Design

• Web Designer
• Skills
• Portfolio
• Tutorials
• Contact

Link Exchange System with Dreamweaver CS3, PHP, MySQL and CSS Part 1

<< Defining project | Connecting to database >>

Database design
For the purpose of this tutorial I have created the database db_links with three tables: tbl_links, tbl_category and tbl_admin.

Open notepad or any other text editor and copy and paste the following SQL statement:

DROP DATABASE IF EXISTS dblinks;

CREATE DATABASE dblinks;

USE dblinks;

CREATE TABLE tbl_admin (
adminid tinyint(3) unsigned NOT NULL auto_increment,
adminusername varchar(15) NOT NULL,
adminpassword varchar(15) NOT NULL,
PRIMARY KEY (adminid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO tbl_admin (adminid, adminusername, adminpassword)
VALUES (1,'admin','password');

CREATE TABLE tbl_categories (
catid int(10) unsigned NOT NULL auto_increment,
catname varchar(30) NOT NULL,
PRIMARY KEY USING BTREE (catid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE tbl_links (
linkid int(10) unsigned NOT NULL auto_increment,
linktitle varchar(45) NOT NULL,
linkurl varchar(255) NOT NULL,
linkrecip varchar(255) NULL,
linkemail varchar(100) default NULL,
linkapproved enum('n','y') NOT NULL default 'n',
linkcat int(10) unsigned NOT NULL,
linkdate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
linkdescr varchar(255) NOT NULL,
PRIMARY KEY (linkid),
KEY FK_tbl_links_1 USING BTREE (linkcat),
CONSTRAINT FK_tbl_links_1 FOREIGN KEY (linkcat) REFERENCES tbl_categories (catid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Replace "admin" and "password" with your chosen login name and password.
These will be your login details to the control panel where you will be managing your links.

Once you’ve done it save the file into C:/ and call it links.sql – remember to give it .sql extension – to do it with notepad you will need to choose All Files from Save as type dropdown menu (fig. 07).

Fig. 07

Now go to Start > Run and type cmd. The command window will open. Type the following command: mysql –u root –p and press Enter. You will be prompted to provide a password – type the password which you have chosen for root user when you were installing MySQL and hit Enter again. Now you should see the screen which looks similar to the one in fig. 08.

Fig. 08

To see what databases you have currently created on your server type the following command:

SHOW DATABASES;

The standard three which come together with the installation of MySQL 5.0.41 are: information_schema, mysql and test.
To create our database which we will be using to store our partner’s links, type the following command and click Enter:

\. c:/links.sql

This will execute commands included in our links.sql and create our db_links database.
To ensure that the database has been successfully created type again:

SHOW DATABASES;

You should now see our dblinks database listed in the list of databases (fig. 09).

Fig. 09

Our database is now ready. Let’s move on and create the connection to our database using Dreamweaver.

<< Defining project | Connecting to database >>