Search code examples
sqlsql-servert-sqlentity-attribute-value

datatype supplied by user for SQL field

I have a SQL table which has a number of fields

ID | Value | Type

A typical record may be :- 1000,10,[int]

a second row may be:-

1001,foo,[string]

a third row may be:-

1002,10/12/2008,[DateTime]

I have been asked to look at this as at the moment, each time we wish to select from this table we have to cast the value to the type specified. I am able to do a database redesign on this and am wondering the best route to go to optimise this. (SQL 2000).

Solution

  • Horrors! This is the dreaded Entity-Attribute-Value (EAV) model! Run away!

    But seriously, assuming there is some reason for needing this kind of model, maybe create a properly typed column for each data type?

    ID       Type      StringValue       DateValue      NumberValue
1001     String    Foo
1002     Date                        10/12/2008
1003     Number                                     123.46