Table Architecture to Host super-multi query

[phillikl]

First off would like to say thanks for helping out with my last question! You guys are the bestest!!

OK now for the fun stuff. I am making a template "KISS" (keep it simple stupid) database. What my goal is to have a User create a login and profile of which animals they like. The database would then take thier selection of animals and ask specific questions for that type of animal. Then store the results into the Results table.

Here is the basic setup:

3-Tables:

tbluser
UID - pk - auto#
Username
Name
Password
*Animal*

tblquestion
QID -pk - auto#
animal
Question

tblresult
UID - indexed/linked
QID - indexed/linked
result - Yes/No

The animal section is giving me the issue. I know this seems minor, but this will be applied to database that is housing over 4,000 questions with over 2000 different selection fields (dropdowns, input, and textbox's) utilizing over 300 users per survey. Then inporting into a different database with over 800,000 results!

Sorry of the side track, back to our animals. I have a couple different ideas, use of checkbox's for each animal or text entry from drop down box (1 for each of the types of animals). Both of these seem redundant as well as an entry nightmear. I have never really messed with anything of this caliber, but am a quick learner. So even if you have an idea, I can normally find something to figure out how it works.

Thanks in advance!!!

Happy St. Patricks DAY!!!

~Kendall

[NeoPa]

Can you clarify how you envisage storing the Animal data (as there seems to be no dedicated Animal table, which I would have expected to see)?

All I see currently is an *Animal* field in the tblUser table, and I cannot see a sensible way of using this to do what you require.

[phillikl]

The *animal* field is where I have speed bumps.

I have been working with each animal having its own field in the tbluser

example:
tbluser
cat - yes/no
dog - yes/no
gerbil - yes/no
hedgehog - yes/no
etc..... (you can see now the redunancy issues)

or

new table:
tblAnimal
animal - text - linked to the tbluser!animal for list capabilities

The problem I am running into is, if I creat a drop down box, the user can select only 1 item, even with mutli-select, the field will not hold the values of the animals they have.

That clear up things a little?? I do have a tendancy to talk in circles =D

Thanks!

~Kendall

[FishVal]

Hello, Kendall.

That looks quite straightforward. You need:

• Table to store animals - tblAnimals.
• Bridge table tblUserAnimals connecting tblUser and tblAnimals in M-to-M relationship to store user "animals selection".
• tblquestion.animal obviously should be FK of tblAnimals.

The rest seems more or less ok.

Regards,
Fish.

[NeoPa]

Fish probably knew where I was going with this even before your response.

Let us know if you have any problems understanding this setup or its ramifications.

[phillikl]

Thanks Fish, and Thank you Neo!!

I'm pretty good at figuring things out; just need a small push in the right direction at times :)

I have setup the Many-to-Many relationship; I understand the concept, still doing some research on the limitations. Thats one thing I have noticed about all these expensive books... They are always more then happy to tell how to do something, but never the why and ramification. I was one of those kids that took his toys apart to see how they worked, then put them back together into something cool.

One thing I did question: Input forms for the bridge/link table. I would love to make my tblanimals into yes no check box for each entry then when a user creates thier profile it would add correct information to the linking table. I am thinking unbound checkbox's???

Ok guys, really appreciate the help... Your quickly making the top of my Christmas Card List!!!

~Kendall

[FishVal]

Hi, Kendall.

I think this stuff could help you to create a suitable interface.