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

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.

Illuminant

Answer 1

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.

Pedagogy

I never new about the realize array, thanks for showing me that ollie! I will def go with the separate row structure. Thanks again for the links. – Illuminant

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

Misjudge

Answer 3

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.

Koon

Thats exactly what I was going to do, pretty much have a page where I can edit all the settings instead of going into the database manually or have it hard coded in a config.php file somewhere on my server. Wouldn't I still have to use ALTER QUERY COMMAND when I update the settings row value? – Illuminant

No, you will have to perform an UPDATE query. For exemple UPDATE settings_table_name SET setting_value = 'new title' WHERE setting_name = 'title' – Koon

Answer 4

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]

Hallway

The settings table is completely separated from the user table. but I am not using any kind of technology. it was originally hard coded and want to turn it into a flexible editable structure. – Illuminant

If i cypher it, how would i be able to extract the password when I need to send emails with lets say cron jobs? – Illuminant

You have to deal with key/value table. It will be better than creating a column by setting. For example if you want to add a new setting you just need to execute an insert statement. In the other structure you will have to make an alter column. – Hallway

You should never decode/decrypt a password; passwords should always be stored as hashes (if at all). If an authorised person/application can decrypt a password, an unauthorised one can possibly do the same. Don't restore passwords; reset them. – Exculpate

Very interesting design. I've never come across anything like this before. Thanks K4timini. – Dispersion

It's ok but if you have 100 rows if you want to update 100 by one click your update query will be very complicated actually not complicated need to write were condition for each row. But if you save the setting by JSON format only one row with key-value pair a text type field it will be very easy for me. – Sanders

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.

Lashoh

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

Airmail