My questionaire table design-normalization! Your thoughts please?

[MsLady]

Im planning a db client's dinner questionaire that will comprise of client's favorites (meals, games, transportation). Each client is allowed to bring a guest. So the client's will also be filling out "the same set" of questionaire for his/her guest.

Meaning, a user will need to fill out the same form twice, one for himself and one for his guest.

Now, my question is, should i have two tables. Client's questionaire table and Guest questionaire tables with foreign key in the guest's table showing who's guest the question belongs to?

Or should i have one table for everyone (since they are the same set of questions). Having one table seem like a wise idea since it's the same set of questions (don't want to have 2 duplicate tables with only one field different). Now, if i go with one table. How do i go about handling this? So that client's and guest's info are linked. and i can run a query to tell who's guest it is? I have never tried thsi before. Care to share your thoughts?

 

[KenHigg]

I would say one table. Then the table would a have a client/quest fld and maybe an associatedClient fld...

Also - Have you searched 'survey' in the forum - There should be a ton of post on the topic.

 

[workmad3]

Not sure if it is 'technically' possible in Access, but you can certainly simulate this in code and the like:

One table with a relationship defined relating it to itself. Basically, your single table has its primary key, which is the ID of the questionnaire, and a foreign key, which is the tables primary key containing the ID of the person they were a guest for.

You can make this field allow nulls, and then the easy way of differenitating clients and guests is simply if they have something in this field (as a guest always needs to have someone they were there with, so always have an entry in this field).

 

[MsLady]

Okay then, the idea sounds perfect! One table it is!
I guess i can do a self join query on the table itself to get the guest for each client.

Thanks workmad3 and KenHigg