create a table for a multiple choice question

[Winter1]

Hi there,

I am struggling with designing my database:

I have a table that has 31 fields: they are all locations. Each field has a choice of 11 different activities, from walking, cycling, fishing etc.
The primary key is my SurveyIDForm.
If I have a person that does more than one activity in each location, I would have to input that twice. Therefore having two or more records with the same SurveyFormID. I have tried to enforce referential intergrity in my relationships....but this comes up with a problem and it doesn't allow it. If I don't have enforced referential intergrity I think I may end up with orphan records.


Also it doesn't allow me to have one to many relationship.

I have another table with this same problem too, although on a smaller scale, I have 2 fields and in the field called ownership I can have 5 different options, ie the person might own a bike, a car etc, but if the person owns two or three different things I would have more than one record, with the same surveyIDform.

I am not sure how to do this, any suggestions would be great. Once I get this right I will go onto design my forms!

Thanks

Winter

[June7]

Multiple records associated with same SurveyFormID sounds reasonable. So the question is why can't you enforce referential integrity? Why do you have 31 fields for the same kind of data? This is not a normalized structure.

[Winter1]

So I am not sure why I can't enforce referential integrity and I also agree, but not really sure how to organise the table....31 field for different locations is a pain, but at each location people could be doing more than one activity....infact most people do a combination of three different activities, but there is a choice of 11 different activities.

I could have 11 fields with the different types of activities, but most people go to at least 10 or 12 different locations.

Any suggestions?

[June7]

Conventional structure would be a related table with a record for each activity for each location for each person.

Consider:

tblPeople
PersonID

tblPeopleLocations
ID (primary key)
PersonID (foreign key)

tblLocationActivities
PeopleLocationID (foreign key)

[Winter1]

Thank you got it, I have many tables, and the location and activities are set in two fields! I have managed to get my one-many relationships and enforce referential integrity. I have now run a test of queries and they seem to give me intelligible answers!

Winter