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 help
Hi everybody,
I need to create a Survey db. In this db I’ll have tables such as SurveyType, Survey, Respondents, Questions, Answers, QuestionGroup, QuestionCategory, Users, etc. There are three major survey types (SurveyType table with SurveyTypeID: 1, 2, 3). The users who are ordering the Survey templates to be filled by respondents can order different versions of the major survey types: with or without answering questions about categories, with or without answering questions regarding work areas or supervisors, ages, limit of number of people taking the survey, etc. But, some group of questions is constant for the certain Survey Type. My question is whether I should have some SurveySubType table link to the SurveyType that would have a primary column SurveySubTypeID with entries such as 1111120 that would mean 1 – survey type 1, the next 1 would mean with categories, the next 1 – with work area, … 20 – limit of people, etc. Then 10000020 – survey type 1, 0 – means without categories, etc. I could add additional columns to the table such as IsCategory – Yes/No, IsWorkArea – Yes/No, etc., to have it more explanatory in addition to a first primary column coding 11111, etc. In this way it seems it would be easy to assign a proper survey template to a user who ordered it as all user’s/client’s requirements will be reflected in a first column coding SurveyTypeID of the SurveySubType table. The SurveySubTypeID + UserID + e.g. Date could create a unique combination for this Survey records in db. Please, advise if it could be a right approach. Thanks |
#2
|
|||
|
|||
DB design help
Have you looked at the sample survey application "At Your Survey" found at
http://www.rogersaccesslibrary.com/f...osts.asp?TID=3 ? -- Duane Hookom Microsoft Access MVP "Alex" wrote: Hi everybody, I need to create a Survey db. In this db I’ll have tables such as SurveyType, Survey, Respondents, Questions, Answers, QuestionGroup, QuestionCategory, Users, etc. There are three major survey types (SurveyType table with SurveyTypeID: 1, 2, 3). The users who are ordering the Survey templates to be filled by respondents can order different versions of the major survey types: with or without answering questions about categories, with or without answering questions regarding work areas or supervisors, ages, limit of number of people taking the survey, etc. But, some group of questions is constant for the certain Survey Type. My question is whether I should have some SurveySubType table link to the SurveyType that would have a primary column SurveySubTypeID with entries such as 1111120 that would mean 1 – survey type 1, the next 1 would mean with categories, the next 1 – with work area, … 20 – limit of people, etc. Then 10000020 – survey type 1, 0 – means without categories, etc. I could add additional columns to the table such as IsCategory – Yes/No, IsWorkArea – Yes/No, etc., to have it more explanatory in addition to a first primary column coding 11111, etc. In this way it seems it would be easy to assign a proper survey template to a user who ordered it as all user’s/client’s requirements will be reflected in a first column coding SurveyTypeID of the SurveySubType table. The SurveySubTypeID + UserID + e.g. Date could create a unique combination for this Survey records in db. Please, advise if it could be a right approach. Thanks |
#3
|
|||
|
|||
DB design help
In message , Alex
writes My question is whether I should have some SurveySubType table link to the SurveyType that would have a primary column SurveySubTypeID with entries such as 1111120 that would mean 1 – survey type 1, the next 1 would mean with categories, the next 1 – with work area, … 20 – limit of people, etc. Then 10000020 – survey type 1, 0 – means without categories, etc. I could add additional columns to the table such as IsCategory – Yes/No, IsWorkArea – Yes/No, etc., to have it more explanatory in addition to a first primary column coding 11111, etc. Building a complex key with this type of substructure is a nightmare to document and maintain. If you need multiple fields then use them. This looks complicated enough that you should probably start by sketching out a relationship diagram on paper before you start designing tables. -- Bernard Peek |
#4
|
|||
|
|||
DB design help
Thank you so much.
It seems as I resolved this problem. I've created a gstnGroups table for question grouping where I have surveyid, qstnGroupID, and qstnGrpName. I've added the same qstnGroupID column into Questions table as well. It will be an independent for each survey question grouping and a user/creator who has a right to modify it can do it editing, deleteing, and adding groups based on surveyid. For now I've created relationships between Survey table and gstnGroups through surveyid to be able to with cascade deleting delete question groups with a survey deleting. Another independent grouping will be based based on webpage question list that will be the same for all questions on the page. I've created a separate table with surveyid, pageGrpID, and pageGrpName accordingly. I think it's more than enough flexibility for grouping. "Bernard Peek" wrote: In message , Alex writes My question is whether I should have some SurveySubType table link to the SurveyType that would have a primary column SurveySubTypeID with entries such as 1111120 that would mean 1 – survey type 1, the next 1 would mean with categories, the next 1 – with work area, … 20 – limit of people, etc. Then 10000020 – survey type 1, 0 – means without categories, etc. I could add additional columns to the table such as IsCategory – Yes/No, IsWorkArea – Yes/No, etc., to have it more explanatory in addition to a first primary column coding 11111, etc. Building a complex key with this type of substructure is a nightmare to document and maintain. If you need multiple fields then use them. This looks complicated enough that you should probably start by sketching out a relationship diagram on paper before you start designing tables. -- Bernard Peek |
Thread Tools | |
Display Modes | |
|
|