Re: Modelling objects with variable number of properties in an RDBMS

<casey.kirkpatrick_at_gmail.com> wrote in message news:1130965639.482009.182400_at_g49g2000cwa.googlegroups.com...
>
> vc wrote:
>> Roy Hann wrote:
>> > "vc" <boston103_at_hotmail.com> wrote in message
>> > news:1130939293.784399.319040_at_g43g2000cwa.googlegroups.com...
>> > >
>> > > I gave you a reason why someone might be forced into using EAV to
>> > > which
>> > > you gave a smart-alecky response.
>> >
>> > I had intended it to be a mildly witty way of expressing an important
>> > point.
>> > I didn't realize it might seem to lack proper gravity.
>> >
>> > > That was tried and rejected primarily for performance reasons. The
>> > > EAV
>> > > approach turned out to be faster and various SQL queries turned out
>> > > to
>> > > be simpler in comparison to trying to combine results from multiple
>> > > tables representing a single entity.
>> >
>> > We weren't discussing performance, but I'll let that pass.
>> >
>> > I find both claims quite astonishing. That is entirely contrary to
>> > what I
>> > would expect. Evidently it was contrary to what they expected too
>> > since you
>> > say they tried one of my suggestions. Since it seems we agree that
>> > wasn't
>> > the expected outcome that makes it an extraordinary claim.
>>
>> OK, a SQL Server row can be 8K long maximum. Say, a drug formula is a
>> relation like (some_stable_info_about 2K long,
>> ~5000-6000_lab_data_results). The SQL Server row can be 8K long
>> maximum which leaves room for 750 double precision columns for storing
>> trial results. In a multi-table, approach it would amount to 8 tables
>> partitioned horizontally, by attribute subsets. A simple request like
>> select formulas for a given date with drug trial parameters in some
>> given ranges can be expressed as a single SQL statement with an EAV
>> table and would require 8 unions, or thereabout, with multiple tables.
>> The optimizer could not build an access plan with acceptable
>> performance with the multiple table approach. Joins complicated the
>> problem even more.
>>
>
> I hope this is not a side-track here, but could you clarify this a bit?
> It sounds like you are saying you have a drug entity, some stable
> information about that drug, and then the results of multiple
> tests/experiments done against that drug (e.g. test1, test2, test3,
> test4,..., test750).
>
> Should these tests really be considered attributes? Wouldn't the act
> of presenting them as a two-tuple relation <test#, test result> simple
> be an wise step of normalization - not an example of an EAV
> decomposition? Am I totally missing the boat on this?

It's a single test with ~6000 measurements.
>
> Thanks,
>
Received on Wed Nov 02 2005 - 23:23:21 CET