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 a single person can't hold more than one official post at once.)

Each entity in 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, and then add a TownOfficial table and join the two in a relationship. The following sections describe how.

Creating the First Table in a Multitable 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 get within it a single table, also called Town, and the option to add fields to that table. The initial field definition might look as shown in 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 __kp_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 upper left of the Define Database dialog called Table. In a multitable system, this menu names the table you're currently working with, and lets you switch easily among field definitions for different tables.

Adding a Table to a Multitable System

That takes care of the Town table. To add a table for TownOfficer, stay in the Define Database dialog, but switch to the Tables 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 and click Create, and the new table will be 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 __kp_TownOfficerID. Like __kp_TownID in the Town table, this is the primary key for TownOfficer. Notice also the field called _kf_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.

Note

You might find the naming convention used here for key fields a bit puzzling. For primary keys, we precede the field name with a double underscore (__), and then "kp" to signify a primary key. For foreign keys, we precede the field name with a single underscore and the designation "kf". The effect of this convention is to cause all the key fields to sort to the top of an alphabetized field list in FileMaker, and further for the primary key to sort to the very top, above all foreign keys. This makes it very easy to access the keys when you're building relationships in the Relationships Graph.

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 __kp_TownID field in the Town table occurrence, and drag until the mouse is over the corresponding _kf_TownID field in the TownOfficer table occurrence. You should see a line extend from one table to the other. 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 the TownOfficer table occurrence. This is none other than the indicator that you're accustomed to seeing on the ERDs from the preceding 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 relationshipit 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 multitable systems.