Looking for table design pattern for different value types

Hi,

I need to store a list of parameters in a database. Each parameter has a name, description, comment and a value. Easy so far.

However the values are of different types. Each individual parameter has a value which may be of type int, decimal, string, boolean, custom type etc.

Which table design pattern is most appropriate?
We have a heated in-house discussion and I need supporting arguments.

Options explored so far:

1) (De-)serializing the value to a string-type.
2) Adding a column for each type, using only one column at a time.
3) Adding extra value-tables, one table for each type.
The disadvantages for each option are obvious and the basis for our discussion.

Your help in this matter will be appreciated.
Regards, Tonn

Tonn:

Can you take advantage of the SQL_VARIANT data type? Something like:

drop table dbo.parameter
go

create table dbo.parameter
( parmName varchar (40),
parmDescription varchar (80),
parmType tinyint,
parmValue sql_variant
)
go

insert into parameter values ('Integer Parm', 'Just an integer parameter', 1, 1)
insert into parameter values ('Numeric (9,2) parm', 'Yeah', 2, cast (17.50 as numeric (9,2)))
insert into parameter values ('Varchar parm', 'A varchar parm', 3, 'Yes, a varchar')
select * from parameter

-- Sample Output:

-- Warning: The table 'parameter' has been created but its maximum row size (8164) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

-- parmName parmDescription parmType parmValue
-- -- -- --
-- Integer Parm Just an integer parameter 1 1
-- Numeric (9,2) parm Yeah 2 17.50
-- Varchar parm A varchar parm 3 Yes, a varchar

|||

Hi Waldrop,

Thanks for your reply.
It most certainly is an interesting suggestion. It's like option no.1 except that the serialization has been moved to the database.

Is this the preferred way of handling this type of problem? We're in the financial market and this is a common problem for us, but there seems to be no documentation, best practices etc. available so our individual programmers tend to insist on their personal preference.

|||

I think that your purpose is the type of application that the SQL_VARIANT datatype is intended to address. It would be helpful if you could provide some sample update or select statements where you might use your proposed column. I don't think I would want to have a column for each different potential datatype. Below is an example of how you might load the data into variables. Note the need to CAST the data:

declare @.numericVar numeric (9,2)
declare @.varcharVar varchar (80)
declare @.intVar integer

set @.numericVar = (select cast (parmValue as numeric (9,2)) from parameter where parmName = 'Numeric (9,2) parm')
set @.varcharVar = (select cast (parmValue as varchar (80)) from parameter where parmName = 'Varchar parm')
set @.intVar = (select cast (parmValue as integer) from parameter where parmName = 'Integer Parm')

select @.numericVar as [@.numericVar],
@.varcharVar as [@.varcharVar],
@.intVar as [@.intVar]

-- S A M P L E O U T P U T :

-- @.numericVar @.varcharVar @.intVar
-- -- --
-- 17.50 Yes, a varchar 1

Dave

|||

Hi Dave,
I think you've already made your point and put your suggestion at the top of the list.

It's no use sending an example since every proposed alternative so far works. I'm actually looking for some form of authority to stop spending all those man-hours debating about it.
In the past I've had success regarding programming issues by referencing Gamma's "Design Patterns". I was hoping to do the same for database issues, but I found out that it's not going to be that easy.