Enterprise Data Modeling in FileMaker -- Relationship Design

Expert

Apply entity-relationship modeling principles to FileMaker solutions at enterprise scale, covering normalization, surrogate keys, and graph organization for hundreds of tables.

What you'll learn

How to apply 1NF, 2NF, and 3NF to FileMaker table design
Why surrogate keys (serial numbers) are preferred over natural keys
How to organize the relationship graph for 50+ table families
How to document a large FM data model for team maintenance

Enterprise-scale FileMaker solutions (50+ tables, hundreds of thousands of records, dozens of concurrent users) require careful data modeling decisions that small solutions can ignore. This lesson applies entity-relationship (ER) modeling theory to FileMaker -- covering normal forms, surrogate vs. natural keys, and graph organization strategies that scale to hundreds of table occurrences without becoming unmaintainable.

1/4

Normal forms applied to FileMaker

First Normal Form (1NF): no repeating groups -- use related tables, not multi-value fields. Second Normal Form (2NF): non-key fields depend on the whole primary key, not just part of it. Third Normal Form (3NF): no transitive dependencies -- a field should depend only on the primary key, not on another non-key field.

TEXT

// 1NF violation: storing multiple phone numbers in one field
// PhoneNumbers: "555-1234, 555-5678, 555-9999"
// Fix: separate ContactPhones table with ContactID, PhoneType, PhoneNumber

// 2NF violation (in a composite-key table):
// OrderLines: OrderID, ProductID, ProductName, Quantity
// ProductName depends only on ProductID, not the full (OrderID, ProductID) key
// Fix: move ProductName to the Products table; reference via ProductID

// 3NF violation:
// Employees: EmployeeID, DepartmentID, DepartmentName
// DepartmentName depends on DepartmentID (a non-key field), not EmployeeID
// Fix: separate Departments table; store only DepartmentID on Employees

Optimizing Portal Performance Complete Relationship Design Review