How to store site wide settings in a database?

Question

I'm debating three different approaches to to storing sitewide settings for a web application.

A key/value pair lookup table, each key represents a setting.

• Pros Simple to implement
• Cons No Constraints on the individual settings

A single row settings table.

• Pros Per setting defaults and constraints
• Cons - Lots of settings would mean lots of columns. Not sure if Postgres would have an issue with that

Just hard code it since the settings won't change that often.

• Pros Easy to setup and add more settings.
• Cons Much harder to change

Thoughts on which way to go?

Answer 1

Since your question is tagged with database/sql I presume you'd have no problem accessing an sql table for both lookup and management of settings... Guessing here, but I'd start with a table like:

settingName  value   can_be_null   minvalue maxvalue  description
TheAnswer      42       no            1       100     this setting does...
...

If you think about managing a large number of settings, there's more information you need about each one of them than just their current value.

Answer 2

I would go with the first option -- key/value pair lookup table. It's the most flexible and scalable solution, in my opinion. If you are worried about the cost of running many queries here and there to retrieve various config values, you could always implement some sort of cache, such as loading the whole table at once into memory. In addition to key and value, you could add columns such as "Description", and "Default Value", etc., and build a generic configuration editor that displays the Descriptions, etc., on-screen to help the user edit the config values.

I've seen some commercial applications with a single-row config table, and while I don't have direct experience doing development work against it, it struck me as much less scalable and harder to read.

Answer 3

A mixed approach is best. You have to consider what is best for each setting - which largely boils down to who would change each site-wide setting.

If you have a development server and a live server, adding new application settings can be awkward if they are solely in the db. You either need to update the database before you update the code, or have all your code handle the situation where a setting is unavailable. Obviously one common sitewide setting is the database name, and that can't be stored in the database!

You can easily end up with different settings in your test and live environments. I've taken settings away from the DB and into text files before now.

I would recommend having defaults in a 'hardcoded' file, which may then overridden by a key/value pair lookup table.

You can therefore push up new code without first needing to change the settings stored in the database.

Where there are a varying amount of values, or values that are always changed at the same time, I'd store the values as JSON or other serialised form.

Answer 4

I am including using a separate PHP script with just the settings:

$datatables_path = "../lib/dataTables-1.9.4/media";
$gmaps_utils_dir =  "../lib/gmaps-utils";
$plupload_dir = "../lib/plupload-1.5.2/js";
$drag_drop_folder_tree_path = "../lib/dhtmlgoodies/drag-drop-folder-tree2";

$lib_dir = "../lib";
$dbs_dir = "../.no_backup/db";

$amapy_api_registration_id = "47e5efdb-d13b-4487-87fc-da7920eb6618";
$google_maps_api_key = "ABQIABBDp7qCIMXsNBbZABySLejWiBSmGz7YWLno";

So it's your third variant.

I don't actually see what you find hard on changing these values; in fact, this is the easiest way to administrate these settings. This is not the kind of data you want your users (with different roles) to change via web interface. Products like PHPMyAdmin and Joomla happily use this approach.

Answer 5

I have used a mixed approach before in which i had put all the settings (which are not likely to change) into a separate PHP file. The individual settings (which are likely to change) as a key/value pair. That way I could reduce entries from the database thereby reducing my overall query time also this helped my keep the key size small .

Answer 6

Following Mike's idea, here is a script to create a table to save pairs of key/value. This integrates a mechanism (constraint) to check that the values is ok with respect to min/max/not null, and it also automatically creates a function fn_setting_XXXX() to quickly get the value of the corresponding setting (correctly casted).

CREATE TABLE settings
(
   id serial,
   name varchar(30),
   type regtype,
   value text,
   v_min double precision,
   v_max double precision,
   v_not_null boolean default true,
   description text,
   constraint settings_pkey primary key(id),
   constraint setting_unique unique(name),
   constraint setting_type check (type in ('boolean'::regtype, 'integer'::regtype, 'double precision'::regtype, 'text'::regtype))
);

/* constraint to check value */

ALTER TABLE settings
  ADD CONSTRAINT check_value
  CHECK (
        case when type in ('integer'::regtype,'double precision'::regtype) then
            case when v_max is not null and v_min is not null then
                value::double precision <= v_max and value::double precision >= v_min
            when v_max is not null then
                value::double precision <= v_max 
            when v_min is not null then
                value::double precision >= v_min
            else
                true
            end
        else
            true
        end
    and
        case when v_not_null then
            value is not null
        else
            true
        end
    );

/* trigger to create get function for quick access to the setting */

CREATE OR REPLACE FUNCTION ft_setting_create_fn_get() RETURNS TRIGGER AS
    $BODY$
    BEGIN
        IF TG_OP <> 'INSERT' THEN
            EXECUTE format($$DROP FUNCTION IF EXISTS fn_setting_%1$I();$$, OLD.name);
        END IF;
        IF TG_OP <> 'DELETE' THEN
            EXECUTE format($$
                CREATE FUNCTION fn_setting_%1$I() RETURNS %2$s AS
                    'SELECT value::%2$s from settings where name = ''%1$I''' language sql
            $$, NEW.name, NEW.type::regtype );
        END IF;
        RETURN NEW;
    END;
    $BODY$
    LANGUAGE plpgsql;


CREATE TRIGGER tr_setting_create_fn_get_insert
    BEFORE INSERT OR DELETE ON settings
    FOR EACH ROW
    EXECUTE PROCEDURE ft_setting_create_fn_get();
COMMENT ON TRIGGER tr_setting_create_fn_get_insert ON settings IS 'Trigger: automatically create get function for inserted settings';

CREATE TRIGGER tr_setting_create_fn_get_update
    BEFORE UPDATE OF type, name ON settings
    FOR EACH ROW
    WHEN ( NEW.type <> OLD.type OR  OLD.name <> NEW.name)
    EXECUTE PROCEDURE ft_setting_create_fn_get();
COMMENT ON TRIGGER tr_setting_create_fn_get_update ON settings IS 'Trigger: automatically create get function for inserted settings';