Database Reference
In-Depth Information
most of the time. But in the real world, customers will make partial payments on invoices, or
sometimes they won't pay an invoice when it's due and a new one gets issued in the mean-
time. Then they'll cut a check to cover both invoices. Your database has to be able to track
those cases, even though they aren't the norm. A many-to-many relationship lets you handle
all those situations.
But many-to-many relationships pose a special challenge. To understand why, think about
how they're different from a one-to-many relationship, like an Invoice and a Line Item. One
Invoice can have many Line Items, but each Line Item can belong to only one Invoice. The
two tables are related by a key field, which holds the same value in all the records that relate
to one another. And of course each record's key field has only one value in it.
With a many-to-many relationship, you need many records in each table related to many re-
cords in the other table. But you can't put multiple values in a key field to try to make it
work. Instead, you add a new special-purpose entity between the two ends of a many-to-
many relationship—called a join table . Think of it as chopping the many-to-many line in half
and then inserting a new table in the middle. That new table has a one-to-many relationship
to both of the original tables.
Here's how it works in your many-to-many relationship (Invoices and Payments): To split it
up, you need to create a new entity. Since it doesn't have a decent name, just call it Invoice
Payment (as in “This record represents one invoice payment—one payment on one invoice”).
Now, instead of “Any given invoice is paid with payments, and any given payment is applied
to invoices,” you can say these two things:
▪ An invoice is paid with invoice payments, and an invoice payment is applied to one in-
voice.
▪ A payment is divided into invoice payments, and an invoice payment is part of one pay-
ment.
Figure 5-5 shows the updated diagram.
Search WWH ::




Custom Search