25

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?

reformed
  • 3,922
  • 9
  • 51
  • 77
Cam
  • 13,963
  • 16
  • 70
  • 121

4 Answers4

30

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');

:-)

Cam
  • 13,963
  • 16
  • 70
  • 121
Ben Everard
  • 13,254
  • 12
  • 63
  • 95
  • 1
    +1 for explicitly pointing out that I needed a primary key, since I forgot it in my original post ;) ... Looks pretty good. I especially like that, using this setup, I could make a function that would allow me to select a setting (or multiple settings at a time), which would be very handy. – Cam Mar 18 '10 at 11:54
  • 2
    I concur, I've added another possible SQL statement, we use this method on our ERP system, holds lots of settings. :-) – Ben Everard Mar 18 '10 at 11:56
  • 1
    What if you want to store different types in "Value", is it acceptable to later make the cast in your code to the type you expect? If you have very few settings and they are all different types i.e. string, int, float, would it make more sense to use the second schema? – radtek Sep 29 '16 at 14:19
  • 1
    @radtek you could have a column type (if you want to store it with the value), and then one column for each type you want to have. The problem with this approach is that you will have lots of nulls although you win in flexibility. – PhoneixS Aug 31 '17 at 08:22
  • how to update values in this table could you please share the query – Saeed Ansari Mar 01 '18 at 15:49
  • this looks like EAV, an anti-pattern? – waspinator Jan 23 '20 at 03:17
4

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.

reformed
  • 3,922
  • 9
  • 51
  • 77
1

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.

Paul Sasik
  • 73,575
  • 18
  • 144
  • 180
0

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?

Dercsár
  • 1,516
  • 2
  • 12
  • 26
  • Dozens. I may need some of them as often as every pageview. That is, an admin setting may affect the view of certain pages every time they are displayed. As an example, one of the settings would be the name of a featured product, and another setting would be the filename for the gif image of said product. – Cam Mar 18 '10 at 11:54