The best 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)