Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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 '12 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. –  Craig Ringer Oct 21 '12 at 10:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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