Best way of storing 'website settings' data in a database table?

Question

I am working on a marketplace, and was wondering what is the best way of handling website settings such as title, url, if its https, contact email, version, etc.

I am trying to structure the table so it is easy to update, and able to have more and more settings added to it to fetch.

I developed 2 structures, either keep it in one row, with column names as the setting name, and the row column value as the setting value. and just echoing the first row value column name with a mysql_fetch_assoc.

I was also thinking about having a new auto-increment row for every setting. And turning it into an array to fetch from the database to assign a column name for the column im fetching of the setting name.

What would be your way of handling this efficiently. thank you.

Answer 1

First of all it totally depends on your business requirement but as of now the best approach is to create a settings table scheme is below.

CREATE TABLE `settings` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` text NOT NULL,
  `type` enum('general','advanced') NOT NULL DEFAULT 'general'
);

Example

site_title = "example"
site_logo = "something.jpg"
site_url = "https://www.example.com"
address =  "#795 Folsom Ave, Suite 600 San Francisco"
email = "[email protected]"
mobile = "9898xxxxxx"

This is the best approach because you never know a when new key will be introduced. Here name will be the key and value will be value.

value column data type should be TEXT for long description.

I have taken one more column named type data type is ENUM which is to distinguish data. You can customize type as per the business logic.

Answer 2

First of all i would considere one more thing, a configuration file...

Then you should ask yourself what you need for your project...

First of all i would considere config file vs database :

The big advantage of databases options over a config file is the scalability, if you have many applications / sites requiering those configurations then go for the database as it would avoid you to copy several times the same config file with all the problem of versioning and modification of the file on all those different "sites"

Otherwise i would stick to the config file as access is faster for an application and the file may still be aviable in case of sql server outage in which case some config may still be relevent, the config file may also be include by your versioning software. For some security reason as well, imagine your DB is shared among many softwares ...

Then if you stick to database i would recomand the one row one label one config, i considere it easyer to manage records than table structure, specially over time and over evolution of your software.If other dev join your project your table structure may quickly become a big mess :]

The final arg is security... a good practice is to set the "DB user" form a software to a user which dosen't have DB structure modification rights, only the right to access/moidify delete records ;)

Answer 3

A row for each distinct option setting, using name/value pairs one per row, is probably the best way to go. It's more flexible than lots of columns; if you add an option setting you won't have to run any kind of ALTER TABLE operation.

The WordPress wp_options table works this way. See here. http://codex.wordpress.org/Options_API

If you had a "compound" option, you could serialize a php array to store it in a single row of the table.

Answer 4

The two ways works fine. I would say that if you want to administrate those settings in an admin panel, the one setting by column is better, because you can add new settings on the fly with a simple INSERT query from your admin. Which is better (more secure) than an ALTER TABLE query.