datatype supplied by user for SQL field

Question

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).

Answer 1

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

Answer 2

Generally when designing tables you want to know what is actually going to be in them. Do you have a distinct number of data types which you need to support? Do you need to cover decimals? One possible solution would be something like this:

CREATE TABLE dbo.My_Table (
     id           INT NOT NULL,
     data_type    VARCHAR(10) NOT NULL,
     string_value VARCHAR(100) NULL,
     int_value    INT NULL,
     date_value   DATETIME NULL,
     CONSTRAINT CK_My_Table_data_type CHECK data_type IN ('int', 'string', 'datetime'),
     CONSTRAINT PK_My_Table PRIMARY KEY CLUSTERED (id)
)
GO

You can then use the data type to decide on which column you want to select or insert/update. Keep in mind though that you might have problems with using:

SELECT
     id,
     CASE data_type
          WHEN 'string' THEN string_value
          WHEN 'int' THEN int_value
          WHEN 'datetime' THEN date_value
          ELSE NULL
     END

SQL Server requires that the data types all match for a column being returned. If you are only ever selecting one at a time it might work ok, but if you ever want to select sets of data then you'll likely need to do some casting anyway or select only rows where the data_type values are equal. I haven't done testing on all of the scenarios though, so you should play around with it to see what works and what doesn't work.

I'll just say again though, you should look at the design of your application again. It's certainly possible that you have a requirement for this kind of functionality, but often times when I see this design pattern what the front-end REALLY wants is a string that will be displayed in the application. That string just happens to look like a number or a date at times. If you're going to actually be performing data type specific functions on the data then it's likely that a less "flexible" design is called for, but without knowing all of your requirements I can't say. This is just from my experience in the past.

EDIT: Looks like I type too slowly and Tony beat me to it. :)