Best way to store user notification prefrences in database

53
December 02, 2020, at 01:30 AM

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?

Answer 1

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

READ ALSO
MailGun results in 401 forbidden using nodeJs

MailGun results in 401 forbidden using nodeJs

I am trying to send an email using mailgun and nodejsI took the code MailGun provides you and added my domain name and api key:

47
Do something if product type is changed

Do something if product type is changed

I have a custom WooCommerce product typeIf you switched between the standard WooCommerce type the input are cleared but this does not work for custom types

65
Why IntelliJ has inspections for generic usage disabled by default?

Why IntelliJ has inspections for generic usage disabled by default?

Why does IntelliJ has inspections for generic usage disabled by default?

68
Dynamically check value and display alarm popover

Dynamically check value and display alarm popover

I want to validate user input so he only provides numbers from a certain range with 0,5 stepsBut I want my website to do it every time user swaps to another input form, not after he sends the data to my view

33