Re: My honours project - databases using dynamically attached entity-properties - Mailing list pgsql-hackers

On Tue, Mar 13, 2007 at 05:54:34PM +0000, Richard Huxton wrote:
> David Fetter wrote:
> >On Tue, Mar 13, 2007 at 02:21:37PM +0000, Richard Huxton wrote:
> >>David Fetter wrote:
> >>>On Tue, Mar 13, 2007 at 09:31:45AM +0000, Richard Huxton wrote:
> >>>>* Another good example is the "questionnaire".
> >>>With all due respect, this is a solved problem *without EAV or
> >>>run-time DDL*.  The URL below has one excellent approach to this.
> >>>
> >>><http://www.varlena.com/GeneralBits/110.php>
> >>Which broadly speaking was the solution I used for my
> >>questionnaire, except I had a restricted set of types so basically
> >>just coerced them to text and side-stepped the inheritance issue.
> >>To the extent that it's dynamic, it's still just EAV though.
> >
> >That's precisely the difference between the above solution and
> >yours, and it's the difference between a good design and one that
> >will come up and bit you on the as^Hnkle.
> 
> It's still basically EAV (either approach).  The key fault with EAV
> is that the tables have no semantic meaning - answer_int contains
> number of oranges, days since birth and the price of a tube ticket
> in pennies.

Stuffing all of those into an answer_int is *precisely* what the end
user must not do.  That's pilot error.

> Now, with a questionnaire that might not matter because everything
> is an "answer" and you're not necessarily going to do much more than
> count/aggregate it.

See above.

> >>It doesn't remove the need for run-time DDL if you allow users to
> >>add their own questions.
> >
> >Sure it does.   When a user, who should be talking with you, wants
> >to ask a new kind of question, that's the start of a discussion
> >about what new kind(s) of questions would be generally applicable
> >in the questionnaire schema.  Then, when you come to an agreement,
> >you roll it into the new schema, and the whole system gets an
> >improvement.
> 
> Fine, but if you're not letting the user extend the system, then
> it's not really addressing Edward's original posting, is it?

It's my contention that Edward's original idea is ill-posed.  SQL is
just fine for doing this kind of thing, and it's *not that hard*.

> If the user's talking to me, I might as well just write the DDL
> myself - it's the talk that'll take the time, not writing a dozen
> lines of SQL.

It's the talk that's the important part.  Machines are really bad at
seeing the broader picture.  In the attempt to "save" a few minutes'
discussion, he's trying to borrow that time from a system asked to do
things that computers are inherently bad at doing, and every end user
will pay that time back at a very high rate of interest.  This is
precisely the kind of false economy that so plagues software
development and maintenance these days.

> The interesting part of the problem (from a Comp-Sci point of view)
> is precisely in automating part of that discussion.  It's providing
> an abstraction so that you don't end up with a mass of attributes
> while still providing freedom to the user.

This freedom and efficiency you're talking about is better supplied,
IMHO, by putting a standard DDL for questionnaires up on a pgfoundry
or an SF.net.  That way, improvements to the DDL get spread all over
the world, and a very large amount of wheel reinvention gets avoided.
Reusable components are a big chunk of both freedom and efficiency. :)

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate