لطفا منتظر باشید ...
Chapter 5: Relational Database Design
"Getting Started with MySQL" of this book,we've worked with a very simple database of jokes, which is composed of a
single table named, appropriately enough, Jokes.
While this database has served us well as an introduction to MySQL databases,
there's more to relational database design than this simple example illustrates.
In this chapter, we'll expand on our example, and learn a few new features
of MySQL, in an effort to realize and appreciate what relational databases
have to offer.Be forewarned that many topics will be covered only in an informal,
hands-on (i.e. non-rigorous) sort of way. As any computer science major will
tell you, database design is a serious area of research, with tested and mathematically
provable principles that, while useful, are beyond the scope of this text.
If you want more information, stop by http://www.datamodel.org/ for
a list of good books, as well as several useful resources on the subject.
In particular, check out the 5 Rules of Normalization in the Data Modelling
section of the site.
Giving Credit where Credit is Due
To start things off, let's recall the structure of our Jokes table.
It contains three columns: ID, JokeText, and JokeDate.
Together, these columns allow us to identify jokes (ID),
and keep track of their text (JokeText)
and the date they were entered (JokeDate).
The SQL code that creates this table and inserts a couple of entries is provided
as jokes1.sql in the code archive.Now let's say we wanted to track another piece of information about
our jokes: the names of the people who submitted them. It would seem natural
to want to add a new column to our Jokes table
for this. The SQL ALTER command (which we haven't seen before) lets
us do exactly what we need. Log into your MySQL server using the mysql command-line
program as in "Getting Started with MySQL", select your database
(jokes if you used the name suggested in that chapter)
then type this command:
mysql>ALTER TABLE Jokes ADD COLUMN
->AuthorName VARCHAR(255);
This code adds a column called AuthorName to
your table. The type declared is a variable-length character string of up
to 255 characters, plenty of space for even very esoteric names. Let's also
add a column for the author's email address:
mysql>ALTER TABLE Jokes ADD COLUMN
->AuthorEMail VARCHAR(255);
For more information about the ALTER command, see "MySQL Syntax". Just to make sure the two columns
were added properly, we should ask MySQL to describe the table to us:
mysql>DESCRIBE Jokes;
+-------------+--------------+------+-----+------------+-- -
| Field | Type | Null | Key | Default |
+-------------+--------------+------+-----+------------+-- -
| ID | int(11) | | PRI | NULL |
| JokeText | text | YES | | NULL |
| JokeDate | date | | | 0000-00-00 |
| AuthorName | varchar(255) | YES | | NULL |
| AuthorEMail | varchar(255) | YES | | NULL |
+-------------+--------------+------+-----+------------+-- -
5 rows in set (0.01 sec)
Looks good, right? Obviously, we would need to make changes to the HTML
and PHP form code we created in "Publishing MySQL Data on the Web" that
allows us to add new jokes to the database, but I'll leave the figuring out
of those details to you, as an exercise. Using UPDATE queries,
we could now add author details to all the jokes in the table. But before
we get carried away with these additions, we need to stop and consider whether
this new table design was the right choice here. In this case, it turns out
that it wasn't.
