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

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.

Answer 2

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 3

It will depends on the technology you are using. For example in a PHP Symfony Project, settings are mainly stored in flat files (Json, xml...).

I worked on many big web applications for clients. Key/value table is commonly used to store simple settings. If you need to store more than one value you have to serialize them, so it's a little bit tricky.

Keep in mind to cypher sensitive data such as passwords ( Sha256 + salt).

The best way is to create two tables. A table to store settings as key/value :

CREATE TABLE Settings (
    Id INT NOT NULL PRIMARY KEY,
    Key NOT NULL NVARCHAR,
    Value NULL NVARCHAR
    EnvId INT NOT NULL
);

Then you need a Environment table.

CREATE TABLE Environment  (
    Id INT NOT NULL PRIMARY KEY,
    Key NOT NULL NVARCHAR,
);

Don't forget the foreign key constraint.

Moreover you should create these tables in a separated schema. You will be able to apply a security policy by filtering access.

So you can work with many environments (dev, test, production, ....) you just need to activate one Environment. For example you can configure to do not send email in development env, but send them in production env.

So you perform a join to get settings for a specified environment. You can add a Boolean to easily switch between environments.

If you use a file (it doesn't need db connection) you can get something like that (Json) :

Env:
    Dev:
        Email: ~
    Prod: 
        Email: [email protected]

Answer 4

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 5

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 6

I think having a separate column for each setting is best for data type flexibility, because each column can have different data type for its setting data requirement