Best way to store Site Settings data in a database table?

Question

I work in the market and wonder how best to manage website settings such as name, URL, https, contact email, version, etc.

I am trying to structure the table so that it can be easily updated and be able to add more and more settings to it.

I developed 2 structures, or saved them in one row, and the column names as the parameter name, and the column value of the row as the parameter value. and just repeat the column name of the value of the first row using mysql_fetch_assoc.

I also thought that for each setting there will be a new line of automatic increase. And turn it into an array to retrieve from the database to assign a column name to enter the column parameter name.

What will be your way to deal with this effectively. thanks.

Answer 1

A line for each individual parameter setting, using name / value pairs one for each line, is probably the best way. It is more flexible than many columns; if you add a parameter, you will not need to run any ALTER TABLE operation.

The Wordpress wp_options table works this way. Take a look here. http://codex.wordpress.org/Options_API

If you have a “complicated” option, you can serialize to create a php array to save it in one row of the table.

Answer 2

Both methods work fine. I would say that if you want to administer these settings in the admin panel, then one parameter per column is better, because you can add new settings on the fly with a simple INSERT request from your administrator. Which is better (safer) than an ALTER TABLE query.

Answer 3

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

I have worked on many large web applications for clients. A key / value table is usually used to store simple settings. If you need to save more than one value, you will have to serialize them, so this is a bit complicated.

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

The best way is to create two tables. Table for storing settings as a 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 an environment table.

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

Do not forget the foreign key constraint.

In addition, you must create these tables in a separate schema. You can apply the security policy by filtering access.

Thus, you can work with many environments (dev, test, production, ....), you just need to activate one environment. For example, you can configure not to send emails in env development, but send them in development mode.

So, you are connecting to get the settings for the given environment. You can add Boolean to easily switch between environments.

If you use a file (it does not need a db connection), you can get something like this (Json):

 Env: Dev: Email: ~ Prod: Email: [e-mail] 

Answer 4

First of all, I would consider one more thing, the configuration file ...

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

First of all, I would like to configure a configuration file with a database:

The big advantage of the database options over the configuration file is scalability, if you have many applications / sites that require these configurations, then go to the database, as this will allow you to copy the same configuration file several times with the whole version problem and file modifications on all these different sites

Otherwise, I will stick to the configuration file, since access is faster for the application, and the file may be available in case of sql server failure, in which case some configuration may still be relevant, the configuration file may also be included by your version control software. For any security reason, imagine that your database is split between many software products ...

Then, if you stick to the database, I would recomand one line one shortcut one config, I find it easier to manage records than the table structure, especially over time and over the evolution of your software. If other developers join your project, your table structure can quickly become a big mess:]

The final argument is security ... it’s good practice for the user to install a “DB” for software for a user who does not have permission to change the database structure, only the right to access / moidify the delete records;)