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

Roy Hann wrote:
> "VC" <boston103_at_hotmail.com> wrote in message
> news:P6KdnREFdtrKAfXeRVn-jQ_at_comcast.com...
> > I know that, but what recipe does Roy suggest for, let's say, MS SQL
> Server
> > that does not have either UDTs or other means to implement an entity with
> a
> > higher than the table limit number of attributes, beyond offering a cute
> > saying ?
>
> I didn't think there was any burden on me to solve someone else's specific
> problem with a specific product.

No one's asking you to do that. You said the following, " the tired old
entity-attribute-value (EAV) design (much beloved of medical research wonks
for some reason)"

I gave you a reason why someone might be forced into using EAV to which you gave a smart-alecky response.

> Nor did I think my point needed
> explaining. I had imagined it was just common sense. Evidently I was
> mistaken, for which I apologize. Let me explain now:
>
> When we have a pretty good solution with many well-known virtues then it is
> not sensible to abandon it until we demonstrate that some alternative is
> actually better than all other conceivable possibilities. Otherwise we are
> just picking a random alternative with no rational basis on which to prefer
> it.
>
> For instance, is EAV better than a non-loss decomposition of a 3,000
> attribute table into six 500 attribute tables? That is clearly less elegant
> than the single big table but it at least allows us to continue using SQL
> and the power of the SQL DBMS.

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.

I was not the SQL Server EAV solution designer, but when talking to the person who reluctantly went with EAV, I could not offer a better alternative.

> These questions and more need to be tested before hoisting EAV onto our
> shoulders for a heroes welcome. (There is no burden on me to answer them
> because I am not proposing to use EAV.)

The project I am talking about went through many painful iterations with multiple tables. Their testing showed that EAV appears to be at least an acceptabe solution in their cicumstances.

There is a burden on you because you did not offer a sensible alternative after having rejected EAV in toto.

>
> Roy
Received on Wed Nov 02 2005 - 14:48:13 CET