Re: Attribute-values in separate table

"Authorised User" <bg_at_microsoft.com> wrote in message news:xuFVi.6848

[snip]

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

Oddly enough, there's nothing wrong with using an ER model (which usually includes attributes as well) as a conceptual model. The thing is that a conceptual model is not a design model. A single conceptual model can give rise to numerous acceptable design models, and an even larger number of unacceptable design models.

Even after analysis has been done well, design is still not trivial.

[snip]

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

Having said what I said above, I'm still glad you saved yourself from a near certain disaster.

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

For a disciplined approach to storing common facts in general tables, look up "generalization specialization relational model" on the web. Some of the sites you'll find treat the subject well.

> 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've been on both sides of this fence. In my very first Oracle project, I was brought in precisely because the outside development team and the end-user managers felt that the in house DBAs were "uncooperative". Turns out the in house DBA team was right, and refused to cooperate with a disaster in the making. They were doing the in house management team a favor.

But that's a whole discussion in and of itself. Received on Tue Oct 30 2007 - 15:21:48 CET