Relational Databases Explained

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:

• 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 years. You could use the Invoices database and run a summary report by, say, the customer's name. But duplicate names, misspellings, and name changes would render the information useless. You could use the People database instead. After all, the total amount billed (or outstanding balance) is, in some sense, an attribute of a customer. But to have that information ready when you need it, you'd have to create a field for it in the People database and retype the invoice information there every time. You know you have all the information somewhere, but because the system isn't integrated, it can't help you put all the pieces together.

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.

Figure 7-1. Each box in this picture represents a table. The name of the table is on top of the box. The fields are listed inside each box. When you design a multitable database, as you'll learn later in this chapter, you can use boxes just like this to map out your plan.

Figure 7-2. This picture shows the same tables, but it also shows how they're related to one another. The line between the tables represents the relationship. In this case, it shows that the Invoice Number in the Invoices table is connected to the Invoice Number in the Line Items table.