Relational Database Design - Page 1 by
Brian Haveri aka bwh2 |
2 October 2006
Many web designers understand what a database is. Some have even
written queries to access a database or even
designed their own database. Unfortunately, many web
designers suffer from unnecessary problems and setbacks
because they do not understand the basics of relational
database design. This tutorial teaches the basics of
relational database design. Along the way, SQL tips and best
practices will also be shown and explained. In addition, you
will be introduced to important database terminology. As an
example, this tutorial will design a database to hold works
for a web design portfolio.
A few notes before we start: All table names will begin with
"tbl_". The database used in this example is MySQL version
4.x. The queries shown should also work in MySQL version 5.
MySQL is often the choice of web designers needing database
functionality. Other SQL databases such as MSSQL Server may
use slightly different query syntax, but the relational
database design concepts shown will still apply.
Define What the Database Will
Do
The database will hold portfolio works for my web design
portfolio. The database will hold information about each
work such as title, category, technology used, and
client name.
Create the Database
Before we can get started designing our database, we
have to create it. Below is the SQL to create the
database. Many hosts will not allow you to create a
database directly from SQL, so you may have to use your
administration panel to create the database. In that
case, ignore the following code.
/***** CREATE the database *****/
CREATE DATABASE `kirupa_portfolio` DEFAULT
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
/***** End CREATE *****/
SQL Tip
Within SQL, comments are held inside /* comment */
tags. Tick marks (`) can be used around database,
table, and column names. Tick marks are not required
unless you are attempting to query a database,
table, or column named with a reserved word (such as
"date" or "select").