Chapter 7: Multiple Tables and Relationships
Chapter 8: Advanced Relationship Techniques
Up until now, you've been working with the simplest kind of database imaginableit has just one table. In the real world, one table is almost never enough. In your private
Before you dive head first into relational databases, it will be helpful to review some vocabulary. First, a
is a collection of tables, layouts, and other things that forms an organized system. A
holds information about one kind of thing, like people, orders, products, or suppliers. A
holds one attribute of something: the person's first
In the previous chapter, you've created a database whose tables and fields track various attributes of people. You could repeat the process and build any number of individual databases for organizing your time, creating invoices, and logging payments. But that approach has real problems, like the following:
When you log some billable work, you have to type in the customer information. Then, when you create an invoice, you have to type the customer information all over again in the invoice file. When you receive a payment, you have to type it a third time in the payment file. Since the databases aren't connected in any way, they can't share that information with one another.
Suppose you want to see how much you've billed a customer over the
Even if you could live with these limitations, you're certain to run into trouble when you try to create the Invoices database. An invoice typically encompasses information about two different entities: the invoice itself, and each line item . There's simply no good way to track invoices without involving at least two tables. Your solution to all these problems? Hook multiple tables together into one big database.
Not only can you put more than one table in a databaseand track more than one kind of thing in the processbut you can also tell FileMaker how the data fits together. You can say, for example, that invoice records have attached line item records; or that each payment record is associated with a particular customer record.
In database parlance, you define relationships among the data. A relationship is a connection from one table to another, along with the rules that define how records in the tables go together. For example, suppose you have two tablesInvoices and Line Itemsas shown in Figure 7-1.
In order to figure out which line items belong to each invoice, you need to understand the relationship between these tables. Notice that the Line Item table has a field called Invoice Number . This field holds (surprise!) the invoice number for each line item. You can also show this relationship with a picture, just like the one in Figure 7-2. By defining this relationship, you've created a mechanism to hook together an Invoice record and a Line Item record.