Introduction to Database Software

At its heart, FileMaker Pro is database software; databases are useful for keeping track of contacts and their addresses and phone numbers, the students in a school, the sales and inventory in a store, or the results of experimental trials. Although this sort of information can be kept in spreadsheets and word processor documents, a database will make it much easier to take on these tasks:

• Organize your data into reports Databases can organize information into reports sorted by city, last name, price, or any other criteria necessary.
• Find one or several items in your collections Visually scrolling through a document with flat data displayed soon becomes unwieldy. Databases make it relatively simple to search for one record (or row) of data within potentially millions of others.
• Create related associations among data Rather than duplicating the name of a company for multiple people (for example), or perhaps having to reenter an address in a dozen places, users can utilize databases to create associations between data elements (using a form of addressing) and preserve the integrity of their information.
• Share data with other systems Databases are often built to exchange information with other systems; many become one component in multitiered technology solution for companieseven small businesses often exchange data between QuickBooks, for example, and FileMaker Pro.

There are other advantages to using database software, not the least of which is the capability in FileMaker Pro to construct a user interface that can map to an organization's workflow. Often the members of an organization will outgrow the documents of desktop applications when they need to support multiple authors, track data in structured, interrelated ways, or manipulate data sets based on differing criteria. Often the first herald of the need for a database is when users are frustrated with not being able to find a given piece of information.

The rest of this book gets into detail on how to do everything just mentioned and much more as well. You'll get a more detailed look at what a database is and how it works, how to build databases, and so on. But before we dive into the mechanics of databases, it's important to understand how theyand FileMakerfit into the overall software computing world.

Database Software

A huge variety of software is on the market today. FileMaker generally falls into the category of business productivity software; however, it really is a hybrid application that marries desktop application productivity to a server-based architecture and database. It is as accessible as programs like Microsoft Excel and Intuit's QuickBooks, yet it also allows developers to create complex workgroup databases that are deployed in the same manner as other IT server-based applications.

The idea of managing a collection of structured information is what database software is all about. Some database products on the market manage specialized collections such as business contacts. Products such as Act and Goldmine are good examples of those. Quicken, QuickBooks, and Microsoft Money manage collections of financial transactions.

FileMaker and other nonspecialized database products such as Microsoft Access are used to create database systems just as word processing software is used to create specific documents and Microsoft Excel is used to create spreadsheets. In fact, Microsoft Excel is often used as a database because it has several strong list-management features. It works well for managing simple databases, but it doesn't work well in managing multiple lists that are related to each other.

Often, simple grids of columns and rows of information (such as spreadsheets) are called flat file or list databases. Simple databases like these are generally self-contained; they usually don't relate to each other, so keeping information up-to-date across many such databases can become unwieldy or impossible. In such cases a relational database is called for. FileMaker is a fully relational database system and allows developers to associate a row (or record) in one area of the database (a customer list, for example) with records in another area of the database (a list of purchase orders, for example). To take another example, users of a relational database system can tie a single company entry to multiple contact people or even associate a single person with multiple company entries. Rather than entering this information in a dozen different places, relational databases, using a form of internal addressing, simply associate one item with another (customers with their orders, companies with their contacts). It is in this way that FileMaker gradates from a single-user productivity tool to a fully realized database development platform.

Off-the-Shelf Software

There are many relational database products on the market: Specialized products such as Act and Quicken are also relational database products, but the difference is that those products are finished systems, offering a specific set of functionality, whereas products such as FileMaker are tools, used to create custom systems tailored to the individual needs of an organization or a person.

It is certainly possible to re-create the functionality of Act or Quicken by using FileMaker, and some organizations choose to do so when faced with the fact that such specialized products are relatively inflexible. If an organization has nonstandard ways of doing things, its members may find it difficult to work with specialized products. Although FileMaker Pro does come with several database templates that might be perfectly suitable for an organization to use right away, most users instead turn to FileMaker to create custom database systems that exactly match how their organization operates.

Custom Development Software

With a database development tool such as FileMaker Pro, a person can build a system to be exactly what is needed. It's the difference between buying a house that is a pretty good match and building a custom home that has exactly the features one wants (or at least can afford).

Home construction is actually a great analogy for building a database because both follow similar trajectories. A home needs to be designed by an architect before it can be built. An owner needs to wait for the home to be built before he can move in, and questions or issues often arise during the construction process. After the home is built, the owner's needs may change and he may need to have an addition built onto the house to accommodate changed circumstances.

Building a custom home often follows a similar path: The foundation needs to be laid and the walls and plumbing need to be stubbed in before the final coat of paint can be applied to the drywall. Software development often is a complex layering of interdependent parts, and we in the software business don't have the good fortune to be able to run to Home Depot for standardized parts at a moment's notice! Often we need to build our own tools as well.

Finally, imagine that a home's construction is well under way and the owner decides to move the living room wall six feet. Although that is always possible, the impact of that change will vary a great deal depending on the stage at which the crew is working.

This last point is an important one, and it is also where we diverge from the home construction analogy because real-world environments always change. This is especially true for today's email-driven, connected-network world. One of the key advantages to developing database systems in FileMaker Pro is that these systems can be rapidly redesigned, even while the system is in use by other users. Any aspect of a FileMaker system can be changed while it's live if need be (although that may not always be advisable). FileMaker's greatest strength is its inherent flexibility.

What Database Software Does

FileMaker is database software. The thing that makes it unique in the market is the ease and means by which it allows developers to present information, but it's important to grasp the fundamentals of how all database softwareincluding FileMakerworks.

The simplest kind of database is a list. It could be a list of employees or products or soccer teams. Consider an employee example. The information a Human Resources department might want to keep track of could look like the information shown in Table 1.1.

In database parlance, a list like this is called a table. Crudely put, a table is a collection of like thingsin this case, people. After a table for people is established, one might extend it to include other attributes (or columns) for, say, phone numbers. The result is shown in Table 1.2.

As mentioned earlier, this type of database is called a flat file database because everything is in one table. Although it's nice to have everything in one place, this kind of structure has shortcomings. In this case, every time someone thinks up a new type of phone number to track, another column needs to be added to the table. This is likely fine for phone numbersin the real world people usually have only a handfulbut imagine what would happen if the example was tracking people's previous job titles? The spreadsheet or list would have a potentially unlimited number of columns, and there would be no logical correspondence between one person's "job #1" column and another's.

Furthermore, if someone doesn't have a particular type of phone number, that cell is left blank, resulting in a "Swiss cheese" look to the table. Unused cells take up space in the database and can slow things down for larger data sets.

In a relational structure only the first three columns would be in the employee table itself. The last three columns, which all represent phone numbers of some kind, would be moved to their own table. A label field could be added to identify each type of phone number, with the resulting two tables looking something like those shown in Tables 1.3 and 1.4.

Note that an additional field has been added: an Emp ID field. Think of this field as an internal address within a table. It is used to match employees with their phone numbers. In relational database terminology, this column is called a key field. The FileMaker Pro help system refers to it as a match field, but they are one and the same. Key fields are used to identify specific records.

Although FileMaker Pro can be used to build simple flat file database systems (see Figure 1.1), it really shines at creating relational database systems (see Figure 1.2).

Figure 1.1. FileMaker can be used to construct simple flat file databases.

Figure 1.2. The two-table Employee/Phone example can look something like this when implemented in FileMaker.