User Defined Field Patters 3 – Hybrid Relations

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.