Before you dive head first into relational databases, it will be helpful to review some vocabulary. First, a database is a collection of tables, layouts, and other things that forms an organized system. A table holds information about one kind of thing, like people, orders, products, or suppliers. A field holds one attribute of something: the person's first name, the order date, the color of a product, or the supplier's address. (An attribute simply means an individual characteristic. For example, a bicycle might have several attributes: Color, Height, Style, and Price. In a database, each of these attributes gets its own field.)
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:
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.
Note: Database developers use the word entity to mean "one kind of thing." Person is an entity, and so is Invoice. Remember, though, that one specific thing isn't an entityBill Gates isn't an entity, and neither is Invoice #24601.
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.
Part I: Introduction to FileMaker Pro
Your First Database
Organizing and Editing Records
Building a New Database
Part II: Layout Basics
Advanced Layouts and Reports
Part III: Multiple Tables and Relationships
Multiple Tables and Relationships
Advanced Relationship Techniques
Part IV: Calculations
Introduction to Calculations
Calculations and Data Types
Part V: Scripting
Part VI: Security and Integration
Exporting and Importing
Sharing Your Database
Part VII: Appendixes
Appendix A. Getting Help