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:-
a third row may be:-
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).
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