On Feb 15, 2008, at 6:29 AM, Balázs Klein wrote:
> Erik Jones wrote:
>> First, please stop top-posting. It makes it difficult for both me
>> and others to know to whom/what you are replying.
>
> Sorry, I don't know much about mailing list customs - I had to look
> up what top-posting is. I will behave now ...
It's cool, now you know :)
> I would prefer to keep the complications for when I retrieve the
> data rather then when I store it.
Really? When do you think users notice performance hits the most?
I'd think, given that answers for a questionnaire are stored as a
batch, people running reports on will be the ones to notice, i.e. at
retrieval time.
>
> I could imagine something like this though to create a crosstab as
> an array, but I am afraid that there is no assurance that the
> resulting array would contain the values in the same order for each
> focus:
>
> tbl(eID, aID, value)
>
> Select eID, array_accum(value) from
> (
> (Select Distinct eID from tbl) e
> CROSS JOIN
> (Select Distinct aID from tbl) a
> ) ea
> LEFT OUTER JOIN
> tbl USING (eID, aID)
> GROUP BY eID
That's cool. I still don't see why you're so set on an EAV, but it's
your setup. Watch out, though, big questionnaires will turn into
queries with an inordinate amount of joins and performance on those
will suck. If you just used arrays directly you could pull all of
the answers for a given person and/or questionnaire with pretty
simple query.
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com