This set entered on 5-12-2014
5.1.14.2
- p 311. Normally FileMaker knows which records are related to one another because:
They HAVE SOMETHING IN COMMON. For example, the INVOICE TABLE and the LINE ITEM TABLE both have an INVOICE NUMBER FIELD.
- You can put only ONE INVOICE NUMBER in a LINE ITEM RECORD. This is no problem because:
INVOICES and LINE ITEMS have a ONE TO MANY RELATIONSHIP. A LINE ITEM belongs to ONE INVOICE, but ONE INVOICE can have many LINE ITEMS.
- A JOB TABLE may have only one ATTRIBUTE: a JOB NAME. Why have a TABLE that ONLY has ONE ATTRIBUTE?
To ensure consistency, you are basically using the TABLE AS A VALUE LIST. With a JOB TABLE, instead of typing the job name everywhere, you simply choose it from a list.
- What is an example of a MANY TO MANY RELATIONSHIP?
An INVOICE IS PAID WITH PAYMENTS, AND A PAYMENT IS APPLIED TO INVOICES. An iNVOICE can be paid with several payments and a payment can be applied to multiple invoices. THEREFORE, MANY TO MANY.
- How should you handle a MANY TO MANY RELATIONSHIP?
You need to chop it in half, turning each HALF into a separate ONE TO MANY RELATIONSHIP.
- How do you turn a MANY TO MANY RELATIONSHIP into TWO separate ONE TO MANY RELATIONSHIPS?
You have to add a new SPECIAL PURPOSE ENTITY in the middle, so the INVOICES TABLE has a ONE TO MANY RELATIONSHIP with the SPECIAL ENTITY, and the PAYMENTS TABLE has a ONE TO MANY RELATIONSHIP with the SPECIAL ENTITY.
- What is the SPECIAL ENTITY called?
its database name is JOIN TABLE, but you should give it a descriptive name TO FIT THE SITUATION: call it INVOICE PAYMENTS.
- Now, instead of “AN INVOICE IS PAID WITH PAYMENTS, AND A PAYMENT IS APPLIED TO INVOICES.” you can say these two things:
a. an INVOICE is paid with INVOICE PAYMENTS, and an INVOICE PAYMENT is applied to ONE INVOICE.
b. A PAYMENT is divided into INVOICE PAYMENTS, and an INVOICE PAYMENT is part of one payment.
