How should I structure my settings table with MySQL?

Question

What's the best way to structure a MySQL table for storing admin settings?

Like this?

Setting _|_ Value
setting1 |   a
setting2 |   b
setting3 |   c
setting4 |   d
setting5 |   e

Or like this?

|--------|_setting1_|_setting2_|_setting3_|_setting4_|_setting5_|
Settings |    a     |    b     |    c     |    d     |    e     |

Or maybe some other way?

Solution 1

Table name = 'settings'

name  | varchar <-- primary key
value | varchar

Then you can query like this:

SELECT * FROM settings WHERE name = 'default_printer';

This option is nice and easy and it will work well with 10, or 10,000 settings. With the other option you'll have to add a new column, which would be a completely pointless waste of time.

Edit

After your 1st comment you could choose multiple values like this:

SELECT * FROM settings WHERE name IN ('default_printer','default_page_size');

:-)

Solution 2

Consider the first option (Setting, Value) as columns. But also consider adding additional, meta columns as well, such as Description (would come in handy if you have alot of ambiguous settings), PreviousValue, LastUpdated, UpdatedBy, etc.

Solution 3

Your first example, name-value pairs or EAV, allows for a good deal more flexibility.

Check out the wiki page about EAV modelling in databases.