Hi all, just got back from the MySQL Users Conference. I had a great time and learned a lot of new stuff.
Okay, to my question.
I am working on a survey. After the session on table normalization, I have decided to pull the comments field from my answers table and put it in it's own table because it can be null and could potentially waste a huge amount of space. I want to write my answers to the answer table and then get that autoincremented primary answer table key and use it to connect it with the comments table.
Is there an easier, more reliable way of doing that other than doing a select just after the insert, using the same criteria that I used to build the insert string?
The survey is a bit more complicated than the average survey. There are actually 4 surveys with 4 different sets of questions, targeting 4 different groups of users. The customer wants the ability to change or add/remove questions as they refine everything to make sure they are asking things in a way to get the most accurate feedback so they can improve their processes. So, I have my answer table set up one row per question/answer. My answer table looks like this:
[INDENT][FONT=Courier New]
id int(10) unsigned auto_increment primary_key
userKey int(10) unsigned
surveyKey tinyint(4) unsigned
questionNumber varchar(5)
answer varchar(5)
question varchar (255)
responseDate date[/FONT]
[/INDENT]
and the comment table looks like this:
[INDENT][FONT=Courier New]
id int(10) unsigned primary_key
comment text
responseDate date[/FONT]
[/INDENT]
I want the to use the id from the answers table for the id in the comments table.
Also, I am storing the actually question that was asked because the customer wants the option of changing the question in the future, but also wants to be able to keep track of exactly what question was asked when the survey was taken. There is probably an easier, more efficient way to do this as well instead of storing the actual text in a varchar for each answer.
