User supplied data type for SQL field
I have a SQL table that has multiple fields
ID | Value | A type
A typical entry could be: - 1000,10, [INT]
the second line could be: -
1001, Foo, [string]
the third line could be: -
1002.10 / 12/2008, [DateTime]
I was asked to look at this as at the moment, every time we want to select from this table we have to specify a value to the specified type. I can do a database redesign on this and I wonder what is the best way to go for optimization. (SQL 2000).
source to share
Horror stories! This is a scary Entity-Attribute-Value (EAV) model ! Run away!
But seriously, assuming there is some reason for such a model, perhaps 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
source to share
Usually when designing tables, you want to know what will actually be in them. Do you have a specific number of data types that 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
Then you can use the datatype to determine which column you want to select or insert / update in. Be aware that you may have problems 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 all data types to be the same for the returned column. If you only ever select one at a time, it might work fine, but if you ever want to select datasets, you probably need to do some casting or only select rows where the data_type values are equal. However, I haven't tested every scenario, so you have to play around with it to see what works and what doesn't.
I'll just say it again, but you should look at your application design again. Of course, it's possible that you have a requirement for this kind of functionality, but often when I see this design pattern, what the REALLY front end wants is the string that will be displayed in the application. This string just sometimes looks like a number or a date. If you are going to actually perform certain data type functions on the data, then most likely a less flexible design is required, but without knowing all your requirements, I cannot tell. This is just from my past experience.
EDIT: Looks like I am typing too slowly and Tony has beaten me. :)
source to share