Database Reference
In-Depth Information
One-to-Many Relationships
Most of the relationships in your diagram are one-to-many, which is normal. One-to-many
relationships outnumber all other types by a large margin in almost any system. See the box
above for a description of the various types of relationships.
One-to-One Relationships
Your list of entities and relationships shows a one-to-one relationship between Customers
and Addresses. For the purposes of this database, one customer can certainly have one ad-
dress, and vice versa. But if that's the case, are they really separate entities? In fact, Address
is just an
attribute
of the Customer entity. That makes it a prime candidate for entity-elimina-
tion. Put the address
fields
in the Customers table instead. You may argue that you
could
work for two people in the same household, and would therefore have to type the same ad-
dress twice in your Customers table if you didn't have an Address table. The best answer to
an argument like that is: “big deal.” This situation doesn't arise often enough to justify a
more complicated database just to eliminate duplicating one or two addresses. Even without
a separate Address table, you can still separately handle all other tasks for these two clients.
On the other hand, if you're managing a school, it's important to know which students share
a home, and which parents they belong to. In that case, an Address entity makes sense. But
as you start to think about the entity called Address, you start to realize that it's really a
Household, and once again the Address is an attribute, but of the Household and not of the
Student. This new way of thinking about your entities is one of the many realizations that
can dawn on you as you're modeling your data, and it's a great example of why this sort of
planning is so crucial. It's a lot better to make these types of mistakes on paper than in the
database.
TIP
If you expect to have to track several addresses for each customer, then you can create a one-to-
many relationship between Customers and an Addresses table. For the current example, though,
you'll stick to a single address built right into the Customers table.
As a general rule, unless you can articulate a good reason for its existence, a one-to-one rela-
tionship is a mistake: It's just two tables where one would suffice. You'll almost always want
to combine entities like people and their addresses into one table.
Many-to-Many Relationships
Ideally, you send an invoice to a customer, who pays the entire invoice with one check or
credit card payment. It may even be the case that most of your customers do exactly that
