Re: Table design for application options

Bugblatter wrote:

> If I have an application that has a handful of configuration options,
> what is a good way to store these options in a table?
>
> e.g.
> feature_x_enabled boolean
> default_currency_symbol character
> default_currency_name string
>
> and so on
>
> The obvious way, to me, is a table with one row with a column for each
> option. However this table would need to be redefined every time an
> option is added - making deployment of updates more complex. It also
> seems a bit strange to design a table that will only ever hold one row.
>
> I'm not sure whether it might be better to have a table with two
> columns: option_name and option_value. The latter could be a string
> value and would then have to be converted to/from other datatypes as
> appropriate. I'm not sure Codd would approve :-)
>
> What type of table structure do other apps use for this type of info?

Keep in mind security concerns. If you have multiple tables, say one per module, you can assign different security to the different tables. This works whichever method you use.

Another thought is that database structure updates should not be a problem, they should be expected with all updates and should be very easy, or your tools may be inadequate.

Now for your actual question :) I have tried both ways and have found only one small argument in favor of the one table with many columns. It turned out to be simpler to have a row composed of columns of appropriate types and sizes. The other approach requires filtering (where parmname=...) and type casting.

Consider the difference between:

SELECT CAST(parmname as int) FROM Universal WHERE parmname='blah'

vs:

SELECT blah FROM Universal

-- 
Kenneth Downs
<?php echo $sigblock ?>