Creating a One-to-Many Relationship in FileMaker

Let's consider a simple case. We're doing a database for a municipal government. The database is intended to store information on all the towns in an area, as well as a list of government officials such as mayors, commissioners, and the like. If we follow the principles mentioned in Chapter 5 and try to think of this in entity-relationship terms, it should be clear that we have two different entities here: "town" and "town official." The two have a one-to-many relationship: one town may have many officials. (We'll assume for the sake of simplicity that single person can't hold more than one town official post at once.)

Each entity on an entity-relationship diagram (ERD) generally translates into one table in a FileMaker system. To make this happen for our example, begin by creating a database that initially contains just the Town table, then add a TownOfficial table and join the two in a relationship. The following sections describe how.

Creating the First Table in a Multi-table System

Again, when you create a FileMaker database for the first time, you get a single table with the same name as the database. If you create a new database called Town, you'll also get within it a single table, also called Town, and the option to add fields to that table. The initial field definition might look like Figure 6.1.

Figure 6.1. Field definitions for an initial table in a database of town information.

We've defined a number of basic fields containing town information. You should notice two things here. First, there is a field called TownID . That field will be the primary key field, which will be essential when it's time to build a relationship to another table. Notice also the small menu at the top left of the Define Database dialog called Table. In a multi-table system, this menu shows on which table you're currently working, and lets you switch easily among field definitions for different tables.

For a refresher on the details of creating fields within a single table in FileMaker, see Chapter 3, "Defining and Working with Fields," p. 63 .

Adding a Table to a Multi-table System

That takes care of the Town table. To add a table for TownOfficer, stay in the Define Database dialog, but switch to the Table tab. You'll see just one table, which in this example is called Town. To add a new Table, type the name in the Table Name box, click Create, and the new table is added to the list, as shown in Figure 6.2.

Figure 6.2. FileMaker's Tables view, showing a database with multiple tables.

You're now free to add fields to the new table. Figure 6.3 shows a suggested field list for the TownOfficer Table.

Figure 6.3. Field structure for a table of town officers.

Pay attention to two fields here. The first is TownOfficerID . Like TownID in the Town table, this is the primary key for TownOfficer. Notice also the field called TownID . This is the foreign key that makes it possible to specify in which town this particular town officer serves. The foreign key will be crucial to making the relationship back to the Town table. Later in this chapter we discuss the principles of making effective key fields in FileMaker.

For a refresher on primary and foreign keys, see "Understanding the Role of Keys in Database Design," p. 138

Adding a Relationship

There are now two tables, as well as the primary and foreign keys that good database design demands. To create a relationship between these two tables, move to the Relationships tab of the Define Database dialog. This window, known as the Relationships Graph, should have a couple of graphical elements already displayed. Each one represents one of the database tables that exist in this database. These elements are known as table occurrences . Each shows the name of the table it represents, along with that table's fields. Figure 6.4 shows the Graph with the two tables presented there.

Figure 6.4. FileMaker's Relationships Graph, with table occurrences for two tables.

Adding a relationship between these two table occurrences is simple: Position the mouse over the TownID field in the Town table occurrence, and drag until the mouse is over the corresponding TownID field in the TownOfficer table occurrence. You should see a line extend from one table to the other, and when you release the mouse, FileMaker creates the relationship and displays it as a link between one or more match fields at the top of the table occurrence pair. Figure 6.5 shows how the Graph will look as a result.

Figure 6.5. FileMaker's Relationships Graph, with a relationship between two table occurrences.

You might have noticed the "crow's- foot " at the end of the relationship line, where it touches TownOfficer. This is none other than the indicator that you're accustomed to seeing on the ERDs from the last chapter. It's intended to indicate the "many" side of a one-to-many relationship. Be warned , though! FileMaker provides this graphical adornment as a kind of a hint or guess about the relationship ”it may not always be accurate, though in this case it is. We explain that point fully in the next section, where we discuss the creation of key fields in FileMaker.

At this point you've seen how to add a new table to FileMaker's default one-table database configuration and how to define a one-to-many relationship between two FileMaker tables. The next sections clarify some important points about multi-table systems.