adding more tables ... design...

[cram81]

hi (most probably to r937 !!)

just a quick question...

currently im using tables:

user (user_id,username,password,pic_id,qNum)
question(q_id,q_text,q_type)
users_ans(user_id,q_id,a_text)
images(pic_id, path).

i want to add the option of allocating a sound file to a user and that the user has a string associated with the sound file. I want to keep the table structure as 'normalised' as possible ....

what would be my best way of implementing this...
i thought...
user(user_id,username,password,pic_id,qNum,sound_id,s_answer)
sound(sound_id, sound_path)

but then thought maybe the user table is then holding too much information.... so maybe it should go in the users_answers table:

users_ans(user_id,q_id,a_text,sound_id,sound_text)
sound(sound_id, sound_path)

??
thanks

 

[hos2]

it depends what it is you want.

is it possible for a user to have more than sound file ??? if yes then case you're first option will not do

is there a relation between answers and a soundfile ?? if no then second option is not good.

just add the soundpath to the user table when they are 1:1 connected

 

[cram81]

the sounds are unrelated to the users_answers but there is an 'answer' associated with sounds....

maybe then like u said i could just then add the sound id the users table and then myabe sound answer to the answers table
leaving me with...
user (user_id,username,password,pic_id,qNum,sound_id)
question(q_id,q_text,q_type)
users_ans(user_id,q_id,a_text,sound_answer)
images(pic_id, path)
sounds(sound_id, sound_path)

 

[hos2]

user (user_id,username,password,pic_id,qNum,sound_id)
question(q_id,q_text,q_type)
users_ans(user_id,q_id,a_text,sound_id)
images(pic_id, path)
sounds(sound_id, sound_path)

you rever to the same sounds table I think

further on I always use shortcuts for each table to define field names so you can always recognize a fieldname
like
user(user_id,username,userpassword,userpic_id,userqnum,usersound_id)
question(q_id,q_text,q_type)
users_ans(uauser_id,uaq_id,ua_text,uasound_id)
images(pic_id, picpath)

I always found it easier to read when queries become more complex

 

[r937]

hey cram, i'd love to help, but i see you got your "answer" already, eh

f.y.i. when all we see are tables and columns, it is very difficult to get a good understanding of what "should" go where, because, as hos2 pointed out, it is not always clear what the one-to-many or many-to-many relationships are

sample rows of data usually help

rudy

http://r937.com/

SQL Consulting

 

[cram81]

thanks for the replies but..i didnt quite get if anyone agreed with any of my solutions? i get your point though Hos2 of labelling columns according to the table they are in...
r937 .. here are some examples of data if that makes life easier .....(took me a while to type) (got any tips of writing output to a file for future reference?)


SELECT * FROM user WHERE user_id = '178'
+---------------------------------------------------+
|user_id | username | password | pic_id | q_num |
+---------------------------------------------------+
| 178 | baloo33 | peach | 2 | 3 |
+---------------------------------------------------+

SELECT * FROM users_ans WHERE user_id='178'
+--------------------------------+
|user_id | q_id | a_text |
+---------------------------------
| 178 | 3 | fish |
+--------------------------------+
| 178 | 4 | hello |
+--------------------------------+
| 178 | 6 | sunday |
+--------------------------------+
| 178 | 101 | five |
+--------------------------------+

SELECT * FROM question
+------------------------------------+----------+
|q_id | q_text | q_type |
+------------------------------------+----------+
| 3 | what is favourite food? | O |
+------------------------------------+----------+
| 4 | what is favourite colour| C |
+------------------------------------+----------+
| 6 | Favourite day | O |
+------------------------------------+----------+
| 178 | Hello today I ________. | Con |
+------------------------------------+----------+


SELECT * FROM images;
+------------------------------------+
|pic_id | path |
+------------------------------------+
| 3 | c:\temp\img1.jpg |
+------------------------------------+
| 4 | c:\temp\img3.jpg |
+------------------------------------+
......continued...

and i propose to have a sound table ... like the images table (above) exceot with the path to the sound files for each i.d.
in terms of my architecture i need a central repository of the location of all the sound files available so that a user can choose a sound file and allocate it to themselves along with a word or phrase associated with that sound...whereby on hearing their sound at a later date they are prompted to enter there word or phrase for that sound file to autheniticate themselves....

thanks

 

[hos2]

so you just build a sound table as described
sound(sound_id, sound_path)

and you make a field in you're user table if you only want to allow one sound for each user

usersound_id

 

[cram81]

yeah thats what i thought ...

thanks

 

[r937]

i don't understand the purpose of q_num in the user table

for user 178, it seems to be pointing to question 3

but user 178 has answers to 4 different questions in user_ans, including an answer to a question that does not exist in the question table

or did you just create some inconsistent samples? because i can understand inconsistent samples, i've done that too

rudy

http://r937.com/

SQL Consulting

 

[cram81]

i thought someone would mention that qNum thing... its basically a variable which i use to display how many questions for each user are displayed...
ie : qNum = 3, means for that user, 3 questions are selected at random from the users_ans table for that user particular id.