Creating Self-Relationships

A self-relationship sounds like another dubious (if not illegal) concept. We assure you that it's just one more tool in the database developer's arsenal. Self-relationships are an important tool even at the ERD level, though we didn't explore them in Chapter 5. We'll go into some detail here on how to create and use them.

Managers and Employees: A "Structural" Self-Relationship

Suppose that you want to create a database system that models a company's organizational chart. Your process description tells you that the system should show, for each employee, who that employee's manager is, and for each manager, a list of all that manager's direct reports. On the strength of this, you draw an ERD like that in Figure 7.10, with two separate entities, Manager and Employee, in a one-to-many relationship.

Figure 7.10. An ERD for a system that models an organizational chart.

This diagram seems fine until you come to list attributes for each entity. Besides the difference in "type" (manager versus employee), it appears that managers and employees look pretty much alike. They each have a first and last name, a department, an employee ID, and so on. Worse, as you think about it, many people in the chart are both a manager and an employeethey manage several others as managers, but also report to their own superiors.

It turns out that the correct way to solve such a problem is to collapse the two entities into one. The trick here is that the relationship of managers to employees doesn't quite fit the regular one-to-many or many-to-many criteria. The relationship is hierarchical, in the sense that one person can have many levels below her, and many above, similar to a family tree in some ways. A more accurate ERD for the system looks as shown in Figure 7.11.

Figure 7.11. An ERD for an organizational chart system, with attributes shown.

Each instance of the Employee entity has a __kp_EmployeeID, but also a _kf_ManagerID, which points to the particular employee who is this employee's manager.

Note

This does beg the question of what to enter into the _kf_ManagerID field when you get to the CEO's record. This can be solved by adopting some convention: either leaving the field empty or (probably better) setting it equal to the CEO's own __kp_EmployeeID.

Using Value Lists to Speed Up Self-Join Data Entry

How would you implement such a system in FileMaker? You'd begin with a one-table system matching the ERD. The structure is shown in Figure 7.12. You'll also find it useful to create a couple of value lists, based on field data from the Employee table. The first should be a two-field value list, in which the first field is the __kp_EmployeeID, and the second field is either the employee's last name or a calculation that shows both the last and first names together. You'll use that value list for data entry. The other list should be just alike, but contain only the __kp_EmployeeIDyou can use this value list to validate the contents of the _kf_ManagerID field. Figure 7.13 shows the value list definitions.

Figure 7.12. The FileMaker field structure for an organizational chart system.

Figure 7.13. Two value lists are defined in the system to aid with data entry.

At this point, without even touching the Relationships Graph, you've done enough work to begin data entry. If you apply the EmployeeIDNames value list to the _kf_ManagerID field on the Employee layout, you can begin entering data. Of course, the list is initially empty, so you need to either begin with the CEO and work your way down, or enter a bunch of other employees and come back and fill in their managers later. Figure 7.14 shows how the system might look during data entry.

Figure 7.14. Using a value list to select an employee's manager.

Creating Additional Table Occurrences to Display Related Data

The setup shown in Figure 7.14 works fine, but soon you'll find a few things missing. When you enter a manager ID, you probably want the manager's name to somehow appear as well. Likewise, on the layout for an employee who manages others, you'd probably like to see a list of those who report to the current employee.

These problems sound quite similar to the ones we covered in the preceding chapter (see the section "Working with Related Data" in Chapter 6). Whether you want to see a manager name when you choose a manager ID, or see a list of managed employees in a portal, some kind of relationship appears to be needed.

To make this work, you need to relate your single Employee table to itself. To do this, you need to add it to the Relationships Graphtwice more, in the form of two separate table occurrences. To do this, open the Relationships Graph and click the Add Table Occurrence button at the lower left. Add another table occurrence for Employee and name it Manager.

Now relate the two table occurrences by dragging a relationship line from the __kp_EmployeeID field in the Manager table occurrence to the _kf_ManagerID field in the Employee table occurrence. If the significance of that isn't obvious right away, think about it for a bit. If I'm an employee, my manager is the person with an employee ID equal to my manager ID.

That handles the employee-manager relationship. What about the relationship between an employee and those she manages? (Let's call them, clumsily, "managees.") To capture that relationship, you need to add another table occurrence. Once again, the table occurrence is based on the Employee table. Name it Managee and add it to the Graph. Then define an employee-managee relationship between Employee::__kp_EmployeeID and Managee::_kf_ManagerID. Looking again from the context of the employee, the "managees" are all those employees whose _kf_ManagerID is the same as a given __kp_EmployeeID. Figure 7.15 shows the resulting Relationships Graph.

Figure 7.15. This is the Relationships Graph for the employee-manager system.

With that relationship in place, adding the remaining data views you want is easy. To view the name of the employee's manager alongside the manager ID, drag a couple of additional fields onto the Employee layout. You would choose to look at fields from the Manager table occurrence, and choose to show first and last name.

Finally, on the same layout, you want a portal of managed employees. To do this, you'd add a portal to the layout, and this time you'd base it on the Managee table occurrence, bringing in (say) the FirstName, LastName, and Title fields. The resulting layout is shown in Figures 7.16 and 7.17 in Layout and Browse modes.

Figure 7.16. Adding a portal to the Employee layout to show managed employees.

Figure 7.17. Viewing the new Reports portal in Browse mode.

The manager-employee relationship is "structural," in the sense that you'd find it on a good ERD of this system. But, as you've just seen, to implement this structure in FileMaker, you need to add a couple of table occurrences to your system. From the standpoint of an employee, there exist both upward relationships (to your manager) and downward relationships (to your direct reports), and in each direction, Employee is relating back to itself. So one additional table occurrence is required for each relationship, for a total of three.

We recognize that this example is a little tricky, and we encourage you to work through it and think about it until it makes sense.

Note

Strictly speaking, you could accomplish much of what's described here with just two table occurrences, rather than the three we advocate. But you'd lose the capability to display both the name of an employee's manager and a list of his direct reports on the same layout. You'd need to view the manager name from one layout, and the data on managees from another layout, which is not very desirable.

An "Exclusionary" Self-Relationship

The preceding section looked at a fairly straightforward self-relationship (believe it or not). But, in the same way that it's possible to add complex criteria to relationships between different tables (as shown in an earlier example that displayed a portal of this year's invoices), it's also possible to design more complex self-joins. In this section we describe a useful form of relationship that we call an exclusionary relationship (also known as the everyone but me relationship). This type of relationship was rather challenging to create in previous versions of FileMaker, but is quite straightforward in FileMaker 7 and 8.

Suppose that you have a database containing information about model railroad hobbyists. One of the goals of the system is to give people who live near each other the chance to meet. Suppose that for a given railfan (call her Antigone), you'd like to display a list of the other railfans in her state. You'd specifically like to exclude Antigone's record from the list; you know she lives in Montana, and you're really just interested in pairing her up with her fellow Montanans for a few evenings of tracklaying and dispatching.

Let's say the basic data of your Railfan database looks like what's depicted in Figure 7.18.

Figure 7.18. This database needs to perform some sophisticated filtering of this list of railfans.

If you want to view other Montanans in a portal from Antigone's record, you need to create a relationship that somehow looks for others in the same state. If you think about that more precisely, from the viewpoint of Antigone's record, you want to see all other railfans who live in the same state as she does, but who do not have the same RailfanIDall Montanans except Antigone. This, again, is a kind of query, and you can implement it with a relationship.

To make this happen, go to the Relationships Graph and add a new table occurrence. Base it on Railfan and call it OthersInState. You need to create a relationship from Railfan to OthersInState based on multiple match criteria. Select State=State in the upper boxes and then click Add. Then select __kp_Railfan ID images/U2260.jpg border=0> __kp_RailfanID, and then click Add again. Figure 7.19 shows the Edit Relationship dialog as it will appear after this step.

Figure 7.19. This relational match finds all other railfans in the same state as the current record.

You can now create a portal on your main Railfan layout that uses this new relationship. The resulting display might look as shown in Figure 7.20.

Figure 7.20. The portal on this layout shows all other records that share the current record's State. The current record will always be excluded from the list.