DB design help

[Alex]

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

 

[Duane Hookom]

Have you looked at the sample survey application "At Your Survey" found at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3 ?

 

[Duane Hookom]

Have you looked at the sample survey application "At Your Survey" found at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3 ?

 

[Bernard Peek]

In message <[email protected]>, Alex

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]

In message <[email protected]>, Alex

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.

 

[Alex]

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.