Re: Attribute-values in separate table

From: Bob Badour <>
Date: Tue, 30 Oct 2007 10:40:44 -0300
Message-ID: <4727345d$0$14835$>

Authorised User wrote:

> On Sat, 27 Oct 2007 15:44:02 +0100, Roy Hann wrote:

>>"Brian Selzer" <> wrote in message
>>>"Authorised User" <> wrote in message
>>>It is a truly poor programmer indeed who would trade off data integrity
>>>for a faulty expectation of reduced work.  If there are eighty types of
>>Unfortunately, given the piss-poor tools that programmers are content to
>>use--for a whole lot of reasons that I could rage about at length some
>>So, if programmers are content to re-invent the wheel, and if they
>>regard dynamic SQL as being too esoteric (or worse, they simply don't
>>know about it and don't know the DBMS already provides more and better
>>meta-data than they can devise--plus the logic to support it), then to
>>them it could well look like EAV is less work.  They are imagining they
>>have to code 200 distinct services by hand to support 200 distinct
>>tables.  To them, the math shows EAV is cheaper.

> Yep - hit the nail on the head there. I don't think developers keep on
> re-inventing EAV because they think they're being so clever as such --
> it's (unfortunately) the most obvious way to solve the problem of
> numerous, sparsely populated tables coupled with changing requirements
> that need to be maintained after the development cycle has completed and a
> fraction of the development effort is available for maintenance. Dynamic
> SQL requires lateral thinking and is obviously superior when you consider
> all the issues.
> Funnily enough I first came across the EAV model in '98, when I was
> maintaining a GBP 3 M project that fell out of the rear-end of Oracle
> Designer. I belive a crack-team of DBAs were responsible ;)

Which just goes to show there are no shortage of DBAs on crack. Given the widespread ignorance in the industry, I am not sure what relevance it is whether someone is a DBA versus a programmer.

>>(Note that I've written about dynamic SQL here.  There are other tools
>>available and one could imagine even better ones bing invented.   I am
>>not suggesting dynamic SQL is the only solution.  I am just pointing out
>>that at least one pretty good solution exists and one possible
>>explanation why no one uses it.)

> I have spent some time looking at both EAV and Dynamic SQL in detail since
> hijacking this discussion. I agree with you that Dynamic SQL is
> preferable. I'd just like to say thank you – you've been a great help.
> Oracle's "Ask Tom" summed it up best for me: "Oracle implemented this
> thing called SQL to define OBJECTS and ATTRIBUTES and lets you use SQL to
> query them. You are trying to put a generic layer on top of a generic
> layer and it fails each and every time except for the most trivial of
> applications."
> Luckily for me, although I was erring towards EAV in this design... I'm
> glad I came to this forum before it was too late for me to pull back from
> the brink. There's no point in throwing away the PL/SQL written so far
> because it covers a different area of the schema. I still have a bad
> feeling about re-working a section of the database design after having
> started coding.
> I may end up with one general table or common facts and many sub-tables. I
> am still looking the possibility of schema-based XML (i.e. with
> constraints and indexes).
> Sadly, after ten years in Oracle, it has been consistently my experience
> (from 4 sites, in development teams of between 3 and 9 people) that the
> DBAs vary between hostile and uncommunicative towards developers. I won't
> clutter the thread with examples. Cheers.

I have known both surly DBAs and surly programmers. I have known both competent DBAs and competent programmers. I have known plenty of each who were both, neither or one or the other. Received on Tue Oct 30 2007 - 14:40:44 CET

Original text of this message