Storing application settings with different datatypes

I'm developing a web application and I plan to store application settings in the database in name,value fields (one row = one setting).

These settings range from directory paths to simple yes/no flags. Obviously, I can't store a path (string) into a boolean field.

Is my only option to make the value field a string and then interpret the value as necessary per setting, or is there some other strategy for this?

(I'm using PostgreSQL, but the answer should probably apply to any database server.)

The simplest way I can think of which keeps the types is the following: create as many columns in your application_settings table as many different types you have. This means normally a boolean, a numeric, a date and a text field. So your table would look like

CREATE TABLE application_settings (
      id serial PRIMARY KEY
    , setting_name text NOT NULL -- possibly UNIQUE
    , boolean_setting boolean
    , numeric_setting numeric -- or this can be an integer, too
    , date_setting date -- again, this can be timestamp, too
    , text_setting text
);

If you plan to keep only one row of each setting (no historical data, then set the setting_name field to UNIQUE. And if you keep exactly one value in a row (which is what I'd expect), then you can add a check constraint like

, CHECK (
    (boolean_setting IS NOT NULL)::integer + 
    (numeric_setting IS NOT NULL)::integer + 
    (date_setting IS NOT NULL)::integer + 
    (text_setting IS NOT NULL)::integer 
    = 1
  )

Depending on how you pass the values into this table, you may or may not need some sort of validation as well. This may involve checking setting names against the type of values provided and a list or a range of valid inputs.

StackExchange Database Administrators Q#27296, answer score: 3