FileMaker Pro 7 Bible

John Wiley & Sons

Chapter One

Before exploring FileMaker Pro 7, you must understand what a database is. A database is an organized collection of information, usually with one central topic. In a computer database (as opposed to a paper database), the program that you use to enter and manipulate the data is a database program or a database management system (DBMS).

The word organized is a key part of this definition. Otherwise, a shoebox stuffed with business receipts might be considered a database. In general, if you must look at every scrap of data until you find the one for which you're searching, you don't have a database. You just have a shoebox full of stuff.

Even if you've never used a computer database management system, you're already familiar with many examples of paper (and probably computer) databases:

* Address books and business card files

* Employee records

* Recipe card files

* Telephone books

* Holiday greeting card lists

Every database-whether on paper, in a hand-held electronic organizer, or in a computer-is composed of records in tables. A record contains information that has been collected on one individual or entity in the database. A table holds the records that you create, and the database encompasses the tables. For example, in the Employee Records database example listed in thepreceding list, you might have one table containing the employees' ID numbers, names, addresses, dates of birth, and dates of hire. Another table might include salary information, and another might include personnel actions (such as review dates and performance history).

In the previously listed examples, a record would hold all the address data on one friend or business associate (the address book or business card file example); the employment information on one employee (the employee records example); the ingredients and cooking instructions for one recipe (the recipe card file example); the name, street address, and phone number for one person or business in the area (the telephone book example); and the name of one person or family from whom you previously received a card or want to send a card to (the holiday greeting card list example).

Note

A database containing more than one table of related information is a relational database. Each related table contains a field in common with the table(s) to which it is related, such as the Employee ID number. FileMaker Pro is a relational database management system (RDBMS).

Records are divided into fields. A field contains a single piece of information about the subject of the record. In an address database, for example, the fields might include first name, last name, street address, city, state, ZIP code, and phone number. Figure 1-1 shows the relationship among the components of a database.

What distinguishes a database from any old hodgepodge of information is that the data within each record is organized. Fields are responsible for this organization. The fields appear in the same place on every record and are reserved for a particular type of information. In the example in Figure 1-1, the field for the last name is always in the upper-left corner of the address card, and it always contains a person's last name. No matter which address card you pull, you will find a last name at that spot on the card.

Of course, in some paper databases, maintaining this level of organization can be difficult. When you are writing or typing an address card, for example, you might occasionally reverse the order of the last and first names or enter a company name in that spot. Organization in informal paper databases comes exclusively from your own consistency-or lack of it.

When consistency is critical, such as when you're recording information on employees or filling out a customer invoice, records are often designed as forms. Spaces on the form have labels so that you always know which piece of information belongs where. You can still type a phone number in the space labeled Social Security number, but at least the labels make catching and correcting mistakes easier. Forms help organize the data in much the same way that a computer-based database does. In fact, this type of paper database is frequently the basis for a computer database.

Paper Databases versus Computer Databases

What's wrong with paper databases? After all, many homes and businesses rely on them. In the following sections, we discuss some shortcomings of paper databases and explain how computer databases can help avoid these limitations.

Limitations of paper databases

First, consider some of the shortcomings of paper databases:

* Making data-entry errors is easy to do. Even when you're using a typeset form, nothing prevents you from entering the wrong data in a field or forgetting to fill in a critical field, such as the hire date or medical history. * Maintenance can be difficult. For records to be easy to locate, they must be in some rational order. Whenever you return or add a record to a folder or the filing cabinet, you must place it in the correct spot. If you put the vendor file for Alpha Corporation in the Q folder, you might never find it again!

* Updating records can be time-consuming. Because of changes in information (such as addresses, phone numbers, and salaries), few databases are static. Updating a paper record could require several steps, including finding the record, erasing the old information, writing in the new information (or typing a whole new record), and returning the form to the filing cabinet. Making an across-the-board change-such as granting an incremental salary increase to all employees-can take a long time.

* Sorting records, selecting subgroups of records, and creating reports are cumbersome tasks. Suppose the boss walks into your office and says, "We're thinking about putting in a day-care center. How many of our 149 employees have kids under the age of five?" Or you might be thinking of sending a direct mail piece to your local customers. To determine printing and postage costs, you must know how many customers are in the target ZIP code or are within a particular range of ZIP codes.

In either case, you'll probably have to examine every record in the paper database. Whenever a task requires sorting, organizing, or summarizing the data in a different way, you can look forward to a nightmare of paper shuffling-hoping that you didn't overlook something important. And when you're through, you'll have to restore all the records to their original order.

* Sharing records is difficult. When a supervisor borrows some employee records, for example, the office manager no longer has easy access to those records unless you decide to kill some trees by photocopying the paperwork. (They're no longer in the file drawer.)

* Information is hard to reuse. If you want to use the information in a paper database for any purpose other than just reading it (addressing envelopes, for example), someone has to drag out the typewriter. Photocopying an address and then taping it onto a letter is considered bad form (unless you're creating a ransom note).

Advantages of computer databases

Computer databases, on the other hand, offer the following benefits:

* Entering error-free information is easier. Most database programs have features that speed data entry. Setting default values for some fields can save an incredible amount of typing time and ensure that information is entered consistently. (Using CA as the default entry for a State field, for example, ensures that you don't end up with records that variously contain CA, Calif., and California in the same field.) Other useful data-entry features include

Auto-incrementing fields (which automatically assign invoice or record numbers to new records)

Field types (which, for example, can prevent you from entering alphabetic information in a field that was designed to record salary data)

Range checking (which accepts only numbers within a particular range)

Required fields (which warn you if you don't fill in a critical field)

* You can easily add, delete, or change data. Making a change to a record merely involves bringing the record up onscreen, editing it, and then closing the file or moving to another record. Because you make all changes on a computer, you don't need to search through file drawers or hunt for an eraser. And if you need additional copies of records, you can quickly print them. The ease with which you can manage data is one of the key reasons for buying and using a database program such as FileMaker Pro.

* Finding records is simple. A Find feature enables you to quickly locate the record or records of interest.

* You can specify criteria for sorting data. Arranging records in a different order is as simple as issuing a Sort command. You can rearrange records in order of salary, record creation date, or any other field that's in the database. Most database programs also enable you to sort by multiple fields simultaneously. For example, you can sort a client database by state and by city within each state.

* You can work with discrete groups of records. Using the database program's record selection tools, you can select a subgroup of records that's based on any criteria you want. You might, for example, want to see only recipes that have chicken as the main ingredient or perhaps group employee records according to salary ranges or by department.

* Database programs can perform calculations, frequently offering many of the same calculation capabilities that spreadsheet programs offer. Instead of using a hand calculator to compute the sales tax and total for an invoice, you can have your database program automatically make the computations. In addition to performing computations within individual records, database programs can also generate summary statistics across all records or for selected groups of records. For example, you can easily summarize the efforts of different sales teams by calculating sales totals and averages by region.

* Many people can simultaneously access the database. If several people in a company need to view or modify the information in a database, you can use a database program on a network. Some database programs-including FileMaker Pro-also enable you to publish and share your data over the Web or a company intranet.

* You can readily use information for multiple purposes. For example, you can use the address information in records to print mailing labels, envelopes, a pocketsized address book, or personalized form letters.

* You can create custom reports. Only you are in a position to decide which reports are essential to running your business, department, class, bowling league, or home. In most database programs, you can create your own reports and lay them out in any format that meets your information needs. Because you can save report formats on disk, you can reuse a format whenever you want to generate a current report.

* You can use data from one program in another program. Most database programs can import and export data.

Importing enables you to bring information into the database from other programs. For example, you might already have an address book program in which you've recorded the addresses of friends and business associates. Rather than retyping those addresses in your database program, you can export them from the original program (creating a file that your database program can read) and then import them into a database.

Exporting, on the other hand, enables you to use fields and records in a database to create a file that other programs can understand. For example, you can easily export numeric data so that you can graph it with a spreadsheet program, such as Microsoft Excel.

When should you use a database program?

Not every database is a good candidate for computerization. Specifically, when deciding between using a paper database and using a computer database, you must ask yourself the following questions. (The more Yes answers you give, the more reasons you have for using a database program.)

* Will the contents of individual records change frequently? If the information for each record isn't static and editing is often necessary, choose a computer database.

* Is much of the information repetitive? A database program can have default entries for fields. If much of the information that you'll enter is repetitive, using a database program can help you avoid unnecessary typing.

* Must the records be grouped or sorted in different ways? Database programs can quickly re-sort and select records for even very large collections of data.

* Are calculations necessary? The more complex the calculations, the more you need a database program.

* Is printed output required? Unless photocopies are satisfactory, use a database program.

* Are reports necessary? Database programs excel at summarizing information. If your reports go beyond simple record counts, a database program might be the best choice.

Flat-File and Relational Databases

You can roughly classify every database program as either flat-file or relational, according to the program's relational capabilities: that is, its capability to simultaneously draw information from more than one table on the basis of shared fields.

That explanation is quite a mouthful, isn't it? A couple of definitions and an example might make it easier to swallow:

* A flat-file database always consists of a single table. All required fields must be contained within that table.

* A relational database consists of at least two interrelated tables that have at least one key field in common.

Instead of designing a single customer table that contains all your customer information (as you would in a flat-file database program), you might create several smaller tables. For example, you could create one table called Addresses to contain just customer addresses, and another called Orders to hold information about the customers' previous orders.

Continues...

---

Excerpted from FileMaker Pro 7 Bible by Steven A. Schwartz Dennis R. Cohen Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---