Hi, >> 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 > >is, that >1) i must define the column with the value of the answer as a TEXT to >cover all possible answers, altough a SMALLINT might fit it better. but >i can live with that
? why a text? Think: QUESTIONS -- QuestionID QuestionText POSSIBLE_ANSWERS -- QuestionID AnswerID (A/B/C?) AnswerText ANSWERS -- QuestionID AnswerID UserID >2) i think it's slow when i have lots of data. i'm currently not at home >to do performance tests, but let's assume i have 2000 questions, then i >collect by user input 2000 answers to every question by 10000 different >users (a realistic scenario for our application) and store it in a table >like this: > >i then have 2000*10000 = 20 mill. rows in ANSWERS. now let's assume i >want to select all answers to all questions for 100 random users at >once. The only solution i see here for one query is a large join which >will take really, really long to get the data even with proper indexes >or did i do sth. wrong? Getting the answered questions from 100 users should be fast if you use an index on UserID in ANSWERS. >is there a way to provide a view on these two tables (QUESTIONS and >ANSWERS) that simulate the original table i intended, which is actually >fast even with large amounts of data? > >Are other dbs like oracle better on doing joins on such large tables? > >So i really like the idea of having one large table with many columns >because it's really fast and i can define each column properly but i >currently see no option to go this way but with that way there will >alway be a limit to the max. amount of answers in the system. Bad idea, if you add a question, you have to modify your metadata AND your queries. This is not how it's supposed to work. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com If you can't dazzle em with brilliance, baffle em with bullshit -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
