generic datatype table
in [Programming SQL]
|
From: John A Grandy on 11 Jul 2008 17:36 I have designed the following table that is generic in datatype for its primary data ... Id (PK, bigint, not null) SymbolId (int, not null) IntervalTypeId (int, not null) IntervalMultiplier (int, not null) BarNumber (int, null) DataDateTime (DateTime not null) ValueDatatypeId (int, not null) ValueBoolean (bit, null) ValueDecimal (decimal, null) ValueDateTime (DateTime, null) ValueInteger (bigint, null) ValueText (text, null) This allows multiple data time-series ( each potentially of different datatype) to be loaded into a single table where the datatype only becomes known at load-time ... I am wondering if including ValueDatatypeId is a good design ... In querying this table , is it more performant to use a coalesce or a case ? coaelese ( ValueBoolean, ValueDecimal, ValueDateTime, ValueInteger, ValueText ) case ValueDataTypeId when 1 then ValueBoolean when 2 then ValueDecimal when 3 then ValueDateTime when 4 then ValueInteger when 5 then ValueText end Any general comments on this table design also welcomed ...
From: --CELKO-- on 11 Jul 2008 17:56 >> Any general comments on this table design also welcomed << A newbie re-invents this thing about every 4-5 months on this newsgroup. This nightmare is so bad that it has the name EAV (Entity- Attribute-Value); Google for the painful details of why we don't do this. Also, there is no such thing as a "Value_Data_Type_Id" in a proper design. An attribute cannot be both an identifier (unique to a single entity) and a type (valued attribute, like "blood_type", etc.) Likewise, we can have <something>_value" because value is what ISO-11179 called a property. "Data" as either a root term or a property is just plain silly -- too vague. Do you also have "non-data" in your RDBMS?
From: Eric Russell on 11 Jul 2008 18:13 A case expression must return the same data type, so to avoid "Error converting data type" errors, you would end up having to wrap something like a convert(varchar(max),..) around each column. I've never personally had a need for it, and I don't know what your specific application is, but if you really think this type of generic datatype table would be benefitial, then perhaps you could leverage the [sql_variant] datatype. According to one article I found... "A sql_variant can hold any datatype except text, ntext, image, and timestamp without conversion." "It's like varchar in that it will expand or shrink as necessary and it can hold up to 8016 bytes." http://www.sqlteam.com/article/the-sql_variant-datatype Here is my own little test. declare @T table (V sql_variant) insert into @T (V) values ('A') insert into @T (V) values (getutcdate()) insert into @T (V) values (newid()) insert into @T (V) values (1) insert into @T (V) values (1.234567) insert into @T (V) values (-6282811199999.234567) select * from @T A 2008-07-11 22:03:53.593 0FAB0574-E847-42F3-B039-51654616FD5E 1 1.234567 -6282811199999.234567 "John A Grandy" wrote: > I have designed the following table that is generic in datatype for its > primary data ... > > > Id (PK, bigint, not null) > SymbolId (int, not null) > IntervalTypeId (int, not null) > IntervalMultiplier (int, not null) > BarNumber (int, null) > DataDateTime (DateTime not null) > ValueDatatypeId (int, not null) > ValueBoolean (bit, null) > ValueDecimal (decimal, null) > ValueDateTime (DateTime, null) > ValueInteger (bigint, null) > ValueText (text, null) > > > This allows multiple data time-series ( each potentially of different > datatype) to be loaded into a single table where the datatype only becomes > known at load-time ... > > I am wondering if including ValueDatatypeId is a good design ... > > In querying this table , is it more performant to use a coalesce or a case ? > > coaelese ( ValueBoolean, ValueDecimal, ValueDateTime, ValueInteger, > ValueText ) > > > case ValueDataTypeId > when 1 then ValueBoolean > when 2 then ValueDecimal > when 3 then ValueDateTime > when 4 then ValueInteger > when 5 then ValueText > end > > > > > Any general comments on this table design also welcomed ... > > >
From: John A Grandy on 11 Jul 2008 19:02 I wonder if ~~THE DATABASE GOD --CELKO-- HIS MOST WORTHY LORDSHIP~~ confers his blessing on sql_variant .... "Eric Russell" <EricRussell(a)discussions.microsoft.com> wrote in message news:7ED56D89-152E-4EAF-BAEF-D2A10DD18D8D(a)microsoft.com... >A case expression must return the same data type, so to avoid "Error > converting data type" errors, you would end up having to wrap something > like > a convert(varchar(max),..) around each column. > > I've never personally had a need for it, and I don't know what your > specific > application is, but if you really think this type of generic datatype > table > would be benefitial, then perhaps you could leverage the [sql_variant] > datatype. > > According to one article I found... > "A sql_variant can hold any datatype except text, ntext, image, and > timestamp without conversion." > "It's like varchar in that it will expand or shrink as necessary and it > can > hold up to 8016 bytes." > http://www.sqlteam.com/article/the-sql_variant-datatype > > Here is my own little test. > > declare @T table (V sql_variant) > > insert into @T (V) values ('A') > insert into @T (V) values (getutcdate()) > insert into @T (V) values (newid()) > insert into @T (V) values (1) > insert into @T (V) values (1.234567) > insert into @T (V) values (-6282811199999.234567) > > select * from @T > > A > 2008-07-11 22:03:53.593 > 0FAB0574-E847-42F3-B039-51654616FD5E > 1 > 1.234567 > -6282811199999.234567 > > > > "John A Grandy" wrote: > >> I have designed the following table that is generic in datatype for its >> primary data ... >> >> >> Id (PK, bigint, not null) >> SymbolId (int, not null) >> IntervalTypeId (int, not null) >> IntervalMultiplier (int, not null) >> BarNumber (int, null) >> DataDateTime (DateTime not null) >> ValueDatatypeId (int, not null) >> ValueBoolean (bit, null) >> ValueDecimal (decimal, null) >> ValueDateTime (DateTime, null) >> ValueInteger (bigint, null) >> ValueText (text, null) >> >> >> This allows multiple data time-series ( each potentially of different >> datatype) to be loaded into a single table where the datatype only >> becomes >> known at load-time ... >> >> I am wondering if including ValueDatatypeId is a good design ... >> >> In querying this table , is it more performant to use a coalesce or a >> case ? >> >> coaelese ( ValueBoolean, ValueDecimal, ValueDateTime, ValueInteger, >> ValueText ) >> >> >> case ValueDataTypeId >> when 1 then ValueBoolean >> when 2 then ValueDecimal >> when 3 then ValueDateTime >> when 4 then ValueInteger >> when 5 then ValueText >> end >> >> >> >> >> Any general comments on this table design also welcomed ... >> >> >>
From: John A Grandy on 11 Jul 2008 19:28 Am I right on schedule ? Because believe me I sure try to be. Aren't you going to rev up your "rows are not records, columns are not fields, raisins are not grapes, prunes are not plums, figs are not Newtonian, phat is not fat, dope is for dopes, etc." spiel ? "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:7c0ec7d3-ee25-49c2-afd5-47cbe91190ba(a)x35g2000hsb.googlegroups.com... >>> Any general comments on this table design also welcomed << > > A newbie re-invents this thing about every 4-5 months on this > newsgroup. This nightmare is so bad that it has the name EAV (Entity- > Attribute-Value); Google for the painful details of why we don't do > this. > > Also, there is no such thing as a "Value_Data_Type_Id" in a proper > design. > > An attribute cannot be both an identifier (unique to a single entity) > and a type (valued attribute, like "blood_type", etc.) Likewise, we > can have <something>_value" because value is what ISO-11179 called a > property. "Data" as either a root term or a property is just plain > silly -- too vague. Do you also have "non-data" in your RDBMS?
|
Next
|
Last
Pages: 1 2 3 4 Prev: @@Error changed by Begin? Next: clear table and reset Identity cols |