I am a Sr. Software Developer at Oracle Cloud. The opinions expressed here are my own and not necessarily those of my employer.
Storing records in SQL DB requires a fixed set of columns. User has
middle name) but having too many of them is not practical.
What if we had to build an online shopping mall with multiple
Stores. They all have
Purchases but store selling t-shirts will require
purchase record. Another store would require different custom fields.
We do not want to create optional columns
size in our DB. It will result in a messy database and require developers to make code/schema changes for new customers.
- Document structure
- Predefined columns
- Serialized data
- Custom tables per client
- SQL plus Key Value
If we are already using a document DB like Mongo or Couch we are not constrained by that rigid table structure. I used Mongoid Dynamic attributes in several applications which gave me ability to define these custom fields that are only present in some of the records. I previously wrote about that here
But often we NEED that SQL structure. We need joins and transactions or its simply not an option to move from existing MySQL or Postgres.
One option is to create a predefined number of fields of different datatypes.
Purchases table will have
num_field5. Then in
Store model we add
Store admins can configure these values via their dashboard. Then we create methods to display appropriate labels in the UI for end users.
The downside is it restricts us to only having a preset number of these custom fields and creates a bunch of optional columns in the DB.
The downside with this approach is that it can limit using rich SQL querying capabilities. How do we query all Purchases where size is large if size is serialized in this JSON field? Unless we are using latest versions of MySQL / Postgres we will have to do SQL LIKE queries which is slow and less reliable. But we can do queries like this which can solve a lot of our problems:
Custom tables per client
At a previous job we ended up building custom solution that created special tables (one for each customer). They had the columns that those customer’s user records needed and were linked to the main
Users table. It took a long time to build and maintain.
SQL plus Key Value
I would like to expore how can we solve this challenge by combining SQL DB with a Key/Value store like Redis.
Internal users can configure
store level. Then UI will display appropriate HTMl controls. When data is submitted to the server it can be saved like this:
To display data for internal reporting purposes we can do this:
- We can use rich data structures like Redis Hashes, Sets and Lists.
- Redis is FAST.
- We are introducing additional technologies into our stack. Our application needs to talk to both MySQL and Redis.
- We cannot use native DB transactions across MySQL and Redis and will need to build custom logic in our application to rollback.
- We cannot combine data attributes we store in Redis with the ones stored in SQL in our queries.
- Redis requires RAM which is more expensive. So if we are storing LOTS of custom attributes we need a big Redis instance.