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 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.
> If I add a "National Insurance No." (Social
> security No) then either it's:
> 1. Plain text, and can take clearly invalid codes
> 2. A user-space construct with regexp matches etc (basically recreating DDL)
> 3. DDL.
DDL, yes. Run-time, no.
> And as the example says, you need to create the table types in advance.
> If you want to add e.g. "Work History" (employer, from_date, to_date) to
> a questionnaire then you'll need dynamic DDL (of form #2 or #3 above).
Again see above for new types of questions and answers. You can pay
for it once up front in a re-jigger of the schema, or you will pay a
much larger price when you discover you've got EAV goo all over the
place. There is a third option, of course, which is, "I'm sorry,
Dave. I can't do that." ;)
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