database architecture.......

[cram81]

hi
i am really after some advice with regards to the design of my data base... i am family with databases and mySql but i am having trouble designing the architecture for this particular one and was in need of any helpful input.

basically i want to facilitate multiple users and for each user they have 10 question / answer combos stored.
Each question and answer can be of one of four types.

I cant think how i would model this in my database...
obviously each user will need a unique id , but do u guys think that each question answer pair should be stored together as one value and then have a table which has 10 columns for each Q or A ......

or

have 8 seperate tables each corresponding to each of the Q/A types and then store the users unique id along with that particular question or answer. Each question / answer pair would then be given an id and so i can search through the tables looking for user id and question_answer id to retrieve the data?

i hope i have explained the problem well enough ... its just that i have never worked with a database of this nature and so even i feel a bit confused about it.

any help is appreciated
many thanks
mark

 

[r937]

user ( uid, name, ... )

question ( qid, qtext, qtypeid, ... )

questiontype ( qtypeid, qtypedescr )

useranswer ( uid, qid, atext, ... )



rudy

http://r937.com/

 

[cram81]

hi
ive got tables as follows...

user (u_id, name, password) // primary key : u_id
question (q_id, q_text, q_type) // primary key : q_id
users_ans (u_id, q_id, a_id, a_text)

now for my users_ans table i think i want u_id, q_id and a_id as my primary keys but when i do this the a_id doesn't increment when new values are added to the tabe.....

can i have 3 primary keys ... ive tried combinations with two but they dont increment correctly either ...

any suggestions?

thanks

 

[r937]

you do not need an incrementing key for the user_ans table

the primary key for the user_ans table should be just these two columns: (u_id, q_id)

this will also ensure that the same user cannot answer the same question more than once


rudy