Database Reference
In-Depth Information
Figure 5-6. This diagram doesn't look all that bad—except for one thing. The circled area, labeled
Rat's Nest, has a lot of lines among just a few tables. Expenses, Customers, and Jobs are all inter-
related—there's a relationship between Jobs and Customers, another between Customers and Ex-
penses, and a third between Expenses and Jobs. If you look closely, the same situation exists for
Customers, Jobs, and Invoices. In both cases, you have a better way.
But you don't need all those lines to understand all the relationships, and neither does
FileMaker. Now that you have an ER diagram, you can see that the line between Customers
and Expenses is entirely superfluous. Even if it weren't there, you could still see all the ex-
penses charged to a certain customer. Just find all that customer's
jobs
first (by following the
line from Customers to Jobs). Once you've found those jobs, you can look at the expenses
for each job. Since customers incur expenses only by way of jobs, you get exactly what you
want without an extra relationship. In other words, if two entities are connected by a path
along relationship lines—even
through
other entities—then they're related as far as
FileMaker is concerned. Your database can show you the expenses for a customer just as eas-
ily as it can show the jobs for that customer.
Figure 5-7
shows this concept.
When you're thinking about these implied relationships, pay attention to the crow's feet. If,
when moving from one entity to another along the relationship lines, you
ever
go through a
to-many relationship, then the larger implied relationship is itself to-many. This isn't just a
clever trick; it's actually intuitive. If a customer has more than one job, and each job has ex-
penses, then clearly a customer can have more than one expense.
