Re: Database naming convention (yet another post of it, but a bit different)
Date: Thu, 10 Oct 2002 09:23:25 GMT
Message-ID: <hGbp9.1610$MV.72338_at_newsc.telia.net>
Stu,
We've recently been through that change. We had one table with one row where we had one column per config option for the application. We changed this into a two column table as you described, because we got fed up doing schema changes each time a new config option was introduced (this was a particular pain as the schema is created by a setup program which also has to handle upgrades, so he have to maintain that code for each schema change).
In MS SQL Server 2000, there's a sql_variant datatype. For this, an instance can take the shape of almost any of the DBMS datatypes. There are also system functions you can use to ask the DBMS which datatype is stored in an instance of the sql_variant datatype. I guess that this datatype can be *really* misused, but this is a situation where it can be handy. As our code has to run down to SQL Server 6.5, we opted to store all as strings. Then we have a COM object which encapsulates this and exposes proper datatypes to outside world.
One disadvantage for this layout is that creating constraints becomes a bit more involved. You essentially have to check for value in the Setting column along with allowed values in the Value column. We don't have CHECK constraint today for this table (sw isn't released yet, we might do it), but if/when we do we will have to encounter that an "int" is stored in a varchar(100) column.
-- Tibor Karaszi "stu" <smcgouga_at_nospam.co.uk> wrote in message news:ao3ep1$4rd$2$8302bc10_at_news.demon.co.uk...Received on Thu Oct 10 2002 - 11:23:25 CEST
> >Especially when it comes to cases where
> > we have a table with one row.
>
> Slightly OT but...
> Are 1 row tables a good idea for storing settings? I usually set up the
> following table(s) to store settings in a database:
>
> Setting(pk) Value
> pi 3.14159265
> maxReportSize 90
> case 12
> etc....
>
> then another (sometimes I store numeric and text values in the same
column)
> for text settings:
>
> What are the advantages / disadvantages with this structure compared to a
> one row table for storing settings. I realise my design is not normalised
> (at all!) but it seems to work well.
>
> Cheers
> Stu
>
>
>
>