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