Setting up a databse for a quiz

[Anthony_john5]

Basically I want to make a quiz system. I'm fairly sure I know how it's going to work, but the databse side is confusing me. I want users to be able to submit quizzes in certain categories and then for other users to take part in them. I'm thinking I'll need two tables, one for the quizzes and one for user results.

However, I have no idea what the best way to store the quiz questions and answers in the database. Has anyone got any suggestions?

• Anthony

[drawmack]

do you want multiple choice, fill in the blank, true/false or a combination? I will give a db solution for the combination:

quizes
    q_id bigint autonumber
    q_name varchar(100)
    num_quests tinyint
    date_posted datetime (unique)

questions
    qu_id bigint autonumber
    q_id   bigint (links to quizes::q_id)
    q_text varchar(255)
    q_type enum (multiple choice, true/false, fill in)

answers
    a_id bigint autonumber
    qu_id bigint (links to questions::qu_id)
    a_text varchar(255)
    a_order (for multiple choice questions only)
    correct tinyint (actually true false, used for true false and multiple choice)

users
    u_id bigint autonumber
    username varchar(50)
    password varchar(50)

results
     r_id bigint autonumber
     u_is bigint (links to users::u_id)
     q_id bigint (links to quizes::q_id)
     score float

of course this is just the very basic schema it's up to you to do the indexing.