FileMaker Relationship Participation Types

From Dwayne Wright PMP
Certified FileMaker Developer

TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

For the first time in about 10 years, I’ve been asked to create an ERD. In the client struggles I’ve had in projects lately, I’m seriously considering not accepting any projects in which a client will not become actively involved in the ERD creation and review process. I’m seriously considering joining the mentality camp that “storming the beaches” with database design skills alone are officially over (even for the needing a database to store my recipes crowd).

One aspect of an ERD is diagraming the participation types of relationships, which are broken down into mandatory and optional. A mandatory relationship is when a record cannot occur if there isn’t an associated related record. An optional relationship is when a record in a table occur if there isn’t an associated related record. There are times in the planning process where you define a participation type and then change your mind later on.

For example, in InBizness SOHO, we have a contacts file. As we know, a company in the role of a client, vendor or sales lead may have multiple contacts within their organization. The contacts module is designed to allow you to have an almost unlimited number of contacts associated with the above companies or independent records of their own.

So you could say that the contacts module has a mandatory relationship to either a client, lead or vendor. But then you might want to have an interface to the contacts module for contacts with competitors, associates or members of your Rotary Club.

You may have a phone number table, that allows you to have multiple phone numbers for a contact. So you would likely never have a phone number record that isn’t related to a contact. So this would be an example of a mandatory relationship type.

You may also have a business rule that defines the relationship type. You might say that a lead record has an optional relationship type to a sales rep record in the staff table. Leads can be imported into the database and later assigned to the sales rep you want to work on them.

You might say (as a business rule) that there is a mandatory relationship between clients and a sales rep record in the staff table. So a sales rep record cannot be deleted until all their associated client records are re-mapped to an existing sales rep.

More info about the author and FileMaker in general, contact me at

© 2008 - Dwayne Wright -

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.