How should I structure my settings table with mysql?

Question

I want to use MySQL to store a bunch of admin settings - what's the best way to structure the table?

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?

Answer 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, of 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');

:-)

Answer 2

As usual, it depends. Solution 1 is simpler. Sol #2 easily integrates with ORMs, but may hit DB row size limitations. Google for OTLT (as in One True Lookup Table problem) How much settings do you have(few? dozens? hundreds?) How often will you need them?

Answer 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.