Creating a field in a table where a datatype is only known when inserting record
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).
We have also created a ParameterValue table which stores the value of these parameters. ie:
-ParameterID (Foreign key with Parameters.ParameterID)
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?
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
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.
select id, name, get_value( id, 'height' )