ISO FileMaker Magazine: FileMaker Video Tutorials, Templates, Help & More

We are discussing handling People and Organizations in the same table, and similar design pattern applications to FileMaker in a series of podcasts called the Database Foundation Series on the Adatasol FileMaker Podcast, http://podcast.adatasol.com.

This is known as the Party supertype data model, originally published by Len Silverston and David Hay in the early 1990's (see Hay-Data Model Patterns, and Silverston-Universal Data Models Vols 1 & 2). As far as I know, Kevin Hammond and I were the first to adapt (most of) this complete model into FileMaker, with help from John Mark Osborne, John Sindelar, Joe King, and Chris Kubica.

There are three ways to map a supertype structure in FileMaker. Single table, SuperType table with 1:1 subtype tables, and separate tables (no supertype). A variation on the Single table approach using an Aggregation ontology, uses a dynamic attributes table, and was first discussed publicly by Matt Petrowsky in this video on Classification and Organization: http://www.filemakermagazine.com/videos/data-tagging-classification-vs-o.... We will discuss some of the issues in a future podcast with guest Corn Walker.

Separating a Person from an Organization is an important step, since neither actually belong to each other and keeping them together creates various relational anomalies (must have a company before a person, deleting a company orphans a person). Putting them back together in the same logical entity is where the common elements can be generalized.

The second concept to point out with this model is that people and organizations both act in relation to one another within the context of a ROLE (or roles). Customer is such a role, Salesperson is a role, Manager is a role, Parent is a role, Membership is a role. We often simplify this issue, but it is the ROLE of one party that is related to the role of another, not the party itself. Allowing for multiple roles is an advantage of this structure, where a person may simultaneously be an employee, manager, stockholder, and contractor.

A PartyRole (Maggie Jones, employee) connects to another PartyRole (Spacely Sprockets, employer) thru an association entity (AKA join table). Separating the Person-Person, Person-Organization, or Organization-Organization Role ASSOCIATIONS into a separate entity is challenging in FileMaker but it has many advantages in terms of flexibility, and allows for many to many associations and tracking of these associations over time (temporal versioning). I discuss some of the mechanics of this in an earlier podcast (June 2007) with Dan Weiss, http://podcast.adatasol.com/media/Filemaker_PodCast_06012007.mp3.

Winfried Huslik correctly points out that not only can person and organization be generalized into Party; but email address, postal address, telephone number, and web address may all be generalized into a Contact Mechanism supertype.