There is another question coming to my mind: is it possible to define a
view which has more columns then <put nof cols limit for storage engine
here> or does the same limits for a normal table apply to a view?
regards,
Fabian
On Mon, 16 Apr 2007 23:39:40 -0700, "Micah Stevens"
<micah@stripped> said:
> Fabian Köhler wrote:
> > Hello,
> >
> > i have table with answers to questions. Every answer is a column in the table.
> i.e.
> >
> > id|q1|q2|q3
> > 1|answer1|answer2|answer5
> > 2|answer3|answer4|asnwer6
> >
> > another option to save it would be sth like this:
> >
> > id|field|value
> > 1|q1|answer1
> > 1|q2|answer2
> > 1|q3|answer5
> > 2|q1|answer3
> > ...
> >
> > The last one is not really useable when working with large amounts of data, when
> you want to select i.e. 200 questions with answers it's 200*nof answers queries to get
> them.
> >
> >
> Really? Wow, my opinion is that you're trying to do in one table what
> you should do in two. Have a questions table, and an answers table. The
> answers table would have a column specifying which question they belong
> to. i.e.
>
> QUESTIONS
> int autoincrement questionID
> question
>
> ANSWERS
> int autoincrement answerID
> int questionID
> answer
>
> Then if you want all the answers to a question, you just grab them:
>
> select * from answers where questionID = '100'
>
> Or, you can do a join, and get the question information in the same
> query.
>
> -Micah
> > The problem with the first solution is, that MyISAM storage engine is limited to
> 2599 columns i think. So what's happening if i have more answers than columns available?
> >
> > Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option,
> they are all to slow. What's the "right" way to store and select such information?
> >
> > Thank you very much.
> >
> > regards,
> >
> > Fabian
> >
> > --
> > Fabian Köhler http://www.fabiankoehler.de
> >
> >
