Figure 3.6 Here is one customer who has multiple orders.
To create a one-to-many relationship inside a database is very similar to creating a one-to-one
relationship. Again, it involves using keys. First, you must select a primary table. Unlike one-to-one
relationships, there is a definite primary and secondary table. The primary table is the table that
contains the single record, and the secondary table contains the multiple records. The primary table's
key field will exist in the secondary table, but it will not be unique. The secondary table's key field will be
unique, but the foreign key will not be unique. This permits you to add as many records as you want and
still be able to distinguish each record individually, as well as relate them to a single record in another
table. Look again at Figure 3.6, which illustrates this point.
Many-to-Many
A many-to-many relationship exists when table one has a record that has many corresponding records in
table two, and table two has a record with many corresponding records in table one (see Figure 3.7).
Figure 3.7 A many-to-many relationship.
The many-to-many relationship can cause some problems. It might introduce redundant data, which
breaks your rules of normalization. Normalization is covered in more detail on Day 5, "Making Your Data
Normal." A many-to-many relationship is also hard to maintain. Deleting and adding new records
becomes very hazardous. For example, your bookstore has many warehouses across the nation. Each
warehouse stores a supply of books. There are many books that might be in a warehouse, and many
warehouses might contain a particular book. So what happens if you add a new warehouse? You would
have to add every book title again to your table of warehouses. It could get a little hairy. To combat this
situation, you would come up with an intermediary table that would link these tables together. This
would create two one-to-many relationships (see Figure 3.8). This table would consist of the primary
keys of both tables. When a book is placed in a warehouse, this intermediary table would have a new
record added consisting of the book's key fields and the warehouse's key fields. If you needed to know
which books were in the warehouse, you could query this intermediary table to find out. At first, this
might seem like it is adding another layer of complexity to the database. I can assure you it is well worth
it. It is very hard to implement a many-to-many relationship without the intermediary table.
Figure 3.8 A many-to-many relationship becomes two one-to-many relationships. This is done to ease
maintenance and add flexibility, and to obey the rules of normalization.
After the relationships have been identified, you should add them to your model. This will help remind
you to include these relationships when you create the database. You should also add the new key