Best way to store user notification prefrences in database

Question

I want to store user notification preferences. I do it like this:

A one-to-one relationship between Users and NotificationPrefrences table.
To make the foreign key in NotificationPrefrences table also the primary key, so I can easily get any user preferences with their ID (so I only need a single indexed column).
Each column in NotificationPrefrences table will be 1 or 0, depending on if user enabled that preference.

Is my approach good or not? If not, how can it be improved?

Doesn't sound bad to me. But I wonder if you really need another table. Since it's just a simple `0` or `1`, a column in the user table could suffice too. — sticky bit, Nov 30 '20 at 12:33
@stickybit well the notifications table could have for example 5 columns depending or more depending on what notifications they can turn on and off. In this case i dont want to be storing all these extra columns in user table, if that makes sense? — ravexu, Nov 30 '20 at 12:36
@NevilleKuyt Just something that wont slow down in the future with many entries, and something that allows future improvement like more notifications to be added. — ravexu, Nov 30 '20 at 14:31

score 3 · Accepted Answer · edited Jan 10 '22 at 09:33

You current design - a one-to-one relationship between "users" and "notificationPreferences" has a few benefits:

You have just a single join to retrieve notifications. This simplifies the code; in practice, it rarely has a significant impact on performance, though.
It makes your client code a little simpler - to find out all the possible preferences, and the preferences a given user has selected, you just need to make a single call, and iterate though the columns of the "noticationPreferences" table.

It's got a few downside, though:

To add new preferences requires a code deployment. This may or may not be a problem - I'd imagine a new preference would be part of a wider code deployment anyway.
It's very easy to end up with a table with lots of columns over time.
queries like "find all users who want to be notified about event X" would likely not be able to use an index, because binary flags usually have poor indexability (depends on your RDBMS)

There are a few alternative options.

The first is to model this as a many-to-many.

Users
-----
ID
Name
...


NotificationPreferences
-----------------------
Id
Name
...

UserNotificationPreferences
--------------------------
UserID
NotificationPreferenceId
Value (0/1) (see below!)

This would mean:

You can add new notification preferences without a code deployment.
You'd have an extra join to retrieve all preferences for a user; assuming you've got indexes on the primary and foreign key columns, you should not see a noticeable performance impact
Your client code needs to iterate over rows, rather than columns
If you adopted "an entry in this table means true" as a protocol, queries like "find all users who want to be notified about event X" would become "exists" queries, using two foreign key indices, and be really fast.

Another option is to store notification preferences as a bit mask.

Best way to store user notification prefrences in database

1 Answers1