Hi,

I am in the process of creating a medical database (Using SQL-Server 2005) which records information about blood samples that arrive for testing at our centre. At times we have to store additional information about the samples that arrive and the patients that these samples were taken from.

These 'parameters' cannot be predicted at design time. Therefore we have created a 'Parameters' table which stores the names of these parameters. Examples of these parameters could be therapy start date (date), viral load (int), height, weight (float).

---Parameters
-ParameterID (int)
-ParameterName (varchar)
-ParameterDataType (varchar)

We have also created a ParameterValue table which stores the value of these parameters. ie:

---ParameterValue
-ParameterValueID
-ParameterID (Foreign key with Parameters.ParameterID)
-Value (varied)

The problem comes from not knowing how to store dates, ints, varchars and floats in the Value field in ParameterValue. Is there any better solution than having a different field to store each of the datatype possibilities?

Thanks

do a search for entity-attribute-value, better known as EAV

this type of design is invariably fraught with whoopsies, complexity, and inefficiency

i don't envy your situation

Other possibilities include an XML column.

I believe medical databases are about the most common example of this type of requirement (never worked on one myself, mind) so that might prove fruitful googling fodder too.

pootle flump ur codings are working excelent.

Thanks guys,

we are using an EAV structure in combination with the sql_variant datatype to store values of different types.

I haven't worked on medical databases but I have worked on systems similar to what your building. You can simply use a varchar to hold the datatypes you describe (dates, ints, varchars and floats) but I believe you have to convert them explicitly in MS SQL. It's possible you might run into issues where floats converted to varchars can loose some precision but I don't think it will affect you here.

One good idea is to use a procedure to add values to the database - this sproc should check that the "object" exists, that the parameter type exists and that the format of the data is consistent with this data type. You could also add ranges etc so height might have to be within a known min and maximum height. It's also a good idea to have a function to return a given value for an object as this makes the coding far easier.

Code:

select id, name, get_value( id, 'height' )
from my_objects