locked
Store different data types dynamically to one table. RRS feed

  • Question

  • Is there a best practice to store data of different datatypes into the same table ?

    My application handles workflows defined by users, and I need to store the data for the workflow into different datatypes.

    I'll give a try to explain my current layout as far as possible.

    When a workflow is executed as a html-form I need save the data to my table:

    WorkflowSaveData
      WorkflowsaveDataId
      WorkflowFieldInWorkFlowId
      IntData
      StringData
      DecimalData
      DateData

    When I save my data I execute the following stored procedure:
     DECLARE @Sql nvarchar(2000)
     SET @Sql = 'INSERT INTO WorkFlowSaveData(WorkFlowFieldInWorkFlowId, ' + @ColumnName + ')
        VALUES (' + cast(@WffInWfId as varchar(12)) + ',' + cast(@SaveValue as nvarchar(1024)) + ')'

     Print @SQL
        -- Execute query
     EXEC(@Sql)

    The variable @ColumnName comes from the definition of different WorkFlowFiledTypes (WorkFlowFieldTypeId, WorkFlowFieldDescription, ColumnName)

    The idea behind is to keep conversions to a minimum, (I really would like to have sql-server make the decision based on the columntype instead of converting to a nvarchar() in dynamic sql), and also to make it possible to add more datatypes in the future with as little impact on my application and sql code as possible.
    In the present scenario I can add a different workflowfieldtype through my gui and add a new column to my savedata table.

    Feedback of all kinds is appreciated.
     I really havent found anything that makes me convinced that this is the best way of doing it, but on the other hand I haven't found any other techniques to achieve the same thing that feels any better either.

    Regards
    P-H

    Friday, January 15, 2010 2:29 PM

Answers

  • Hi P-H,

    The design you are working with is commonly known as the "E-A-V model", and there are numerous problems associated with it. I suggest you do a quick internet search using that term in your favorite search engine first.

    Then, if you still want to (or think you have to) implement this model, read up on the SQL Server data type "sql_variant". It has some issues of its own, but it might be just what you need.
    -- Hugo Kornelis, SQL Server MVP
    • Proposed as answer by Eric WisdahlEditor Tuesday, January 19, 2010 5:55 PM
    • Marked as answer by P-H Wednesday, January 20, 2010 3:58 PM
    Monday, January 18, 2010 4:06 PM

All replies

  • Is data of different type, the same data? Like instead of "one" a 1. Or are they different attributes, just not all are filled in each time?
    Friday, January 15, 2010 4:45 PM
    Answerer
  • Data is of different types, and stored the same time.

    I'll try to give a better image of how the actual application is supposed to act, and how it will work.

    A systemadministrator creates a couple of workflows, for example invoicing, deliverance etc. where each workflow contains a few different fields. Each field is defined with a few properties like the datatype (int, decimal, string etc.)
    When employees execute the invoicing workflow they are presented with a html form, when the data is saved the application checks the datatype of each field and stores it to the workflowsavedata table. The stored procedure above is executed and the data saved in the column with the proper type.

    So each field in the form actually has its own post in the table, but just one of the columns intdata, stringdata, datedata is filled with data.

    My idea is that I save the data with the proper type in the database and making searching and reporting easier in my next step.

    Friday, January 15, 2010 5:42 PM
  • Hi P-H,

    The design you are working with is commonly known as the "E-A-V model", and there are numerous problems associated with it. I suggest you do a quick internet search using that term in your favorite search engine first.

    Then, if you still want to (or think you have to) implement this model, read up on the SQL Server data type "sql_variant". It has some issues of its own, but it might be just what you need.
    -- Hugo Kornelis, SQL Server MVP
    • Proposed as answer by Eric WisdahlEditor Tuesday, January 19, 2010 5:55 PM
    • Marked as answer by P-H Wednesday, January 20, 2010 3:58 PM
    Monday, January 18, 2010 4:06 PM
  • Thanks for the reply Mr Kornelis,

    I have browsed the web and read up on the EAV-Model and this does seem to be the proper model for solving this problem.

    Considering the use of sql_variant I have reconsidered and decided to send the value to my procedure with the proper datatype, since it is limited to four datatypes at the moment and I can see no need for additional datatypes though the applications lifecycle. The decision is also based on the desire to avoid conversion problems through the layers.

    Thanks again for pointing me in the right direction.

    Wednesday, January 20, 2010 3:58 PM