Optimizing db layout

[shoemilk]

Hello, I'm making a website that will have a weekly quiz on it and I was wondering what the best way to structure the db is. I want to keep a record of what the user answered for everytime they take the quiz (they can re-test an unlimited amout of times). Every week there will be a new test available, so the number of answers that I'll be keeping track of will grow rapidly.
My current layout has a table for the questons of the week (week_1 and week_2 are seperate tables), a table to keep track of the users, and then each user has one table to keep track of all of their test answers. Eventually the user's table will just become huge and have several thousand entries in it.
At what point would the user's table be overwhelmingly large that searches to it would be noticable slow and/ or cause errors? Is there a different way I should structure it? Another idea I thought would to have a table for each user for each week (ie userA_week_1) but say I had 1000+ users and 54 weeks, that's a lot of tables. As it is, I'm starting to wonder about having seperate tables for each week... any advice on which is better, mas tables or mas number of entries would be appretiated.
Thank you,
Brian

[fenway]

Yeah, that's not ideal. You need a table for users, a table for quizzes, and a table for answers. You add a new entry into the quiz table every week, and you simply add a new record into the answers table (user_id, quiz_id, answer, NOW() ) every time someone submits an answer. Multiple tables is never a good idea where multiple rows will do.

As far as performance, until you get into a few hundred thousand rows, you shouldn't notice anything. Even then, a properly indexed table should be just fine. Based on your numbers, 1000 users a week is only 52K answers a year, so I wouldn't worry about this.

Hope that helps.