Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Creating a field in a table where a datatype is only known when inserting record

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2009
    Posts
    2

    Smile

    Thanks guys,

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

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.
    Code:
    select id, name, get_value( id, 'height' )
    from my_objects

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •