Anatomy of a Database

Databases often contain huge amounts of information, yet tiny pieces of that data can be fetched almost instantly. What's the secret? It's because everything within even the largest database is organized piece by piece into categories, or fields (Figure 2.2). Each fieldthe smallest unit within a databasecontains information describing its contents. With that field information, the database can go to work. And by understanding the power of fields, so can you.

Figure 2.2. Every database organizes its information into individual records, which then contain fields for each bit of data.

The fieldthe smallest unit

Fields let a database keep track of what information goes where. Each field contains data but also carries a description, called a field name. The field name helps the database sort, sift, and manipulate without necessarily needing to deal directly with the data itself. It can be a bit confusing, but remember: fields, field names, and the data inside the fields are three different things (Figure 2.3).

Figure 2.3. Each field contains data but also carries a description, called a field name, which makes it possible to quickly manipulate even a large database.

The more specific the fields you create within a database, the more powerful the database. Hang on to this idea as you learn more about FileMaker. For now here's an obvious example of why it's so important to make fields as specific as possible.

Though surely you wouldn't do this, imagine you've built an employee database with just one name field. With only a single name field, an alphabetical sort yields an immediate problem: Dennis Smith appears before Jennifer Norriz because D precedes J (Figure 2.4). Obviously that's not what you want. Creating two name fields lets you sort the last and first names alphabetically and independently (Figure 2.5). Obvious yes, but it's an idea that's easily forgotten in the heat of designing a new database. See Part III, starting on page 85, for more on defining and using fields with precision.

Figure 2.4. Smith before Norriz: Having only one name field highlights the problem of not breaking fields into the smallest pieces possible. Figure 2.5 shows a better approach.

Figure 2.5. By breaking data into smaller pieces, such as adding two name fields, you gain more control over your information. This concept is crucial to building powerful, yet precise, databases.

The recordgrouping related fields

Put a bunch of fields together and you have what FileMaker calls a record. A single record contains related information about a single topic, person, or activity. In an address book, for example, the equivalent of a record would be the entry for one person. That entry or record would contain several related items: the person's name, address, and telephone number. As you already know, those three items are equivalent to fields in a database.

The databasea group of related records

Combine a bunch of records on a single topic (for example, customers), and you have a database. A database also can contain records on several related topics, such as customers, their addresses, invoices, and past orders. The ability to connect or relate different databases is what's meant by a relational database, like FileMaker.

One of the advantages of a relational database is that you can make such connections between databases without duplicating the information in each database. When you're dealing with thousands of records, this feature can save a lot of disk spaceand lots of time.

The layoutone record, many forms

A layout, sometimes called a view, is simply a way to control how the information in a database is displayed. When you first begin building FileMaker databases, you may find yourself occasionally confusing records with layouts. Again, the difference boils down to content versus form: One record (content) can have many different layouts (forms) (Figure 2.6).

Figure 2.6. Using FileMaker's layout pop-up, the content of a single record can be displayed in many different forms, or layouts.

At its most basic, a record is all of the information for a single entry, while a layout shows a view of only the portion you need at the moment. Layouts also offer a way to hide everything you don't need at the moment. Let's go back to our paper address book example.

For each person in your book, you've probably listed their name, address, and phone number. If you're sending someone a birthday card, obviously you don't need to see their phone number. Similarly, if you want to call someone, you don't need their address. The paper address book shows you both. With databases, layouts enable you to show only what's relevant to the task at hand. So if you need mailing labels, you can take those address records and create layouts that only show the address. This notion of showing only what you need becomes especially important when you're working with a huge database containing dozens, or hundreds, of records and fields.

No matter whether you're using FileMaker or some other database program, these terms and concepts remain much the same. Now you're ready to delve into the particulars of FileMaker itself.