CODEWITHSUNDEEP
sqldatabasedatabase-designsoftware-design
abcdefg
abcdefg

Reputation: 13

How to manage user notification settings like Facebook?

I'm trying to set up a user preferences similar to that of Facebook, where, say for each message channel or community you can set up notifications that can be sent to you. In addition, there are also some global preferences, where the settings do not belong to a channel or community. What is the best way to design a database to hold this information? From what I've read, these are some commonly-seen approaches:

  1. A simple table with columns user_id, preference_1, preference_2, etc. for a global preference list. And in the case of a chat channel, it would be columns user_id, chat_channel_id, preferences..., making multiple tables for each category (chat channels, communities, etc). I heard that this may be bad especially when you want to add new settings though, but I don't know too much.
  2. A key/value database, with a User table and Preference table leading to a User_Preferences table with keys corresponding to the user/preference and a corresponding value. It's sort of hard to imagine how I would be able to work this into my design, since I need settings per user per community, as well as settings per user, and a whole lot of other stuff.

Are there any other better approaches aside from these two that I can look into, maybe ones that don't utilize an RDBS?

Upvotes: 0

Views: 213

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Of the two methods you mention, the second seems most appropriate.

The first is not extensible. And it poses a bunch of problems. Searching for a specific preference requires looking through a bunch of columns -- and that generally means a full-table scan.

I wouldn't refer to the second method as "key/value". It is simply a junction/association table that connects users to preferences. You would want to extend the preferences table to have "community" preferences. This might be a separate table or just additional rows in a single table.

Your question doesn't have enough information to provide more detail on what the data model should look like, though.

Upvotes: 1

Related Questions