Storing records in SQL DB requires a fixed set of columns. User has first name, last name, email, etc. But in a mutltitenant applications some records may require specific fields. We can have optional fields (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 size and color in purchase record. Another store would require different custom fields.

We do not want to create optional columns color and size in our DB. It will result in a messy database and require developers to make code/schema changes for new customers.

Document structure

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

class Store
  include Mongoid::Document
  has_many :purchases
end
class Purchase
  include Mongoid::Document
  include Mongoid::Attributes::Dynamic
  belongs_to :store
end

Predefined columns

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 text_field1 - text_field5 and num_field1 - num_field5. Then in Store model we add text_label1 - text_label5 and num_label1 - num_label5.

Store admins can configure these values via their dashboard. Then we create methods to display appropriate labels in the UI for end users.

class Purchase
  def get_text_label1
    store.text_label1
  end
  ...
end

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.

Serialized data

We could create a dedicated field to store serialized version of multiple custom fields. Both MySQL and Postgres support JSON data type.

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:

SELECT name, custom_fields->"$[0]" AS `custom_field1` FROM `purchases`;

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.

In Ruby on Rails there is an interesting gem redis-objects but the same approach could be adapated to other languages / frameworks.

class Store
  has_many :custom_fields
end
class CustomFields
  belongs_to :store
  # define name, html_control (input, boolean, select), required (boolean), ...
end
class Purchase
  include Redis::Objects
  hash_key :custom_values
end

Internal users can configure custom_fields on store level. Then UI will display appropriate HTMl controls. When data is submitted to the server it can be saved like this: purchase.custom_values.update({"size"=>"xl", "color"=>"red"})

To display data for internal reporting purposes we can do this:

@purchases.each do |purchase|
  purchase.user_name
  purchase.custom_values.each do |cv|
    ...
  end
end

Pros

  • We can use rich data structures like Redis Hashes, Sets and Lists.
  • Redis is FAST.

Cons

  • 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.