All in the one table OR not

Hi, i am developing a site at the moment and i need to work on the Notification Settings for my members.

I have ‘members’ table in my database that contains info such as:

  • id
  • first name
  • last name
  • gender
  • dob
  • location
  • email
    etc…

I have on average 10 different notification and email settings that each user can customise, such as:

email me when i am sent a new private message

Should i expand the ‘members’ table and add the 10 notification settings or should i create a separate ‘notifications’ table that will hold all of the notification settings and link it to the ‘members’ table with a foreign key, thanks in advance for you help…

Might be useful using a second table to hold settings.
As an example I would set it up like this:


user_id,
type, # value type, boolean string, integer, etc. (optional)
key, # what is the setting.
value

SELECT value FROM user_settings WHERE user_id = # AND key = #
SELECT user_id FROM user_settings WHERE key = "newsletter" AND value = 1


That way you only add settings for users that actually change them, otherwise you use a default value for them. And if you need to add new settings it does not require a change in the DB scheme. Now whether it is a good idea or not…that is up for debate.

The solution logic_earth provided is EAV (entity-attribute-value). It is a relational database anti-pattern but makes it very easy to add new settings without modifying the table schema. Really though EAV is best avoided unless your building a CMS based on that entire idea like Magento, Drupal 7 or WordPress. Otherwise, I would recommend a single table: user_settings and store each separate setting as an individual column. Much more concrete and simple. The whole EAV thing tends to bring about more issues than it solves though very clever and flexible. The significant advantage of EAV is adding new “virtual columns” through a UI without modifying the database schema. Though it comes with significant efficiency, complexity and clarity implications.

Hi, thanks for the replies… i was planning on using a separate column for each setting… but i was just wondering whether to add them to the ‘members’ table.

There are already 14 columns in the ‘members’ table, so i would only be adding an additional 10 columns for the notification settings to the members table… OR should i create an additional table for the settings…

I thought queries would process quicker if the were only querying the one table…

Anybody got anything else to add to this debate :slight_smile:

I wouldn’t base the decision on that alone. So long as columns are indexed correctly there isn’t going to be any noticeable efficiency implications. Base it on organization and clarity.

I would place them inside a separate table because than a context can be established without prefixing. Otherwise placing the settings in the user table would be most appropriate with some type of prefix like setting_ so that it is clear as to what the “settings” are. This would not be needed though in a separate table as the table itself would provide the context: storing user settings. Hence the name that was recommend: user_settings. Anyone with half a brain would figure out that the the table stored the “user settings”. However, it would be a little less clear if they were in the user table itself. Especially so if column names were not related in some way to settings with a prefix or something.

Ok cool, thanks… separate table it is :wink:

There is another way, try [google]bitmask php[/google] - it might not be the obvious solution but you dont mention it so…


1 [ ] send email on event a happening
2 [ ] send email on event b happening
4 [ ] send email on event b happening
8 [ ] send email on event d happening

So the value “17” in notifications column means do a, b, c, d.

The value “9” means do a and d only.