• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Part III: Creating & Designing Databases > Creating Relational Databases

Chapter 14. Creating Relational Databases

As you build databases, sooner or later you want to go relational, that is, to connect one database to another. Relational databases offer a way to make that connection—without slowing performance or requiring gigabytes of extra storage—by sharing data rather than duplicating it.

Instead of creating one big database packed with everything you might ever need to know about a subject, it makes far more sense to create a number of much smaller relational databases. Not only will relational databases help you focus on what's key to each database—contacts in one database, products in another, invoices in still another—they take up far less space. All your product data, for example, stays in the product database. If you need the price of a product while generating an invoice, the relational link lets you see the price data without actually copying it into your invoice database. That way you get speed—without the bulk of FileMaker's original non-relational, flat file design, which required that each database contain all the data it needed. One final advantage of relational databases: Different users can update or redesign one of the databases without forcing everyone else to stop using the related databases.

Despite the obvious advantages of relational databases, building them can quickly become a chicken-and-egg problem of which database comes first and which does what. It's easy to become overwhelmed by the possible options. Start small and simple. You can always go back and create more lookups and relationships as you need them. To keep focused, approach the process in six sequential steps:

Step 1—Plan, plan, plan: Take a big-picture look at what you want to accomplish and sketch out the overall connections: what kinds of data you want to track and how many databases that might take. Then consider the connections needed between various files and fields. It's common to revise your notions of what information should go where as your plans progress, so don't use the computer just yet. Rely instead on paper, pencil, and eraser—especially the eraser—to draw boxes, arrows, or whatever it takes to identify the best way to organize the data.

Step 2—Define the fields: From the big picture, narrow down to the details of defining exactly what fields need to be in each database. Try to avoid duplicating fields and data from database to database. Instead think about ways the data can be shared.

Step 3—Define the relational links: Thinking of how to share data leads naturally to defining the relationships between various files. As the link between databases, the relationship is simply a formula that names two databases and a field that contains matching data in both databases. The relational link triggers an action, but defining exactly what that action should be comes in the next step.

Step 4—Put the links to use: The actions triggered by a relationship—what FileMaker calls lookups and portals—can be limited to one field or involve the whole database. See Lookups vs. Portals to better understand the pros and cons of both.

Step 5—Create layouts: Only after the first four steps should you actually begin creating layouts for your lookups and portals. Aim for layouts that present the related information as clearly as possible by using portals, buttons, and scripts to tuck the relational "wiring" out of sight.

Step 6—Enter the data: Now that you've built destinations for your data, you can start importing existing files or entering information record by record as you use your new relational system.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint