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_at_news.aliant.net>
>
> 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 ;)
>
> 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.
Date: Tue, 30 Oct 2007 10:40:44 -0300
Message-ID: <4727345d$0$14835$9a566e8b_at_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 - 14:40:44 CET