CODEWITHSUNDEEP
database-designsql-server-2008-r2
user1395782
user1395782

Reputation: 107

How to get the number of respondents for each choice of answers?

I have a survey that I need to design a database for it to store its results and retrieve the results using some queries.

The current database design that I have is as following:

Respondents Table: RespondentID, Name, OrgCode
Department Table: OrgCode, DepartmentName
Category Table: CategoryID, CategoryName
SubCategory Table: SubCategoryID, SubCategoryName, CategoryID
Questions Table: QuestionID, QuestionDesc, CategoryID, SubCategoryID
SubQuestions Table: SubQuestionID, SubQuestionDesc, QuestionID
Answers Table: AnswerID, AnswerDesc, QuestionID, SubQuestionID
CompleteSurvey Table: ID, RespondentID, CategoryID, AnswerID

An example of this survey: 

Category I
    SubCategory A
    Question 1: what do you think about the following service
        SubQuestion 1: Service #1 
            (Strongly Agree, Agree, Disagree, Strongly Disagree)
        SubQuestion 2: Service #2 
            (Strongly Agree, Agree, Disagree, Strongly Disagree)

Let us assume that there are three employees answered this question

Now, I want to write a query that shows the number of respondents who said Strongly Agree, Agree, Disagree and Strongly Agree in each question or subquestion. Is it doable by this database design? If yes, could you please help me with this query?

Also, do you think there is other designs that are much simpler than the above design? If yes, could you please recommend me with one of them?

Database Design:

/****** Object:  Table [dbo].[Departments]    Script Date: 05/20/2012 07:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Departments](
    [OrgCode] [float] NOT NULL,
    [DepartmentName] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED 
(
    [OrgCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Category]    Script Date: 05/20/2012 07:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
    [CategoryID] [int] IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
    [CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SubCategory]    Script Date: 05/20/2012 07:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubCategory](
    [SubCategoryID] [int] IDENTITY(1,1) NOT NULL,
    [SubCategoryName] [nvarchar](50) NOT NULL,
    [CategoryID] [int] NOT NULL,
 CONSTRAINT [PK_SubCategory] PRIMARY KEY CLUSTERED 
(
    [SubCategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Respondents]    Script Date: 05/20/2012 07:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Respondents](
    [RespondentID] [nvarchar](50) NOT NULL,
    [Name] [varchar](50) NULL,
    [OrgCode] [float] NOT NULL,
 CONSTRAINT [PK_Respondents] PRIMARY KEY CLUSTERED 
(
    [RespondentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Questions]    Script Date: 05/20/2012 07:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Questions](
    [QuestionID] [int] IDENTITY(1,1) NOT NULL,
    [QuestionDesc] [nvarchar](max) NOT NULL,
    [CategoryID] [int] NULL,
    [SubCategoryID] [int] NULL,
 CONSTRAINT [PK_Questions] PRIMARY KEY CLUSTERED 
(
    [QuestionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CompleteSurvey]    Script Date: 05/20/2012 07:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CompleteSurvey](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [RespondentID] [nvarchar](50) NOT NULL,
    [CategoryID] [int] NOT NULL,
    [AnswerID] [int] NOT NULL,
 CONSTRAINT [PK_CompleteSurvey] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SubQuestions]    Script Date: 05/20/2012 07:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubQuestions](
    [SubQuestionID] [int] IDENTITY(1,1) NOT NULL,
    [SubQuestionDesc] [nvarchar](max) NOT NULL,
    [QuestionID] [int] NULL,
 CONSTRAINT [PK_SubQuestions] PRIMARY KEY CLUSTERED 
(
    [SubQuestionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Answers]    Script Date: 05/20/2012 07:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Answers](
    [AnswerID] [int] IDENTITY(1,1) NOT NULL,
    [AnswerDesc] [nvarchar](max) NOT NULL,
    [QuestionID] [int] NULL,
    [SubQuestionID] [int] NULL,
 CONSTRAINT [PK_Answers] PRIMARY KEY CLUSTERED 
(
    [AnswerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  ForeignKey [FK_SubCategory_Category]    Script Date: 05/20/2012 07:26:11 ******/
ALTER TABLE [dbo].[SubCategory]  WITH CHECK ADD  CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO
ALTER TABLE [dbo].[SubCategory] CHECK CONSTRAINT [FK_SubCategory_Category]
GO
/****** Object:  ForeignKey [FK_Respondents_Departments]    Script Date: 05/20/2012 07:26:11 ******/
ALTER TABLE [dbo].[Respondents]  WITH CHECK ADD  CONSTRAINT [FK_Respondents_Departments] FOREIGN KEY([OrgCode])
REFERENCES [dbo].[Departments] ([OrgCode])
GO
ALTER TABLE [dbo].[Respondents] CHECK CONSTRAINT [FK_Respondents_Departments]
GO
/****** Object:  ForeignKey [FK_Questions_Category]    Script Date: 05/20/2012 07:26:11 ******/
ALTER TABLE [dbo].[Questions]  WITH CHECK ADD  CONSTRAINT [FK_Questions_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO
ALTER TABLE [dbo].[Questions] CHECK CONSTRAINT [FK_Questions_Category]
GO
/****** Object:  ForeignKey [FK_Questions_SubCategory]    Script Date: 05/20/2012 07:26:11 ******/
ALTER TABLE [dbo].[Questions]  WITH CHECK ADD  CONSTRAINT [FK_Questions_SubCategory] FOREIGN KEY([SubCategoryID])
REFERENCES [dbo].[SubCategory] ([SubCategoryID])
GO
ALTER TABLE [dbo].[Questions] CHECK CONSTRAINT [FK_Questions_SubCategory]
GO
/****** Object:  ForeignKey [FK_CompleteSurvey_Respondents]    Script Date: 05/20/2012 07:26:11 ******/
ALTER TABLE [dbo].[CompleteSurvey]  WITH CHECK ADD  CONSTRAINT [FK_CompleteSurvey_Respondents] FOREIGN KEY([RespondentID])
REFERENCES [dbo].[Respondents] ([RespondentID])
GO
ALTER TABLE [dbo].[CompleteSurvey] CHECK CONSTRAINT [FK_CompleteSurvey_Respondents]
GO
/****** Object:  ForeignKey [FK_SubQuestions_Questions]    Script Date: 05/20/2012 07:26:11 ******/
ALTER TABLE [dbo].[SubQuestions]  WITH CHECK ADD  CONSTRAINT [FK_SubQuestions_Questions] FOREIGN KEY([QuestionID])
REFERENCES [dbo].[Questions] ([QuestionID])
GO
ALTER TABLE [dbo].[SubQuestions] CHECK CONSTRAINT [FK_SubQuestions_Questions]
GO
/****** Object:  ForeignKey [FK_Answers_Questions]    Script Date: 05/20/2012 07:26:11 ******/
ALTER TABLE [dbo].[Answers]  WITH CHECK ADD  CONSTRAINT [FK_Answers_Questions] FOREIGN KEY([QuestionID])
REFERENCES [dbo].[Questions] ([QuestionID])
GO
ALTER TABLE [dbo].[Answers] CHECK CONSTRAINT [FK_Answers_Questions]
GO
/****** Object:  ForeignKey [FK_Answers_SubQuestions]    Script Date: 05/20/2012 07:26:11 ******/
ALTER TABLE [dbo].[Answers]  WITH CHECK ADD  CONSTRAINT [FK_Answers_SubQuestions] FOREIGN KEY([SubQuestionID])
REFERENCES [dbo].[SubQuestions] ([SubQuestionID])
GO
ALTER TABLE [dbo].[Answers] CHECK CONSTRAINT [FK_Answers_SubQuestions]
GO

Upvotes: 0

Views: 143

Answers (1)

Joel Brown
Joel Brown

Reputation: 14408

There are two ways that people usually do the type of query that you want to do. The problem would be very simple - just use the COUNT aggregate function, except that you probably want to report on answers that have never been chosen. Aggregate functions and outer joins don't play well together.

The first way to get the answer you're looking for is to use Correlated Sub-Queries.

The second way is to use Common Table Expressions (or CTEs).

Correlated sub-queries are easier to understand, but can be less efficient than CTEs. CTEs take longer to learn but they can solve problems that plain old SQL can't.

Upvotes: 1

Related Questions