Re: My honours project - databases using dynamically attached entity-properties

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

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

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.

--   Richard Huxton  Archonet Ltd