3

This is something that I have been wondering about for a while, and now that I am about to begin another project that requires this, I can't help but wonder if I am doing this the "right way".

Basically, in my API for, in this instance, an enterprise phone system - our client will be able to toggle several options like "forward calls to interactive menu" or modify the spoken greeting that callers hear.

Typically I handle something like this by creating a table in a MySQL database that only has one row that is updated each time the users makes a change to something.

I can't help but feel like this is overkill. Am I doing this the right way?

Luke Shaheen
  • 4,262
  • 12
  • 52
  • 82
drewwyatt
  • 5,989
  • 15
  • 60
  • 106
  • Configuration settings ought to go in two places: One for per-application / global settings. And another set for per-user or function settings, which since they need to be associated to something, do belong in the database. – mario Mar 30 '13 at 22:46

1 Answers1

4

If it's a global setting, that is, not user specific, I'd recommend two tables. One is your list of available settings with their definitions with a SettingID and the other table is just two fields: the foreign key SettingID and what the setting is set to.

If the settings are something that each user can set for themselves, simply add UserID to the second table.

Using two tables like this, so that each new setting is a new row, is more normalized then the way you suggested. It allows you to have your PHP code add settings or modify settings without having to modify your MySQL Database structure.

SettingsList
-----------
SettingID (Auto-increment)
SettingName
SettingDescription

UserSettings
------------
UserID (if using this field, make a compound primary key from SettingID and UserID)
SettingID (SettingsList foreign key, UserSettings primary key if not using UserID)
Setting
Community
  • 1
  • 1
Luke Shaheen
  • 4,262
  • 12
  • 52
  • 82
  • Love this, thank you! – drewwyatt Mar 30 '13 at 23:46
  • I do have one question though - what data type do you use for the setting in UserSettings? Some settings are boolean values, some aren't, is VarChar the way to go? – drewwyatt Mar 30 '13 at 23:51
  • 1
    @anwyatt Data types aren't my forte, but based off of what you just said, I'd say yes, `varchar` for this example only. You can't really do anything with numbers in `varchar`, such as sorting or addition, but for a `Settings` table you may not be doing anything except checking if it is 1 or 0. See this for more on integers in varchar fields: http://stackoverflow.com/questions/3008371/storing-numbers-as-varchar – Luke Shaheen Mar 30 '13 at 23:55