Database Reference
In-Depth Information
also have to put the Payment ID in the invoice record. If you forget, your data is no
longer valid.
That's where a join table ( Figure 5-10 ) comes in. A join table doesn't usually represent a real
entity. Instead, each record in the join table represents a relationship between two records in
the related tables.
Figure 5-10. A join table's job is to create a many-to-many relationship. This one holds foreign
keys for Invoice ID and Payment ID. To attach a payment to an invoice, add a new record in the
join table, with the correct Invoice ID and Payment ID. Once the record is added, the payment and
invoice are properly connected in both directions.
If it helps, think of join tables this way: Invoices and Payments both have a one-to-many re-
lationship to the join table. So any given invoice can connect to many join records, each of
which connects to one payment. Likewise, any given payment can connect to many join re-
cords, each of which connects to one invoice. So you get many related records in both direc-
tions. A join table always contains two foreign keys, one from each table it's joining.
NOTE
The join table in the database you're building is the type that doesn't represent a real entity. But in
some cases, a database may already have a real table that can act as a join table. Figure 5-11 shows
an example.
Join tables can sometimes hold fields that don't quite belong in any other table. Suppose you
wanted to record the portion of a payment that was applied to each invoice. For example, if a
customer hands you a check for $100 and you have two outstanding invoices for that cus-
tomer, for $80 and $30, then you may want to decide how to allocate the payment. Perhaps
Search WWH ::




Custom Search