Tables and Fields

So you're getting tired of your job, and you decide it would be exciting to get into the private investigator business. You found an office, ordered business cards, secured a phone line, and purchased some snazzy furniture. But have you thought about how you're going to run your business? Chances are, you could use some help from a database. And the first thing you need to think about when building a database is what kind of information it's going to track. For example, a private investigator might want to keep track of mostly peoplenames, phone numbers, aliases, passport numbers, and so on. Someone in retail, however, might want to track inventoryproduct names, descriptions, item numbers, prices, quantities, and similar details.

3.1.1. Tables: The Foundation of Your Database

You already know that whatever information you put in your database goes into fieldsand that's where your database building begins. These fields in turn comprise a table. Tables are at the heart of the database, holding all the information and keeping it organized. Everything else in a database works in service of the tables in some way: letting you edit, extract, or view the information.

Conceptually, a table has rows and columns. The fields you define become the columns in the table. As you add records, you add new rows to the table. It's called a table because it stores information in a tabular form, just like the table of values in the back of a college math book. When you first start out with FileMaker, you may not even be aware that tables exist, but you have one anyway. FileMaker always creates one table for you when you make a new database, and that's where it puts all your information. It doesn't matter how your database lookseven if you never see any rows and columns, you still have a table inside where the data lives. See the box on Section 3.1.2 for more on tables.

Note: In this chapter, you're going to build a database based on a single table. As you've probably guessed, a database can have more than one table. A database can also have no tables at allinstead, it can work with tables from other databases. You can even hook several tables together so they can share data and keep track of relationships between records. That's why FileMaker Pro is called a relational database program. In Part 4, you'll learn all about multiple table databases and hooking multiple databases together.

3.1.2. Figuring Out What Fields You Need

You decide the first thing your new business needs is a database to keep track of the people you deal with. Figuring out what fields your table should have is one of the most important decisions you'll make. To make the right choices, think carefully about what you need to track.

There's a lot of detail to consider when you plan a database. As you work, you may find it helpful to take some notes or even draw a picture. You'll learn more tips for this kind of planning in Chapter 7. For starters, use these guidelines:

• Records are things, like people, buildings, invoices, payments, products, or orders. If a single record holds information about more than one thing, you're probably making a mistake. For example, in the database you're building now, the records represent people. If you later want to keep track of the invoices you send to your customers, you don't want to add fields like Invoice Number, Amount, and Due Date to this table.

  Why not? Because if you did that, each person would only have room for one invoice. If the same customer makes another purchase, you'd have to create a new record for each invoice and enter the person's information all over again. Even worse, when the customer gets a new address or phone number, you'd have to change it individually in every record.

  ---

  Note: If you have more than one thing to keep track of, there's a solution: Have more than one table: one for people and one for invoices, for example. If your business starts branching out, you may even need more than one database. You'll get into that in Part 4.

  ---

• Fields are attributes of the item in each record. First Name, for example, is an attribute of a person. Most things have lots of attributes, and that's why most records have lots of fields. Unfortunately, there's some gray area here. For instance, is Company Name an attribute of a person (the company he works for), or is it a separate thing? The answer depends on what you want to do with the Company Name information. Again, Part 4 will help you work through these tough issues. For now, a simple rule to follow is, if you're only tracking one attribute of a thing, it's usually safe to make it a field. For instance, if you just want to keep track of the company a person works for, and don't plan to use the company's main address, phone number, or Web address, then you probably need only a simple Company Name field on each record.

So far, you know that your database will track people, and its fields will include any important attributes about a person. You might be tempted to have Name, Address, Phone Number, and Email Address as your fieldsbut you'd be making a mistake. What happens when you want to find all the people in Washington State? A search for Washington in the Address field will find them, as well as everyone on any Washington street and everyone in a town called Washington. Same goes for first and last name. You need a little experience and some careful thought to pick just the right fields. It's usually a bad move to have different bits of information in the same field. Instead, think about what elementsno matter how smallare important to how you'll search, sort, analyze, or otherwise access your records later. In database lingo, those bits of information are individually significant, and each one should get its own field.

For example, the first line of a person's address often contains several pieces of information: a street number, street name, direction (north, south, east, west), and an apartment, building, or suite number. Should these all be separate fields, or is one field enough to hold the whole street address? Are all these bits of data individually significant?

Usually, you can use just one field for the entire street address, since you never have reason to divide it into smaller elements and work with them separately. But suppose your work requires you to send different marketing materials to different people depending on which part of town they live in. You'd need to isolate people by street name (all the people on Hawthorne Street) or by ranges of street numbers (1000 to 1400 Elm). In this example, street name and house number are individually significant, and you'd want to create a separate field for each.

For your People database, you'll start off with fields to hold all the basics: Name, Phone Number, Street Address, City, State, Zip Code, and Email Address.