Mysql – Application Settings in Database

configurationMySQLPHP

In the past I have stored all generic settings and configuration items such as: custom application title, home address, version, debug, etc.. all in a "settings.php" file that I would include into all of my scripts.

I would assume that it is better practice to have a database table specifically setup for application settings such as these, in that way you dont need to edit a php file to adjust or make changes. Yet I am a bit cautious about how I would set this up.

There are two options I have been considering:

  1. I have a table with one row, many columns, making each column a unique variable value.
  2. I have two columns with the first being the variable name, and the second being the variable's value.

Any suggestions or things I should be aware of from a database design perspective?

Best Answer

Under Option 1, you are guaranteed a single row (if you remember to insert the one row firsthand).

  • If you want to introduce a new setting, you would have to do ALTER TABLE to add the needed column.
  • If you wanted to remove an old setting, you would have to do ALTER TABLE to remove the unneeded column.
  • You would have to assume to the presence of the column in the code. Otherwise, you may have to query information_schema.columns to make sure the column representing the setting needed exists in the table or not. Querying the table information_schema.columns is extremely bad in the presence of dozens (or even hundreds) of InnoDB tables in a heavy write environment. That could potentially lock information_schema.columns intermittently until it is free to read.
  • You should make sure the table count is always one(1).

Under Option 2, things are a little different

  • If you want to introduce a new setting, you would have to do an INSERT to add the needed setting as a row.
  • If you wanted to remove an old setting, you would have to do a DELETE to remove the needed setting as a row.
  • You have to query the table by variable name to see if the option is present.
  • No virtual limit to adding to or deleting a setting from a table

What do these Options have in common ?

  • If you leave old settings alone, this can allow for old versions of app code to use the old settings, but that just introduces clutter into the table.
  • Storage Engine should be InnoDB to allow multiple people to read from it.

Personally, I would choose Option 2 because creating new settings, optionally removing old settings, and checking for existing settings would all occur at one table. The method of retrieving settings would all be the same. Under Option 1, you would have to query information_schema.columns plus make certain assumptions about the settings table. The sequence of events for retrieving settings may change from code version to code version.

Related Question