Quiz - user submissions into MySQL database

Question

Hello

What I am trying to achieve: a quiz system with user account and cumulative results table.

Process:

-User sets up an account -User logs in -User completes quiz -Answers go into results table -Results table displayed

My database structure:

Table 1: users

user_id  
username
password
email

Table 2: quizzes

quiz_id
title

Table 3: questions

question_id
quiz_id
question
question_notes

Table 4: answers

answer_id
question_id
user_id
answer
answer_notes

Table 5: responses

response_id
quiz_id
user_id
submit_time

The questions will be output from Table 3 via a SELECT.

What I am looking for some pointers for is how I can ensure the relationships for each quiz entry is consistent, so when I run the INSERT statements the IDs are consistent (so the "question_id" for Table 3 and Table 4 are the same, for example)

I am thinking I will have 2 INSERT statements for Table 4 and Table 5. In these insert statements, is there a way to ensure the relationships match?

I am having some trouble visualising how this will work for entering the data into the database, once I've got this figured out I can tackle using the data.

Any pointers to decent tuts or a bit of insight into possible form processing would be much appreciated.

Many Thanks

Answer 1

Foreign keys and transactions enforce referential integrity...

For example:

Answers

question_id is a foreign key to question.id
user_id is a foreign key to user.id
(These are set in the table definition)

You insert in to answers like so (sloppy pseudocode):

begin transaction
int qid = select id from question
int uid = select id from user
insert (qid,uid,...) into answers
commit transaction

Answer 2

There's LAST_INSERT_ID() function in MySQL ( mysql_insert_id() in PHP ) that will return auto_increment id from last insert query. This will let you keep consistency.

See here for more details: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id