FileMaker Extra: Complex Many-to-Many Relationships

Most of the examples in this chapter involved fairly simple, commonly found data modeling problems. But in the real world, matters can get quite complex. Some problems are hard to model in the language of relational databases. Others involve concepts you've already seen, but in more complex ways.

Let's say you've been asked to sketch out a database system for a trucking company. The company needs to track which drivers are driving which trucks , and where they're driving them. After some thought, you decide you're dealing with three entities: Driver, Truck, and Route. A route consists of a start location, a destination, and a number of miles driven.

With the entities fixed, you start to think about relationships. Driver and Truck seem to have a many-to-many relationship: one driver can (over time) drive many different trucks for the company, and one truck will be driven by many drivers (again, over time). Driver and Route also seem to have a many-to-many relationship. Route and Truck also are many-to-many, for similar reasons.

A first sketch of the system might look like Figure 5.21.

Figure 5.21. Initial ERD for a trucking system.

Earlier you learned how to resolve a many-to-many relationship. For any two entities that have a many-to-many relationship, you add a join entity between them that holds a primary key from each side of the relationship. You relate each "side" to the new join entity in a one-to-many relationship. If you "fix" the diagram of Figure 5.21 using those rules, you end up with something that looks like Figure 5.22.

Figure 5.22. The trucking system ERD with the many-to-many relationships resolved.

This diagram seems to be following the rules, but it's hard to know what it means or how it would work. What happens when trucker Samson drives truck T14302-B from Lubbock to Odessa? You need to record this fact by making entries in three different places ”once in each of the join entities. You note the association of the truck and driver in one place, the association of the driver and the route in a second place, and the association of the truck and the route in a third place. What's more, it's possible to make an incomplete entry. What if you make additions to only two of the three join tables? It seems very confusing.

Let's say that the trip starts on Monday and ends on Wednesday and you want to record that fact. With three join entities, where do you put that data? In theory, you'd need to put it into each of the three join records. That amounts to repetitive data entry, and in relational database modeling, a design that promotes redundant data entry is usually a sign that something's not quite right.

One clue is that these three associations (truck-driver, truck-route, driver-route) are not independent of each other. They all happen at the same time. When a trucker drives a truck from point A to point B, all three associations happen at once. Why not put them all into just one record? That's the right answer, as it turns out, and it implies the structure shown in Figure 5.23.

Figure 5.23. The trucking system ERD with a single central join entity.

What you're dealing with here is not three many-to-many relationships, but a single "many-to-many-to-many" relationship. This kind of structure is sometimes referred to as a star join . The central entity in a star join (which in the example stores information about the association between a truck, a driver, and a route) is sometimes called a fact table . If you see a number of join entities in your diagram that are "symmetrical," as they are here, and seem to capture different pieces of the same data, you might want to think about whether you have a star join of some kind on your hands.