If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
db design for survey with two sets of questions per survey??
Hello, I work for a non-profit organization, where I'm working on a
survey database. I need to survey houses and their residents. Houses can be in different categories, each category having its own questions, and then the category determines what questions are asked of the residents. So far I have: tblSurvey: specific information about each survey, including what category the house survey belongs to (seven possible categories of house), survey date, who is doing the survey, etc. tblHouseSurvey: data about the overall house survey, including HouseSurveyID and the category of the house being surveyed tblHouseSurveyResponses: questionID, questionResponse, HouseSurveyID (1 to 1 relationship to tblSurvey) tblQuestions: questionID, questionText tblQuestionsCategories: table showing which questionID goes with which questionCategory (every house is in only one category, but a question can be in more than one category), a query is used against this table to populate the survey questions in the form, not to forget that comments can be made against each survey category (e.g. Safety). Not all categories have all the same questions, or this table would not be necessary... tblPersonSurvey: data about each person's survey tblPersonSurveyResponses: contains questionID, questionResponse, PersonSurveyID (1 to MANY relationship to tblSurvey, which links together all the survey responses for both house and people in each house) I looked at Mr. Hookom's excellent survey tool but I am not sure it applies to this kind of survey (it doesn't seem to allow the above table setup), would someone be willing to comment??? I am fairly experienced with Access, I just want to make sure my tables/database design is headed in the right direction. Thank you, Tom |
#3
|
|||
|
|||
Where I'm getting stuck is the 'junction table' that knows which
questions to assign to a survey of either a house or of a person based on whether the question is a house or person question, and what category that house or person is in. Questions asked about houses are not asked about persons and vice versa, but the category names for the survey questions are the same. I Googled many-to-many and junction tables etc. but didn't find much that helped me understand this. I should look again. The original design had separate tables for all the 'house' stuff and all the 'person' stuff. Maybe I need to go back to working with things that way, but there are 7 categories for a house survey. I had had a field in tblQuestions, with all the category codes in it (any combination of codes up to 7), separated by commas, and another field to show whether it is a person or house question. Maybe this will be easier to work with, since the only reason I need to differentiate is for the Access forms per se, I have other tables that store the PersonID or HouseID, the QuestionID, and the QuestionAnswer text... I will keep working at it. Thank you, tom |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
table design and relationships for housing survey questions | Darren | Database Design | 9 | November 16th, 2004 06:37 PM |
DB design questions | rick m | New Users | 6 | November 15th, 2004 08:29 PM |
Masonic Emblems Where can I get online ? Thanks | Vanman | General Discussion | 3 | September 23rd, 2004 08:26 PM |