لطفا منتظر باشید ...
Simple Data Relationships
Thebest type of database layout for a given situation is usually dictated by
the type of relationship that exists between the pieces of data that it needs
to store. In this section, I'll examine the typical relationship types, and
explain how best to represent them in a relational database.In the case of a simple one-to-one relationship,
a single table is all you'll need. An example of a one-to-one relationship
that you've seen is the email address of each author in our joke database.
Since there will be one email address for each author, and one author for
each email address, there is no reason to split the addresses off into a separate
table.A many-to-one relationship is a
little more complicated, but you've already seen one of these as well. Each
joke in our database is associated with just one author, but many jokes may
have been written by that one author. This joke-author relationship is many-to-one.
I've already covered the problems that result from storing the information
associated with a joke's author in the same table as the joke itself. In brief,
it can result in many copies of the same data, which are difficult to keep
synchronized, and which waste space. If we split the data into two tables,
and use an ID column to link the two together, which will make joins possible
as shown above, all these problems disappear.You have yet to see a one-to-many relationship,
but finding an example isn't difficult. In our database so far, we've assumed
that each author has only one email address. While this may not always be
the case, this is a reasonable limitation to impose since you only really
need one email address to get in touch with an author. You simply trust that
each author would enter his or her most-used email address—or at least
one that is checked regularly—when adding him or herself to the database.
If you did, however, want to support multiple email addresses, you'd be faced
with a one-to-many relationship (one author may have many email addresses,
but each email address belongs to exactly one author).When someone inexperienced in database design approaches a one-to-many
relationship like this one, his or her first approach is often to try to store
multiple values in a single database field, as shown in "Never overload a table field to store multiple values, as is done here".
Never overload a table field to store multiple values, as is done here
While this would work, to retrieve a single email address from the database,
we'd need to break up the string by searching for commas (or whatever special
character you chose to use as a separator)—a not-so-simple, and potentially
time-consuming operation. Try to imagine the PHP code necessary to remove
one particular email address from one particular author! In addition, you'd
need to allow for much longer values in the EMail column,
which could result in wasted disk space, because the majority of authors would
have just one email address.The solution for a one-to-many relationship such as this is very similar
to the solution we saw for a many-to-one relationship above. As you might
expect, the pattern is simply reversed. You just break the Authors table
into two tables—Authors and EMails—and then associate the email addresses
with their authors using an Author ID (AID)
column in the EMails table (see "The AID field associates each row
of Emails with one row of Authors").
The AID field associates each row
of Emails with one row of Authors
Using a join, it's easy to list the email addresses associated with
a particular author:
mysql>SELECT EMail FROM Authors, EMails WHERE
->Name="Kevin Yank" AND AID=Authors.ID;
+---------------------+
| EMail |
+---------------------+
| kevin@sitepoint.com |
| kyank@attglobal.net |
+---------------------+
2 rows in set (0.00 sec)
