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

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?

Thanks, Received on Wed Nov 02 2005 - 22:07:19 CET