>> 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:[EMAIL PROTECTED] > Sent: Tuesday, April 17, 2007 3:50 PM > To: 'Fabian Köhler'; mysql@lists.mysql.com > 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:[EMAIL PROTECTED] > > Sent: Monday, April 16, 2007 7:04 PM > > To: 'mysql@lists.mysql.com' > > 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/[EMAIL PROTECTED] > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
