This series covers a brief history of the 2 historic patterns for implementing User Defined Fields in a CRM, the upcoming hybrid solution that provides the best of both worlds, and how to evolve your existing CRM to the latest pattern.  If you care about CRM performance, scaling, or cost, this series is for you!

What are User Defined Field Patterns?

Every CRM provides a basic fields for defining a customer.  Every CRM’s basic field set is different depending on the CRM’s focus.  So, every user of a CRM needs to expand the basic definition in some way.  Birthdays, purchase history, and interests are three very common additions.

The trick is allowing users to define their own fields in ways that don’t break your CRM.

The Three Patterns

At a high level, there have been three major architectures for implementing Custom Fields.  Most of the design is driven by the strengths and weaknesses of the underlying database architecture.

Pattern 1, generalized columns in a database, spanned the dawn of time until the rise of NoSQL around 2010.

Pattern 2, NoSQL, began around 2010 and continues to today.

Pattern 3, JSON in a relational database, began in the late 2010s and combines the best of the two approaches

Pattern 1 – All in a Relational Database

Before the rise of NoSql there was pretty much one way to build generic user defined fields.

The setup is simple, just 3 tables.  A table of field definitions, a table for contacts, and a relational table with the 2 ids and the value for that contact’s custom field.

The Pros

• This design is extremely simple and can be implemented by a single developer very quickly.
• Basic CRUD operations are easy and efficient.

The Cons

• Building search queries requires complicated techniques like metaprogramming.
• Every search criteria results in a join against the ContactFields table.  This results in an exponential explosion in query times.
• The lack of defined table columns handicaps the database’s query optimization strategies.

Conclusion

The classic relational database pattern is easy to set up, but has terrible scaling.  This super simple example would bog down by 1,000 contacts and 50 fields.  

There are lots of ways to redesign for scale, but this is a SHORT history.  Suffice it to say that it takes extremely complex and finicky systems to scale past 100,000 contacts and 1,000 fields.

The solutions to the classic pattern’s scaling led to the NoSQL revolution, covered in part 2.