List:General Discussion« Previous MessageNext Message »
From:Fabian Köhler Date:April 17 2007 2:09pm
Subject:RE: Max columns in a tabel in MyISAM storage engine
View as plain text  
>> The "right" way to do this would be your second way, where there would be
>> one row per answer. I can't really grasp how the questions and answers
>> relate to anything else, so I'm making a lot of assumptions.
>> 
>> If you are concerned that this technique is too slow, because it involves
>> linking two tables, there is an ugly alternative. You could store all of
>> your answers in a single text field, with some kind of delimited format:
>> 
>> "q1=answer1,q2=answer2,q5=answer5"
>> 
>> And then parse it in your program.
>
>And eliminates the possibility to do any queries against your database
>that make any sense whatsoever.

i see, so this is the way to go:

>QUESTIONS
>--
>QuestionID
>QuestionText
>
>POSSIBLE_ANSWERS
>--
>QuestionID
>AnswerID (A/B/C?)
>AnswerText
>
>ANSWERS
>--
>QuestionID
>AnswerID
>UserID

but there is still one problem. not every answer got an ID, one can also enter free text
as an answer, i'd need to seperate that out in another table again but it's possible of
course. I will do some performance testing on this for large data.

Regarding my other replies: Because we need to do joins to get the data from that tables
above i thought about creating a view to transform the table above into the original idea
i had:

id|q1|q2
1|a1|a2
2|a3|a4

is this possible with reasonable performance? i only got very slow implementations. also
see this link:
http://www.wormus.com/aaron/stories/2007/03/23/mysql-restructuring-data-for-a-view.html i
just thought so, because it would be very handy to read and update data that way (altough
the updates on that view doesn't seem to be possible at all).

Fabian

--
Fabian Köhler      http://www.fabiankoehler.de 

> -----Original Message-----
> From: Jerry Schwartz [mailto:jschwartz@stripped] 
> Sent: Tuesday, April 17, 2007 3:50 PM
> To: 'Fabian Köhler'; mysql@stripped
> Subject: RE: Max columns in a tabel in MyISAM storage engine
> 
> The "right" way to do this would be your second way, where 
> there would be
> one row per answer. I can't really grasp how the questions and answers
> relate to anything else, so I'm making a lot of assumptions.
> 
> If you are concerned that this technique is too slow, because 
> it involves
> linking two tables, there is an ugly alternative. You could 
> store all of
> your answers in a single text field, with some kind of 
> delimited format:
> 
> "q1=answer1,q2=answer2,q5=answer5"
> 
> And then parse it in your program.
> 
> Again, I'm not sure where you are going with this.
> 
> Regards,
> 
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
> 
> 860.674.8796 / FAX: 860.674.8341
> 
> 
> > -----Original Message-----
> > From: Fabian Köhler [mailto:fab@stripped]
> > Sent: Monday, April 16, 2007 7:04 PM
> > To: 'mysql@stripped'
> > Subject: Max columns in a tabel in MyISAM storage engine
> >
> > 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.
> >
> > 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
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=1
> >
> >
> 
> 
> 
> 
Thread
Max columns in a tabel in MyISAM storage engineFabian Köhler17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineViSolve DB Team17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineMicah Stevens17 Apr
    • Re: Max columns in a tabel in MyISAM storage engineFabian Köhler17 Apr
      • Re: Max columns in a tabel in MyISAM storage engineMicah Stevens17 Apr
    • Re: Max columns in a tabel in MyISAM storage engineFabian Köhler17 Apr
    • Re: Max columns in a tabel in MyISAM storage engineFabian Köhler17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineStijn Verholen17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineMartijn Tonies17 Apr
  • RE: Max columns in a tabel in MyISAM storage engineJerry Schwartz17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineMartijn Tonies17 Apr
    • RE: Max columns in a tabel in MyISAM storage engineJerry Schwartz17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineMartijn Tonies17 Apr
    • RE: Max columns in a tabel in MyISAM storage engineJerry Schwartz17 Apr
RE: Max columns in a tabel in MyISAM storage engineFabian Köhler17 Apr
  • RE: Max columns in a tabel in MyISAM storage engineJerry Schwartz17 Apr