Re: Attribute-values in separate table

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 30 Oct 2007 10:40:44 -0300
Message-ID: <4727345d$0$14835$9a566e8b@news.aliant.net>

Authorised User wrote:

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

>>"Brian Selzer" <brian_at_selzer-software.com> wrote in message
>>news:yoBUi.7699$Pv2.3001_at_newssvr23.news.prodigy.net...
>>
>>>"Authorised User" <bg_at_microsoft.com> wrote in message
>>>news:6t0Ui.4613$CN4.2731_at_news-server.bigpond.net.au...
>>
>>[snip]
>>
>>>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
>>
>><snip>
>>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
>>
>><snip>
>>
>>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.)
>>
>>Roy

>
> 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 - 08:40:44 CDT