Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!cycny01.gnilink.net!cyclone1.gnilink.net!gnilink.net!nx02.iad01.newshosting.com!newshosting.com!post01.iad01!news.aliant.net!not-for-mail Date: Tue, 30 Oct 2007 10:40:44 -0300 From: Bob Badour User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.theory Subject: Re: Attribute-values in separate table References: <1188482048.713274.53040@k79g2000hse.googlegroups.com> <46d6ce09$0$4034$9a566e8b@news.aliant.net> <1188551701.232978.17290@r23g2000prd.googlegroups.com> <6t0Ui.4613$CN4.2731@news-server.bigpond.net.au> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable Lines: 105 Message-ID: <4727345d$0$14835$9a566e8b@news.aliant.net> NNTP-Posting-Host: 142.176.58.53 X-Complaints-To: abuse@aliant.net Xref: usenetserver.com comp.databases.theory:167058 X-Received-Date: Tue, 30 Oct 2007 08:40:47 EST (text.usenetserver.com) Authorised User wrote: > On Sat, 27 Oct 2007 15:44:02 +0100, Roy Hann wrote: >=20 >>"Brian Selzer" wrote in message >>news:yoBUi.7699$Pv2.3001@newssvr23.news.prodigy.net... >> >>>"Authorised User" wrote in message >>>news:6t0Ui.4613$CN4.2731@news-server.bigpond.net.au... >> >>[snip] >> >>>It is a truly poor programmer indeed who would trade off data integrit= y >>>for a faulty expectation of reduced work. If there are eighty types o= f >> >> >>Unfortunately, given the piss-poor tools that programmers are content t= o >>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. >=20 > 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 an= d a > fraction of the development effort is available for maintenance. Dynam= ic > SQL requires lateral thinking and is obviously superior when you consid= er > all the issues. >=20 > 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=20 the widespread ignorance in the industry, I am not sure what relevance=20 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 ou= t >>that at least one pretty good solution exists and one possible >>explanation why no one uses it.) >> >>Roy >=20 > I have spent some time looking at both EAV and Dynamic SQL in detail si= nce > hijacking this discussion. I agree with you that Dynamic SQL is > preferable. I'd just like to say thank you =E2=80=93 you've been a grea= t help. >=20 > 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." >=20 > 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 fr= om > 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. >=20 > I may end up with one general table or common facts and many sub-tables= =2E I > am still looking the possibility of schema-based XML (i.e. with > constraints and indexes). >=20 > Sadly, after ten years in Oracle, it has been consistently my experienc= e > (from 4 sites, in development teams of between 3 and 9 people) that the= > DBAs vary between hostile and uncommunicative towards developers. I wo= n't > clutter the thread with examples. Cheers. I have known both surly DBAs and surly programmers. I have known both=20 competent DBAs and competent programmers. I have known plenty of each=20 who were both, neither or one or the other.