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:
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.
|UP TO SPEED
When making a database, you need to understand the concept of a table. In a particular table, every record has the same kind of information. If you see a field called Email Address on one record, there's a place to put an email address on every record. If you want to record someone's birth date, you need to add a field for it. When you do, every record gets a birth date field, even if you only type it in on one record.
When you're deciding what fields to put in your database, it helps to imagine a real table, or a spreadsheet.
In a sense you have rows, which are the records, and columns, which are the fields. If you have a column in one row, it's obviously there for every row. If you add a new column, even after you've added 100 rows, that column is available to all the rows at once.
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.
Part I: Introduction to FileMaker Pro
Your First Database
Organizing and Editing Records
Building a New Database
Part II: Layout Basics
Advanced Layouts and Reports
Part III: Multiple Tables and Relationships
Multiple Tables and Relationships
Advanced Relationship Techniques
Part IV: Calculations
Introduction to Calculations
Calculations and Data Types
Part V: Scripting
Part VI: Security and Integration
Exporting and Importing
Sharing Your Database
Part VII: Appendixes
Appendix A. Getting Help