Re: Recursive queries in slow database

Lars Rönnbäck wrote:

> The use of EAV is also aimed at keeping your information dense. Let's
> say that you have a database of patients and that a patient can have
> about two hundred different attributes, but on average a patient only
> has ten of these filled with information. Such sparse data would using
> many different modeling techniques yield a lot of NULL values in the
> database[...].

Not in any database designed by me. But I see your point; a fool can always design a table that conflates a very large number of fact types, not all of which obtain (or not all right away), and he can conceal the problem by allowing values to be omitted.

However there is no law of nature nor any design principle that invites or requires such a foolish design. Quite the opposite really. So even if one were sympathetic to EAV this scenario is not a justification for it. It's a justifaction for firing the fool's ass. And the ass of whoever reviewed the design and agreed it was cool.

> In the case of flexibility the EAV does not provide anything that
> cannot be done using another technique. The benefit in EAV is not that
> it is more flexible, but that the cost for implementing a change is
> low.

Hogwash. Most SQL DBMSs support CREATE TABLE and ALTER TABLE which allow you to make changes to the database at near-zero cost. Literally seconds of work.

But perhaps you mean the cost of changing the application? Having already incurred the massive cost of developing all the (usually inadequate and fragile) machinery to navigate, maintain, and implement the homebrewed meta-data/catalogues? Even if that is what you mean, you are making an assertion without proof. I will assert (without proof) that most SQL DBMSs already provide robust and well thought out machinery for managing meta-data/catalogues, at no additional cost, because that is exactly what a relational/SQL DBMS was conceived to do.

> However, I do agree that EAV should be avoided at all costs

Well thank goodness for that. The way you were talking it up there I was worried.

> so what is the solution?

> There is a modeling technique called Anchor Modeling in which you will
> never store NULL values to represent missing information. Missing
> information is instead represented by the absence of rows, and
> therefore take up no space. In AM flexibility (in the sense of low
> cost when implementing changes) is achieved through a high degree of
> normalization. I am not sure if it is right for you, but all available
> information can be found at http://www.anchormodeling.com.

So "Anchor Modeling" is what I've been doing for the last 25 years? I guess I'll have to put another bullet point on my CV.

-- 
Roy