If you’re new to databases, this chapter covers some basic concepts that will help you start off on the right foot in tapping the power of databases. If you’re already familiar with databases, you may want to skip ahead to FileMaker Basics on page 19.
While you might not think of them as such, databases are everywhere: address books, cookbooks, television program listings, to-do lists scribbled on envelopes—examples abound. None of those examples involves a computer, but they illustrate a fundamental concept: databases organize information.
An address book organizes information alphabetically. A cookbook organizes information by ingredient or by course. Television listings organize information by time and channel. To-do lists organize information by task and time. Each lets you find what you need precisely because of how the information is organized. A computer database is not so different except for one major advantage: it can quickly organize the same information in multiple ways.
In some ways, a database is like that latenight TV perennial, the Veg-o-Matic: It slices! It dices! It’s ten kitchen tools in one! A database can slice the same basic information any number of ways: as address book entries, as mailing labels, as billing invoices—whatever’s needed.
Understanding content vs. form is the key to tapping the real power of any database. Do not confuse what a database contains (the content) with how it looks (the form). As important as data may be, it’s not what gives a database its power. Instead, the power is in the program’s ability to organize—and instantly reorganize—the display of that data (Figure 2.1).
Figure 2.1. A database’s real power comes from being able to display a single record’s data in multiple ways.
Many people use spreadsheet programs to organize and analyze data. At times a spreadsheet is the best tool for such work, but a database often offers far more flexibility. Spreadsheet information, for example, is confined to rows and columns. Database programs like FileMaker can break free of that grid to display data as tables, lists, address labels, or in almost any form you need. Since FileMaker lets you drag data from Microsoft Excel and drop it right into a layout, making the switch couldn’t be easier.
Tapping the power behind any database boils down to understanding and effectively using just a few items: fields, records, and layouts. Let’s take a look at the role of each in a database program like FileMaker.
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 field—the smallest unit within a database—contains 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.
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 99, 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.
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.
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, that can save a lot of disk space—and lots of time.
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 vs. 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.