Jump to content

Optimizing db layout


shoemilk

Recommended Posts

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.