cult3

Storing User Settings in a Relational Database

Feb 02, 2015

Table of contents:

  1. The problems associated with storing User Settings
  2. The different potential solutions for storing User Settings
  3. Why I’m choosing the Property Bag method
  4. Conclusion

Last week we looked at Setting up a Notification system in PHP for Cribbb. In my opinion, one of the most important aspects of a good notification system is the ability for the user to decide which notifications she wants to receive.

Notifications can be really annoying, especially if the user gets bombarded with emails they do not want. Every notification system should give the user granular control over what the emails they receive and what should be ignored.

However, this presents the problem of how do we store the user’s settings in a relational database?

In today’s tutorial we’re going to be looking at the various options we have for storing user settings as well as the pros and cons of each.

The problems associated with storing User Settings

Before we look at the possible solutions, first I think it’s important to understand the actual problem we face. On the surface, it shouldn’t be too difficult to store user settings, should it?

In my experience, there are basically four problems that we need to factor into our decision making process.

1. You don’t know what the schema will look like as new settings are added / old ones removed Initially you will probably have a handful of user settings that you want to record in the database. However as the application evolves, this list of user settings is highly likely to dramatically increase, especially if new features have associated user settings.

This makes it impossible to predict what the database schema should look like ahead of time.

2. It’s “expensive” to add a column to each record of a table with a lot of records. If you had a settings table with a row for each user, adding an additional column for a new user setting is going to start to get pretty difficult once you have a few million rows.

You can get away with this in the early days of an application or for periodic schema changes, but it’s not something that you want to be doing on a regular basis.

3. When a new setting is introduced, existing users won’t have that setting If your application has been running for a year and you introduce a new user setting, all of the first year’s users won’t have that user setting record in the database.

This means you either have to establish a default setting for a user with no record, or do a mass update to insert the default setting for all the users without that setting.

4. You usually have to query for settings Normally you will have to query the database to check whether a user has a particular setting, rather than load all of the user settings to find out one particular property.

This means you need to store the data in a way that still allows you to query for individual settings.

The different potential solutions for storing User Settings

In my experience, there are basically three ways of solving this problem using a relational database. Each of these solutions have positives and negatives, and none of them are perfect by any stretch of the imagination.

Single Row

The first solution is to have a settings table where each user has a One-to-One relationship with a single row. Each setting has a column on the table and so pulling the row from the database contains the user’s settings.

I think this is a great solution if you know you only have 5 user settings and you will never, or very rarely, need to add or remove settings. It’s also important that all users have the same setting options, otherwise you get yourself into a mess of NULL columns and figuring out which users should have which settings.

The big benefit of this approach is that you will only ever have as many settings rows as you do users rows and it is very easy to clean up the settings table when a user is deleted.

This approach also makes it really easy to query as to whether a certain user has a certain setting.

However, I think if the user settings of your application are likely to be volatile, or you allow third parties to add their own settings, this approach will definitely not work.

Serialised Blob

The second option is to use a serialised blob to hold the settings. This means you take an array or object of settings, serialise it to JSON (or a similar format) and then store the output in a text column in the database.

The big benefit of this approach is that you don’t need to worry about the schema ahead of time and adding or removing settings from an individual user won’t disrupt the database.

However there are also a number of drawbacks to this approach.

Firstly, you can’t query against a blob of serialised text. This means you would have to pull the user and then unserialise the blob of settings each time. This means it will also be really difficult to do any kind of mass update of setting preferences to all users.

Secondly, it would be difficult to handle application wide settings as each user’s blob of settings would be slightly different.

And thirdly, it would be very difficult to add or remove settings from users without going through an expensive update process.

Property bag

And finally we have the Property Bag method. This is where you have a database table settings with columns for id, key, value and user_id.

Each user setting is stored as a key / value pair and so each user would have a One-to-Many relationship with this table.

The Property Bag approach makes it very easy to add or remove settings as we’re not constrained by a schema ahead of time. It’s also very easy to query against or to load all of the user’s settings in one go.

However, there are also negatives to the Property Bag approach.

Firstly, you need to ensure that the name of the setting keys are consistent. Any discrepancies could mean some user settings were not enforced correctly.

Secondly, the settings table could result in millions of records as each user has many different setting options. This could mean that the table eventually becomes difficult to work with, although that is probably a good problem to have.

Why I’m choosing the Property Bag method

I’ve decided to choose the Property Bag method for storing user settings for a number of reasons.

Firstly, the Property Bag method is really easy to set up. We only need a table with 4 columns and we’re good to go. There is no point in worrying about some complex situation in the future on day one of the journey.

Secondly, I’m kinda winging it for this project and so I have no idea what columns I’m going to need. I don’t want to limit myself to a schema that I pick now because in a couple of weeks it will probably change.

Thirdly, I’d like to open up Cribbb to a third-party ecosystem and so a Property Bag approach would make storing user settings easier for third-party developers.

As I mentioned above, I definitely do not think it is the perfect elegant solution, but in this case, I think it is the best approach.

Conclusion

Saving user settings can be quite difficult using a relational database as there really isn’t a perfect solution.

However, you can get a very long way with an imperfect solution. I don’t think there is much value in worrying about using the “most perfect” solution for every problem you face.

The majority of all small web applications could probably get away with using any of the three approaches I’ve looked at in this tutorial.

And of course there are more specialised tools available for large applications. For example, you might want to experiment with a NoSQL data store.

This is a series of posts on building an entire Open Source application called Cribbb. All of the tutorials will be free to web, and all of the code is available on GitHub.

Philip Brown

@philipbrown

© Yellow Flag Ltd 2024.