Database Structure Question

[kwdelre]

Hey guys,

 

I am building a quiz management system and have a few basic structure questions and need some advice. (I am using mysql and PHP)

 

There will be approximately 15 quizzes with unlimited users. users will be able to review previous quizzes (or "track" quizzes)

 

Should I:

 

a) Create a database for each Quiz and then a table for each student that comes through; and gather all quiz data in this one specific table.

 

b) Create one database. Create a table for each quiz and then make each row designate the student and results

 

c) Something entirely different.

 

I'm wondering which would be easier in the long run when there is 2,000 users.

 

Thanks for your thoughts

[joel24]

You should have one database, and several tables,

One for the quiz names, settings, etc

One for quiz questions (linked to the quiz name with a quizID and identified with a questionID)

One for users (login, password, name, email etc)

One for usersQuestions which will list the attempted questions. This table would hold the questionID and the userID and the mark for that question. Primary keys questionID and userID, then use a INSERT ON DUPLICATE UPDATE query and if the user attempts to redo the question, this will update the row with the new mark rather than creating a new one, otherwise it will just insert a new row (for first attempts)

[ignace]

It depends on the Quiz you are trying to make. If it's a typical Question and select from multiple possible Answers then a normal Question/Answer set-up will do. However, if you are after creating a Quiz that allows to make a Question but the Answer can be text as well as only a text input field then you will be required to go for a different approach.

[kwdelre]

You should have one database, and several tables,

One for the quiz names, settings, etc

One for quiz questions (linked to the quiz name with a quizID and identified with a questionID)

One for users (login, password, name, email etc)

One for usersQuestions which will list the attempted questions. This table would hold the questionID and the userID and the mark for that question. Primary keys questionID and userID, then use a INSERT ON DUPLICATE UPDATE query and if the user attempts to redo the question, this will update the row with the new mark rather than creating a new one, otherwise it will just insert a new row (for first attempts)

 

Thank you so much for the detailed answer. I think I see what you are thinking. I will give it a try.

 

I'm learning most of php as I go. Do you know of any tutorials that are specific to quiz systems? I know there are ones out there that you can use pre-made, but I would really rather build this myself.

 

Thanks again!

[joel24]

haven't heard of any, though I'm sure google will source one for you.