June 26, 2017 at 9:52 am
I am creating a survey for us to use on our clients. I have my current table structure shown below. Depending on the client it may ask the user to select from the following demographics: their department; length of employment; location; age group; gender; employee type; ethnicity.
I am currently holding the demographic information in the answers table. However, this has created a problem as I need to be able to break down responses by the different demographics. Is it possible to do this the way I have my tables set up or should I add a different table to just hold demographic information? I thought about a single row for each participant that accounts for each of the above but it could have a lot of null values where the question is not used. This option allows me to do a join to get my results though
My Tables
[Answers](
[PNum] [int] NOT NULL,
[QuestionID] [int] NOT NULL,
[Answer] [nvarchar](max) NOT NULL
)
[Participants](
[PNum] [int] IDENTITY(1,1) NOT NULL,
[Email] [nvarchar](50) NOT NULL,
[SurveyNumber] [int] NOT NULL,
[Status] [char](1) NOT NULL
)
[QuestionGroup](
[GroupNumber] [int] IDENTITY(1,1) NOT NULL,
[SectionID] [int] NOT NULL,
[GroupText] [nvarchar](200) NULL
)
[QuestionTypes](
[QType] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](10) NOT NULL
)
[Sections](
[SectionID] [int] IDENTITY(1,1) NOT NULL,
[Header] [nvarchar](100) NOT NULL
)
[Survey](
[SurveyNumber] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](100) NOT NULL,
[GUID] [char](8) NOT NULL
)
[SurveyQuestions](
[QuestionID] [int] IDENTITY(1,1) NOT NULL,
[SurveyNumber] [int] NOT NULL,
[SectionID] [int] NOT NULL,
[GroupNumber] [int] NOT NULL,
[QuestionText] [nvarchar](400) NOT NULL,
[QType] [int] NOT NULL,
[NumTextBoxes] [int] NULL,
[Sequence] [int] NULL
)
[SurveyResponses](
[ResponseID] [int] IDENTITY(1,1) NOT NULL,
[QuestionID] [int] NOT NULL,
[ResponseText] [nvarchar](200) NOT NULL,
[Sequence] [int] NOT NULL,
[RequireFollowUp] [bit] NOT NULL
)
Data in tables above
QuestionTypes Data
1 Matrix
2 Text Input
3 Radio
4 Checkbox
Sections Data
1 Section 1: Attitudes & Perceptions
2 Section 2: Meaningful Work & Empowerment
3 Section 3: Positive Environment
4 Section 4: Trust in Leadership
5 Section 5: Purpose & Values
6 Section 6: Growth & Learning Opportunity
7 Section 7: Reward & Recognition
8 Demographics
Groups Data
1 1 Attitudes & Perceptions
2 1 Name 3 things employer does well
3 1 Name 3 things employer could do better
4 2 Roles & Responsibilities
5 2 Client Engagement
6 2 What one thing could we do to
7 3 Physical Space
8 3 Collaboration
9 3 Idea Sharing
10 3 How do ideas/opportunities get shared within the company
11 4 Leadership Direction & Engagement
12 4 Communication Quality & Frequency
13 4 What methods are typically used for communicating within the company
14 4 What is your preferred method of communication
15 5 Vision & Values
16 5 In your opinion, what are top 3-5 core values of the company?
17 6 Support & Development
18 7 Reward & recognition
19 8 Demographics
I can get the scores with a query similar to this
SELECT COUNT(Answer) as num, Answer FROM dbo.Answers WHERE QuestionID = 1 AND PNum IN (SELECT PNum FROM dbo.Participants WHERE SurveyNumber = 1) GROUP BY ANSWER
June 27, 2017 at 2:03 am
When the user has the option of inputting their demographic, can they have more than one? Ideally, when dealing with a many-to-many relationship, you would use a composite key table. In very simple terms:CREATE TABLE dbo.Answer (AnswerID int, UserID int);
CREATE TABLE dbo.Demographic (DemographicID int, DemographicDescription varchar(50));
GO
--Now your composite table
CREATE TABLE dbo.AnswerDemographic (AnswerID int, DemographicID int);
ALTER TABLE dbo.AnswerDemographic WITH CHECK ADD CONSTRAINT FK_AnswerID FOREIGN KEY (AnswerID)
REFERENCES dbo.Answer (AnswerID);
ALTER TABLE dbo.AnswerDemographic WITH CHECK ADD CONSTRAINT FK_DemographicID FOREIGN KEY (DemographicID)
REFERENCES Demographic (DemographicID);
GO
DROP TABLE dbo.Answer;
DROP TABLE dbo.Demographic;
GO
Is my understanding for that correct?
Then you could do a query along the lines of:SELECT COUNT(A.AnswerID) AS Num,
A.Answer
FROM dbo.Answer A
JOIN dbo.AnswerDemographic AD ON A.AnswerID = AD.AnswerID
JOIN dbo.Demographic D ON AD.DemographicID = D.DemographicID
WHERE A.QuestionID = 1
AND D.DemographicDescription = 'Sales Department';
So you can easily count the answers for specific demographics, and people answering can give all their demographic details, instead of having to choose only one, when many might apply.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 27, 2017 at 5:20 am
I would isolate your demographic questions from the other responses. You probably aren't going to run scores on them, but rather use them to filter queries or to bucket respondents into groups for reporting. Also, if you do allow more than one response (making it a multiple mention) for demographic questions, you have to be prepared that your percentages will add up to more than 100%. Multiple mention isn't appropriate for some of them (such as income range) so give some thought to where you want to allow multiple answers and always allow for when the respondent doesn't want to answer an optional question.
June 27, 2017 at 9:03 am
Thom A - Tuesday, June 27, 2017 2:03 AMWhen the user has the option of inputting their demographic, can they have more than one?
Not within the same category. They can only provide a single answer within a demographic category. However, we may ask about multiple categories so it may ask about location, department and length of employment or it may only ask about length of employment or employee type.
So my thought was to set a table up like so
[Demographics](
[PNum] [int] NOT NULL,
[LOE] [int] NULL,
[Dept] [int] NULL,
[Location] [int] NULL,
[EmpType] [int] NULL,
[Age] [int] NULL,
[Gender] [int] NULL,
[Ethnicity] [int] NULL
)
Or I could set it up like this
[Demographics]
(
[PNum] [int] NOT NULL,
[QuestionID] [int] NOT NULL,
[Answer] [int] NOT NULL
)
The benefits of setting up like the top table is I will not have to get the Question number of the length of employment question before I run the query. Instead I can go right to the LOE column and make my joins. One negative is that table will have a lot of nulls when the demographic is not used. Another negative is if they have some demographic that I have not thought of I will not have a place to store the answer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply