FileMaker Extra: Complex Many-to-Many Relationships

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 forms.

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 as shown in Figure 5.21.

Figure 5.21. The 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 as shown in 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 placesonce 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 associations 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.

Part I: Getting Started with FileMaker 8

FileMaker Overview

Using FileMaker Pro

Defining and Working with Fields

Working with Layouts

Part II: Developing Solutions with FileMaker

Relational Database Design

Working with Multiple Tables

Working with Relationships

Getting Started with Calculations

Getting Started with Scripting

Getting Started with Reporting

Part III: Developer Techniques

Developing for Multiuser Deployment

Implementing Security

Advanced Interface Techniques

Advanced Calculation Techniques

Advanced Scripting Techniques

Advanced Portal Techniques

Debugging and Troubleshooting

Converting Systems from Previous Versions of FileMaker Pro

Part IV: Data Integration and Publishing

Importing Data into FileMaker Pro

Exporting Data from FileMaker

Instant Web Publishing

FileMaker and Web Services

Custom Web Publishing

Part V: Deploying a FileMaker Solution

Deploying and Extending FileMaker

FileMaker Server and Server Advanced

FileMaker Mobile

Documenting Your FileMaker Solutions

Using FileMaker 8
Special Edition Using FileMaker 8
ISBN: 0789735121
EAN: 2147483647
Year: 2007
Pages: 296 © 2008-2020.
If you may any questions please contact us: