If you’re building a database from scratch, congratulations. You’re free to do it right. Of course, you still have to start from scratch. It’s a bit like building a new house versus restoring an old one. The new house gets a new foundation using the latest materials but it also starts as a clean—and very blank—sheet on the drafting table. With renovation, if the foundation’s crumbling, you have a big old house to somehow hold in place while you do the repairs.
No matter where you start, however, you won’t be entirely free of constraints. If you’re building a database for your department, at some point, you’ll probably need to share some data with another department. Even the home office worker will want, from time to time, to share information with others. In either case, you may have to export your FileMaker data to another format. By planning carefully, you can make even that task relatively easy.
You know the saying: Hurry now, wait later. The time you spend in this little chapter with nothing more than a notepad and your thoughts will save you hours of frustration later at the keyboard. This is the secret to successful databases: they’re not really about data, they’re about people and how they work together. As odd as it might seem initially, the data itself doesn’t dictate the database design. Instead, the processes and procedures among people within an organization’s various groups drive the design. The very same data in a different organization might need a completely different design.
Any time you build a database, you inevitably face questions about the group it’s intended for: Where does this information come from? Who knows this? Who needs this? Why do we do it that way? Before you veer into an existential thicket, here’s the point: Take time to understand the users’ needs—along with their organization’s structure and information flow—and you’ll build a better database.
That, of course, means talking to people about how they use information. Even organizations that don’t already have a database still have information flows, whether it’s hidden in memos, spreadsheets, or the brains of those folks you find in every office who know where everything’s kept and who did what, when, and why. Talk to them. Not just at the beginning of the design process, but at every step. They know more about their needs than you do. Unless, you are the user. In that case, have a little heart-to-heart with your own self. You’ll probably learn something.
Paper can be an important clue to how people use data in any organization. Look for which reports get printed out regularly and what overhead slides get used in meeting after meeting. Both are signs of what people find useful. Examine not just what they contain but what form they take. You’ll find great ideas for what should be in the database and what layouts will be most useful. Don’t just mimic the hard copy, of course, but those papers will help you more than pushing people to jump feet first into an electronic approach.
The same go-slow, pay-attention approach applies in looking at your organization’s existing databases. Likely, you’ll find them everywhere: inventories, billings, and mailing lists. Some will still be in use, others long dormant. Almost any database older than seven years is going to be full of overlapping, redundant information. Don’t slight them, however. Even if they’re a bit creaky, they may contain useful data that you can import once you’ve built a new database.
In puzzling all this through, you may have some false starts. Don’t worry. Unlike some database programs that force you to anticipate all your needs up front, FileMaker is very forgiving: You can always go back and add fields, layouts, scripts, or even new databases as you need them.
Once you’ve done the planning, start listing the fields you’ll need for all the information you’ll want to track. If you’re building a customer database, for example, you’ll want the obvious fields for names, addresses, and phone numbers. You may also want a field or two or three for things like a customer’s email address, pager number, and weekend message service. Don’t forget that you’re not limited to just fields for text and numbers. How about a picture field in the product catalog? And while you can’t predict the future, the best databases anticipate growth and change. Need a crystal ball? Talk to those users again. To get started on using fields, see Defining Fields on page 107.
Next, list the possible layouts you’ll need. Assign a separate layout to each task: mailing labels gets its own layout, so do order invoices, summary reports, etc. You should also consider creating a different layout for each type of user. The sales folks, for example, probably need to see different data than the accountants.
All the action won’t be on the screen, so you’ll also need to think about layouts for printed reports, again using the layout-per-task rule of thumb. For more information, see Creating Layouts on page 137. To make layouts easy on the eyes and easy to understand, see Formatting and Graphics in Layouts on page 183. By the way, thanks to FileMaker’s lookups and portals, layouts aren’t confined to showing data from just one database. For help, see Creating Relational Databases on page 231.
You don’t have to start from scratch in building your layouts. Using Templates and Scripts on page 209 shows you how to customize the many templates built into FileMaker. The chapter also shows you how to use scripts to automate multi-step actions, which makes it easier on the user. Creating scripts can also be a great way, for example, to find, sort, and highlight a group of records that show a pattern only FileMaker-savvy users could unearth without a script.
Finally, list the various databases you’ll need, creating a separate database for each major category of information. For example, it’s much better to create one database for products and another database for vendors rather than combine all that information in a single database. That’s where FileMaker’s relational abilities come in. Again, take your time. Building relational databases calls on everything else you’ll learn in this section: defining fields, creating and formatting layouts, and using scripts. For more information, take a look at Creating Relational Databases on page 231.