Many-to-Many Relationships with Attributes

Intermediate

Model many-to-many relationships using join tables that carry their own attributes -- the only correct approach for relationships that have data beyond the foreign keys.

What you'll learn

The difference between a pure join table and an attributed join table
How to model the join table in FileMaker
How to display and edit join attributes in portals
How to query both sides of the many-to-many from the join table

A simple join table (just two foreign keys) works for pure many-to-many associations. But real-world relationships often carry their own data -- a ContactProject join might have a Role, StartDate, and BilledHours. These attributes belong on the join table, not on either parent. Getting this right is foundational to normalized FM data modeling.

1/4

The attributed join table

An attributed join table has: a primary key (JUNCTIONID), the two foreign keys (ContactID, ProjectID), and the attribute fields (Role, StartDate, BilledHours). It represents the relationship instance, not just the connection.

TEXT

// Table: ContactProjects (join table with attributes)
// Fields:
//   ContactProjectID  -- primary key (auto-enter serial)
//   ContactID         -- foreign key -> Contacts::ContactID
//   ProjectID         -- foreign key -> Projects::ProjectID
//   Role              -- text (e.g., "Lead", "Contributor")
//   StartDate         -- date
//   BilledHours       -- number

// This table is NOT just a lookup table -- it has its own identity and lifecycle

Table Occurrence Groups and Context Management Self-Referential Hierarchies in FileMaker