Re: Attribute-values in separate table

From: Authorised User <bg_at_microsoft.com>
Date: Tue, 30 Oct 2007 12:31:25 GMT
Message-ID: <xuFVi.6848$CN4.2025@news-server.bigpond.net.au>

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 ;)

> (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.

-- 

"For modes of faith, let graceless zealots fight", Alexander Pope, 1733

Received on Tue Oct 30 2007 - 07:31:25 CDT