开发者

Database user table design, for specific scenario

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 settin开发者_如何学Gogs
  • 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.


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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜