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

1 Answer 1


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

    (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.

  • yup, i guess thats the only way. I think i ll go with one string field tho, because postgresqls boolean type doesn't cast to php boolean automatically so i have to cast by hand anyway. Thanks
    – U2ros
    Oct 20, 2012 at 14:12
  • If you do want to keep historical data, add a partial unique index like CREATE UNIQUE INDEX tablename_unique_current_settings ON tablename(setting_name) WHERE (is_current). Then ensure your app always queries with is_current. Personally I'd use a separate history table in preference to this, but a partial unique index is WAY better than not having any enforcement of current setting uniqueness. Oct 21, 2012 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.