In part 1 – I covered the classic solution for User Defined Fields; simple but unscalable.
NoSQL emerged as a solution to relational fields in the late 2000s. Instead of having a meta table defining fields in a relational database, the User Defined data would live in NoSQL.
The structure would look like this:
This model eliminates the meta programming and joining the same table against itself. The major new headache that this model creates is difficulty in maintaining the integrity of the field data.
Pros:
- No complicated meta programming. Instead you write a filter/match function to run against the data in the Collection Of Fields.
- No more repeated matching against the same table. Adding additional search criteria has minimal cost.
- Open ended/internet level scaling. For a CRM or SaaS, the limiting factor will be the cost of storing data, not a hard limit of the technology.
Cons:
- Much more complicated to set up and maintain. Even with managed services supporting two database technologies doubles the difficulty of CRUD. Multiple inserts, multiple deletes, tons of ways for things to go wrong.
- Without a relational database enforcing the data structure, poisoned or unreadable data is common. Being able to store arbitrary data collections means you’ll invariably store buggy data. You’ll miss some records during upgrades and have to support multiple deserializers. You will lose customer data in the name of expediency and cost control.
- It’s more expensive. You’ll pay for your relational database, NoSQL database, and software to map between the two.
Conclusion
NoSQL systems solve the scaling problems with setting up User Defined Fields in a relational database. The scaling comes with high costs in terms of complexity, fragility and costs.
Reducing the complexity, fragility, and costs leads to the upcoming 3rd shift, covered in part 3.