Re: Recursive queries in slow database

Dr. Coffee wrote:

[snip]
>> Google for, and read about the Entity-Attribute-Value (EAV) Model, which
>> is what your description of their approach sounds like.
>
> The Wikipedia EAV page
>
> http://en.wikipedia.org/wiki/Entity-attribute-value_model
>
> certainly describes my situation with some accuracy.
>
>> You will find
>> ample discussion of why it is bad--not the least reason being that its
>> practitioners are reimplementing the very thing an SQL DBMS is designed
>> to do.
>
> ...which happens to be one of my main reasons for complaint
> wrt the product in question...
>
>> It is invariably done only partially too, leaving out almost
>> all of the important stuff a DBMS does, like providing data integrity
>> checks, transaction isolation, etc.  
>
> You wouldn't happen to know of some key words / phrases / acronyms
> to search for, that would guid me to alternatives to EAVs...?

In the overwhelming majority of cases I've encountered, the alternative is simply to use the DBMS the way it was designed and intented to be used. You do not need to look any further, nor innovate in any way.

When people start getting enthusiastic about EAV you usually start hearing about how the database is "static" and "inflexible". Well BS on that. There is nothing simpler or quicker than creating a new table in an SQL database. Once the analysis is done, it takes seconds. Literally seconds.

The inflexibility is in the application programmers. They won't get off their backsides to learn how to access the meta data in the system catalogues--which is *always* there. They are perfectly willing to write dynamic applications that use metadata at run-time, but they insist it has to be their own metadata from their own zany homegrown catalogues. It seems to be some kind of mad "not invented here" kind of mentality.

There was some very slight reason to be wary of dynamic programming back when we were using Cobol and C because dynamic SQL (which is a legitimate part of the ANSI/ISO SQL standard) required a small amount of understanding and was never (as far as I know) covered in any introductory SQL books (e.g. SQL for Feral Idiots). But today, when everyone seems happy enough to grovel down to the level of API programming it's all basically dynamic anyway, so all you need to do is encourage them to get familiar with the SQL catalogues.

-- 
Roy