There’s no upper limit on the number of Contacts that customers will want to add to your CRM. Until you can support billions of contacts, there’s always an argument that you should support more.
On the other hand, Relationship and Management options are constrained by the choices you present to your users. You can’t support everything and need to consider which options make sense for your CRM. Every option adds complexity and cost to you; and cognitive load on your users. Adding features often decreases value.
When it comes to contacts, think about what scales. For everything else, remember that people have limits.
Part 2 covers how NoSQL emerged as an improvement over the classic relational database solution for User Defined Fields. NoSQL delivers speed and scalability by being expensive and fragile. In part 3 I’m going to cover the emerging Hybrid Database solution for User Defined Fields.
Hybrid Databases allow you to combine the best aspects of the relational and NoSQL models, while avoiding most of the downsides.
A hybrid implementation looks like this:
The hybrid model brings the data back to a single server, but without the Contact->Field relation. Instead the field data is stored as a JSON object in the Contact table itself.
Pros:
No meta programming and no filters, everything is back to SQL. Hybrid databases allow you to directly query JSON fields as if they were regular columnar fields.
You can create indexes on the JSON data. This is an improvement over both the classic and NoSQL models. It can significantly improve performance by allowing the database engine to optimize queries based on usage.
Having a single system makes things simple to set up and easier to maintain.
The database will enforce valid JSON structures, which makes it difficult to poison your data.
Cons:
There’s no enforced relationship between the JSON data and your User Defined Fields. This means that data can get lost because your system no longer knows to display or delete it.
While Hybrid Databases should scale far beyond the needs of your SaaS, the scaling isn’t quite as open ended as the NoSQL model. If you out-scale the Hybrid model, congratulations, your company’s services are in high demand!
Conclusion
If your SaaS is implementing User Defined Fields from scratch today, go with the Hybrid model. If you already have the classic or NoSQL pattern in place, it’s a good time to start thinking about how to evolve towards a hybrid solution.
I’ll cover how to evolve your existing solution in Part 4.
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.
