This set entered on 5-12-2014
5.2.14.2
- p 321. JOIN TABLES. What is a MANY TO MANY RELATIONSHIP?
Your database tracks payments from customers. A CUSTOMER can send a check to cover TWO INVOICES, or to cover JUST PART OF AN INVOICE So an INVOICE can have MULTIPLE PAYMENTS, and a PAYMENT CAN BE FOR MULTIPLE INVOICES: MANY TO MANY.
- How do you create a MANY TO MANY RELATIONSHIP?
You must CREATE A JOIN TABLE.
- What is a JOIN TABLE?
A JOIN TABLE does not usually REPRESENT A REAL ENTITY. Instead, EACH RECORD REPRESENTS A RELATIONSHIP BETWEEN TWO RECORDS IN THE RELATED TABLES.
- How does a JOIN TABLE facilitate a MANY TO MANY RELATIONSHIP?
In the INVOICE/PAYMENT example, the JOIN TABLE holds FOREIGN KEYS for INVOICE NUMBER and PAYMENT ID.
- p 322. If you want to attach a PAYMENT TO AN INVOICE, you:
ADD a new RECORD IN THE JOIN TABLE, with the CORRECT INVOICE NUMBER and PAYMENT ID. Remember that the JOIN TABLE holds only TWO FIELDS in this example: FOREIGN KEY for INVOICE ID, and a FOREIGN KEY for PAYMENT ID.
- P 322. What happens once the RECORD is added to the JOIN TABLE?
The PAYMENT and INVOICE are properly connected IN BOTH DIRECTIONS. Since you can add AS MANY RECORDS as you want to the join TABLE, you can flexibly RELATE multiple INVOICES and PAYMENTS.
