. 8
( 132 .)


Types of Relationships
In the applications you™ll see later in this book we create a bunch of tables that
don™t have anomalies. We include columns that maintain relationships among these
tables. You™ll encounter three specific types of relationships in database land.

The one-to-many relationship
This is by far the most common type of relationship that occurs between two tables.
When one value in a column references multiple fields in another table, a one-to-
many relationship is in effect (Figure 1-1).


industry_id industry_name
1 Utilities
2 Construction
3 Banking

company_id company_name industry_id
1 Big Co Corporation 1
2 Little Co Corporation 1
3 Joe's Utility 1
4 Leadfoot Builders 2
5 Angel's Cement Boots 2
6 Al's Bank 3
Figure 1-1: Tables with a one-to-many relationship

Figure 1-1 shows a classic one-to-many relationship. Here, each company is
associated with a certain industry. As you can see, one industry listed in the indus-
try table can be associated with one or more rows in the company table. This in no
way restricts what you can do with the companies. You are absolutely free to use
this table as the basis for other one-to-many relationships. Figure 1-2 shows that
the Companies table can be on the “one” side of a one-to-many relationship with a
table that lists city locations for all the different companies.
16 Part I: Working with MySQL

industry_id industry_name
1 Utilities
2 Construction
3 Banking

company_id company_name industry_id
1 Big Co Corporation 1
2 Little Co Corporation 1
3 Joe's Utility 1
4 Leadfoot Builders 2
5 Angel's Cement Boots 2
6 Al's Bank 3

co_location_id company_id city
1 2 San Francisco
2 2 New York
3 2 Chicago
4 5 Dallas
Figure 1-2: Tables with two one-to-many relationships

The one-to-one relationship
A one-to-one relationship is essentially a one-to-many relationship where only one
row in a table is related to only one row in another table. During the normalization
process, we mentioned a situation in which one table holds information about cor-
porate executives and another holds information about their assistants. This could
very well be a one-to-one relationship if each executive has one assistant and each
assistant works for only one executive. Figure 1-3 gives a visual representation of
this relationship.
Chapter 1: Database Design with MySQL 17

execid exec_first_name exec_last_name
1 Jon Dust
2 Melinda Burns
3 Larry Gains

asst_id exec_id asst_first_name asst_last_name
1 1 Walter James
2 2 James Walter
3 3 Nancy Els
Figure 1-3: Tables with a one-to-one relationship

The many-to-many relationship
Many-to-many relationships work a bit differently from the other two kinds of
relationships. For instance, suppose that the company keeping the data has a vari-
ety of newsletters that it sends to its contacts, and suppose that it needs to add this
information to the database. There™s a weekly, a monthly, a bi-monthly, and an
annual newsletter, and to keep from annoying clients, the newsletters must only be
sent to those who request them.
To start, you could add a table that stores the newsletter types (Table 1-14).


newsletter_id newsletter_name

1 Weekly
2 Monthly
3 Bi-monthly
4 Annual

Table 1-14 can™t be directly related to another table that stores contact informa-
tion. So it™s not sufficient to define which clients have requested which types of
newsletters. The only way to make that work is to add a column to the Contacts table
that stores the newsletters that each contact receives. Right away, you should notice
a problem with Table 1-15. In Table 1-15 the Newsletters column contains more
18 Part I: Working with MySQL

than one value. The value looks a lot like an array. As mentioned earlier, this should
never occur within a database ” you want only atomic values in each column.


contact_id contact_first_name contact_last_name Newsletters

1 Jon Doe 1,3,4
2 Al Banks 2,3,4

In situations like this you™ll need to create another table, of a type often known
as a mapping table because it maps the relationship of one table to another. Figure
1-4 shows how the relationship between these values can be made to work.

contact_id contact_first_name contact_last_name
1 Jon Doe
2 Al Banks

newsletter_id newsletter_name
1 Weekly
2 Bi-Weekly
3 Annual
4 Semi-annual

contact_id newsletter_id
1 1
1 2
2 2
2 3
2 4
Figure 1-4: Tables with a many-to-many relationship

With this structure, any number of contacts can have any number of newsletters
and any number of newsletters can be sent to any number of contacts.
Chapter 1: Database Design with MySQL 19

Newcomers to databases often overlook many-to-many relationships and
instead choose designs that require excessive columns within a table or
arrays within a column. Make sure to consider a many-to-many relationship
if your structure seems unmanageable.

Advanced Database Concepts
For a long time MySQL was a polarizing piece of software in the applications-
development community. It had (and still has) aspects that many developers loved:
it™s free (at least, when used in applications that conform to the GNU Public
License), it doesn™t take up a whole lot of resources, it™s very quick, and it™s easy to
learn compared to packages like Oracle and Sybase. However, it didn™t originally
offer features common in other databases, such as subselects or joins in updates,
and these shortcomings kept many from adopting MySQL for their applications. But
since the publication of the first edition of this book a lot of work has been done on
MySQL, and it now offers at least partial support for the features discussed in the
following sections.

Referential integrity
Every example used so far in this chapter has made use of foreign keys. A foreign
key is a column that references the primary key of another table in order to main-
tain a relationship. In Table 1-4, the Contacts table contains a company_id column,
which references the primary key of the Companies table (Table 1-3). This column
is a foreign key to the Companies table.
In Chapter 2 we demonstrate how to create tables in MySQL. It™s easy enough to
create tables with all the columns necessary for primary keys and foreign keys.
However, in MySQL foreign keys are not universally available.
In packages like Oracle, Sybase, or PostgreSQL, tables can be created that explic-
itly define foreign keys. For instance, with Oracle the database system could be
made aware that the company_id column in the Contacts table has a relationship to
the company_id column in the Companies table. This capability is potentially a
very good thing and is known as a foreign-key constraint. If the database system is
aware of a relationship, it can check to make sure the value being inserted into the
foreign-key field exists in the referenced table. If it does not, the database system
will reject the insert. The capability of the database server to reject records because
they don™t satisfy the requirements of linked tables is known as referential integrity.
With MySQL, at the time of this writing, foreign-key constraints are only avail-
able when you™re using the InnoDB table type. You™ll see how to work with foreign-
key constraints in InnoDB in Chapter 2.
20 Part I: Working with MySQL

To demonstrate the importance of foreign-key constraints we™ll show you how
you™d achieve the same effect using MySQL table types other than InnoDB. Before
inserting or updating records in your table, you have to take some extra steps.
To be ultra-safe, you would need to go through the following steps in order to
insert a row in the Contacts table (Table 1-4), for example:

1. Get all the values for company_id in the Companies table.
2. Check to make sure the value for company_id to be inserted into the
Contacts table exists in the data you retrieved in Step 1.
3. If it does, insert values.

The developers of MySQL had long argued that referential integrity was not nec-
essary and that including it would slow down MySQL. Further, they argued that it
is the responsibility of the application interacting with the database to ensure that
the inserted data is correct. There is a logic to this way of thinking. In Parts III and
IV of this book we present several applications that would work just fine without
enforcing referential integrity or the method of checking shown above. In general,
in these applications, all the possible values are pulled from a database anyway and
there™s very little opportunity for errors to creep into the system.
But there™s no doubt that having the option of enforcing referential integrity is a
good thing.

In relational databases, things change in groups. As shown in a variety of applica-
tions in this book, many changes require that rows be updated in several tables
concurrently. An e-commerce site may contain code that works in the following

1. Insert a customer into the Customers table.
2. Check the inventory table to see that a sufficient quantity of the item
exists to place the order.
3. Add invoice information into the Invoice table.
4. Reduce the quantity available for the item in the inventory table by the
quantity ordered.

When you™re working with a series of steps like this, serious problems can occur.
If the operating system crashes or power goes out between steps three and four, the
database will contain bad data. It™s also important to remember that MySQL and
other relational databases are multi-threaded, which means that they can process
directives from multiple clients simultaneously. Imagine what would happen with
the previous listing if two orders were placed almost simultaneously for an item
that was nearly out of stock. Two threads (in the case of an e-commerce site, two
Chapter 1: Database Design with MySQL 21

customers working through their browsers) could find themselves requesting the
final item at the same time. If precautions are not taken, it™s possible that one per-
son might receive confirmation that the order is available when in fact it is not.
To prevent such occurrences, most sophisticated database systems make use of
transactions. A transaction is a bundle of commands treated as an indivisible unit.
If any one of these commands fails to go through, the whole group of commands
fails, and the database returns to the state it was in before the first command was
attempted. This is known as a commit/rollback approach. Either all the requests are
committed to the database, or the database is rolled back to the state it was in prior
to the transactions. This works both to prevent threads from stepping on each other


. 8
( 132 .)