Database user table design, for specific scenario

Question

I know this question has been asked and answered many times, and I've spent a decent amount of time reading through the following questions:

• Database table structure for user settings
• How to handle a few dozen flags in a database
• Storing flags in a DB
• How many database table columns are too many?
• How many columns is too many columns?

The problem is that there seem to be a somewhat even distribution of supporters for a few classes of solutions:

• Stick user settings in a single table as long as it's normalized
• Split it into two tables that are 1 to 1, for example "users" and "user_settings"
• Generalize it with some sort of key-value system
• Stick setting flags in bitfield or other serialized form

So at the risk of asking a duplicate question, I'd like to describe my specific scenario, and hopefully get a more specific answer.

• Currently my site has a single user table in mysql, with around 10-15 columns(id, name, email, password...)

• I'd like to add a set of per-user settings for whether to send email alerts for different types of events (notify_if_user_follows_me, notify_if_user_messages_me, notify_when_friend_posts_new_stuff...)

• I anticipate that in the future I'd be infrequently adding one off per-user settings which are mostly 1 to 1 with users.

I'm leaning towards creating a second user_settings table and stick "non-essential" information such as email notification settings there, for the sake of keeping the main user table more readable, but is very curious to hear what expects have to say.

Answer 1

Seems that your dilemma is to vertically partition the user table or not. You may want to read this SO Q/A too.

Answer 2

i'm gonna cast my vote for adding two tables... (some sota key-value system)

it is preferable (to me) to add data instead of columns... so,

add a new table that links users to settings, then add a table for the settings...

these things: notify_if_user_follows_me, notify_if_user_messages_me, notify_when_friend_posts_new_stuff. would then become row insertions with an id, and you can reference them at any time and extend them as needed without changing the schema.