Migrating Legacy Databases to FileMaker Relationships

Expert

Plan and execute a migration from a flat, denormalized legacy FM database (or external data source) to a properly normalized relational structure.

What you'll learn

How to analyze a flat database for normalization opportunities
How to execute a staged migration without downtime
How to validate data integrity after each migration step
How to decommission the legacy structure once migration is proven

Many FileMaker solutions start as flat single-table databases built by non-developers. Over time, they accumulate duplicate data, comma-delimited lists in fields, and unmaintainable workarounds. Migrating to a properly normalized structure requires careful planning, staged execution, and robust validation to avoid data loss or corruption in a live production system.

1/4

Legacy structure analysis

Before writing any migration code, document every field in the source structure, identify repeated groups, multi-value fields, and fields that could be foreign keys. Map each to the normalized target structure.

TEXT

// Legacy table: Orders (flat, all in one table)
// Fields:
//   OrderID, CustomerName, CustomerEmail, CustomerPhone,
//   Item1Name, Item1Price, Item1Qty,
//   Item2Name, Item2Price, Item2Qty,
//   Item3Name, Item3Price, Item3Qty,
//   TaxRate, Total, Notes

// Problems identified:
// 1. Item1/2/3 repeating groups -> normalize to OrderLines table
// 2. CustomerName/Email/Phone stored on every order -> normalize to Customers table
// 3. TaxRate stored per order -> FK to TaxRates table

// Target structure:
// Customers (CustomerID, Name, Email, Phone)
// Orders (OrderID, CustomerID, TaxRateID, Notes, OrderDate)
// OrderLines (LineID, OrderID, ProductName, Price, Quantity)
// TaxRates (TaxRateID, Rate, Description)

Normalized vs. Denormalized Design in FileMaker Relationship Design for Reporting