2

I need to add a table to store user settings. For the first time users will have about 10 different settings, but I'm sure there will be more.

What scalable structure of database table to store user settings would you suggest?

Any help would be greatly appreciated.

Thank you!

Peterim
  • 1,029
  • 4
  • 16
  • 25

2 Answers2

5

Settings table columns -: setting_id | setting_code | setting_name | setting_description

User table columns -: user_id | user_name | user_password | etcetera

User-settings table columns -: user_setting_id | user_id_fk | setting_code_fk | setting_value

structure like so:

Users --> User Settings <---- Settings

Hope this helps.

Bella
  • 3,209
  • 23
  • 26
  • 3
    That works if every setting_value is of the same data type. If they are of different types then it makes sense to create a column for each. Also if you have a column for each setting then you only need one table instead of three. – nvogel Jun 04 '10 at 19:20
  • 2
    Does this fall into the often frowned upon key value pattern? – tstyle Aug 03 '11 at 04:14
  • @sqlvogel I would think a Users table gets too wide if you include each setting as a column. 2 tables might be the best approach I think. – user3308043 Aug 21 '14 at 07:11
  • 3
    @tstyle Yes. And with user settings, that's how it's supposed to be. Denormalization has its place. This is that place. – user3308043 Sep 01 '14 at 23:17
2

Depends on the settings.
If it's just flags, you can use bit mask.

If it's strings - just make separate fields.
Do not make it "scalable". Databases must be scalable in length (and they are), not in width.
Just plan your database structure before starting a code.

If it will be poor plan - OK, you can add a few fields later. But do not make it on regular basis. It's a sign of poor database design.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345