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

"vc" <boston103@xxxxxxxxxxx> wrote in message
news:1130949100.933681.55190@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > 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.

I assume you mean "vertically" not horizontally.

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

I guess if I wanted to make the effort I might come up with the sort of
queries you are thinking about here, but it's not my argument and I can't be
bothered. You are re-asserting they were tough queries, but you already
said that.

Also, I am starting to wonder if the tables you are describing are really
EAV tables (like what the OP came up with). I wonder if your tables might
not be a perfectly legitimate design--something along the lines of

create table result
(
experiment ...,
datetime ...,
analyte ...,
concentration ...
)

> The optimizer could not build an access plan with acceptable
> performance with the multiple table approach. Joins complicated the
> problem even more.

No doubt there are very hard queries that tax an optimizer. You are
asserting this is one of those cases but I choose not to take your word for
it. Only examples will do.

> Additionally, on a daily basis, about a dozen or more drug trial
> parameters were added or dropped from consideration which would mean
> potentially modifying all the 9 tables with DDL statements. Clearly,
> it creates a severe maintenance headache.

Maybe, maybe not. Would it have been so hard to create a tool to do it? I
assume a tool was created to make sure the EAV tables were well-behaved,
that no one entered spurious rows, nor left rows out. But perhaps not?

> > An extraordinary claim always requires extraordinary proof and I think
it is
> > quite reasonable for me to remain skeptical about this until the
evidence
> > makes it perverse of me to go on denying it. I can be persuaded by
> > evidence. Can you provide full details?
>
> Unfortunately, I cannot provide more detail than above since the design
> discussion I mentioned happened about two years ago.

Pity. I remain skeptical then.

> > I am not clear if it it is your opinion that the EAV design is
beneficial in
> > general.
>
> If you'd carefully read my other contributions to the thread, you'd
> have been able to figure out that, in fact, I am strongly opposed to
> the EAV approach.

Fair point. I confess I read the contributions more carefully than the
sigs.

> The implication of your original statement was that EAV is bad in all
> the cases.

Hmm. That's pretty close to my intended meaning, yes. (This is a theory
group.) But I do of course concede that bugs and misfeatures might cripple
a specific version of a specific product so badly that a really bad approach
at least works even when the "proper" approach fails entirely. But that is
a banal observation.

> My objection was that the generalization is not fair and I
> gave an example when it made sense to represent a table (or a couple of
> table) as an EAV set.

Unfortunately you only assert that is the case. I remain skeptical for want
of substantial evidence. I can't even tell what your tables look like.
Even if I wanted to believe you (but I don't) I'd be a fool to do so on the
basis of what you've shown us so far. I can think of lots of reasons why
those folks might not have got their first (and therefore presumably
preferred) approach to work.

> You've offered first a saying

In fairness it was a saying that summed up an important point. Do you
disagree that one should only reluctantly discard a successful approach?
How eager should one be?

> and then a
> multitable approach that was not acceptable for the reasons I tried to
> describe above.

I have no doubt that you passionately believe in your position. I don't but
I can be made to. I just need proof. Hazy recollections don't do it for
me.

> > The EAV design has been widely and repeatedly criticised, has numerous
clear
> > weaknesses with respect to the established approach, and there are
plausible
> > reasons to doubt it should be intrinsically easier to code for. It is
> > contrary to the established position. There is no burden on me to
defend
> > the established position.
>
> The "established position" means nothing in a logical discourse.

That's not so. One is never required to re-establish what has already been
established.

There is an established approach to table design. It says attributes are
columns, not rows.

> Every
> situation in a database design world has to be argued on its own merits
> -- it's not a perpetuum mobile problem that has been settled once and
> for all.

That is not the implication of what I said. Of course the established
orthodoxy might be toppled. I am just saying that the burden of toppling it
is not on me if I take the orthodox position. I do.

Roy


.

Relevant Pages

  • Re: Problem with views switching columns
    ... This is called EAV design. ... Design for Generic Clinical Study Data Management Systems" by Prakash ... Similarly, most analytical programs, such as ...
    (microsoft.public.sqlserver.programming)
  • Re: Suggestions for refactoring unusual tables
    ... Throw away the idiot who did the EAV. ... it is not a design at all. ... Dining Room verbal aggression 0 ... courtyard verbal aggression 0 ...
    (comp.databases)
  • Re: Noob needs polymorphism
    ... >>Don't use an RDBMS. ... This design flaw is called EAV ...
    (comp.databases)
  • Re: Attribute-value approach for table design
    ... The intent of my post was to give a name to the design, ... the chances of EAV being a newbie error, ... Bear in mind that 'database' and 'SQL DBMS' are different concepts. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Modelling objects with variable number of properties in an RDBMS
    ... > That was tried and rejected primarily for performance reasons. ... The EAV ... An extraordinary claim always requires extraordinary proof and I think it is ... The EAV design has been widely and repeatedly criticised, ...
    (comp.databases.theory)