I've recently embarked on scaling up a side hustle which entails storing a large amount of user settings and customisations that they can make to a web platform. The product is essentially white label and the customer can change any number of settings to brand, open and close off many features. The level of customisation can even go down to the text that is displayed to their users.

Currently, I've been building these one off for customers to test the water (will someone pay for this?) and storing settings/customisations in JSON files. It's been quick and easy, but now I'm looking to scale to a platform where there could be many multiples of users as well as sharing amongst teams. In short, the flat JSON files aren't going to scale and I'm looking into storing settings/customisation in a database.

Whilst it would be easy for me to look at document stores such as MongoDB to mimic closely the functionality I already have with my JSON files, I've also been pushing some relational issues into those JSON files which really, I would rather have sat in a relational database. Without wanting to proliferate the amount of databases I have in my architecture (albeit if that's the answer then why not!) I have been thinking long and hard about the problem and the potential options.

After some deep thought. I came up with 4 scenarios. But first a little bit of information about how a setting can look:

• A single text item, typically for application names and titles.
• An array of items, usually used for storing a number of selected tags or items.
• A string, that has a default value, but can be customised by a user. Typically used for naming items or message text that can be changed.
• Numerics: Usually stored for min and max limits or definitive values in the application.
• Booleans: Typically stored for features to be turned on / off

The important part I cant forget though, is that there are always defaults. If a user does not adjust a setting, then a default value should be used. This is especially important for the text content shown on screens as well as messages pushed out to users.

Now, the methods and scenarios I've come up with after some brainstorming to potentially manage these settings and customisations:

A single row of settings per customer

In this scenario using a relational database I could store all of the settings required for each users application in a single table "settings", with each setting taking up a column and each user taking up a row.

Whilst this would be the simplest approach to get started, I don't feel like it has longevity. There are problems with ongoing changes I might need to make as well as "default values" which some users might not have based on the old schema. I'd need to go through lots of processing to update users and ensure there are no conflicts. However, as far as querying to return a users settings, this offers real simplicity.

JSON Storage

In this scenario I could take my existing JSON and push it into the relational database such as Postgres for storage. This would reduce the overhead of needing 2 databases (no-sql vs relational) whilst also keeping my model close to what I have. The big benefit is I dont need to worry about my schema ahead of time and I can add and remove setting as I wish without causing too many problems for other users.

With that flexibility though, doing any kind of big updates across my data are going to be a pain and trying to work with the data any further than simple selects might bring with it some overhead.

A property bag / key value approach

In this scenario I could create a very simple table containing an id, user_id, key, value in my database and ensure I have the flexibility to store settings with ease, whilst also being able to query for a user_id and pull back the whole set in one go.

My worry with this approach is the adherence to standards, if any of the keys became inconsistent I can end up with a bit of a mess. I also believe this can lead to some really bloated out table in my database. Assuming each customer had in the range of 200 settings, this can quite easily become a million rows with just 5000 users. I shouldn't be concerned with query performance on a million rows I guess.

An alternative?

My final option, well.. I feel like in each of the above scenarios I've tried to mention relational databases. But I have to face facts, each option actually is flirting with a nosql equivalent. Key value stores such as redis could be an option, although persistence and decomposing some long values might be a bit too inflexible. Document store such as MongoDb/Couch would match closely to my current model, giving each user their own settings document and pulling it back nice and easy. Whilst that sounds like a nice scenario, it means I'd probably also need to spin up a relational database to handle the features of the platform which are clearly relational. Do I want the extra infrastructure to look after and handling queries to two sources now instead of just one? Tough choice.

I'm interest to hear how others have approached this issue and also whether there are deemed any best practices? Whilst I've been quite wooly on the specifics of my project, I feel this is something others must have run into before!