Hello,
Thanks for the comeback, I really apologize for not being able to write down DDL queries, Now I've a question on my database design and below are the assumed requirements. I'm building a survey questionnaire application, where there are 2 scenarios,
1) User will be populated with first question on the form and when he/she selects answer for that question, based on the Q&A combination I need to load the next set of questions on the same screen below the first question. (based on Is_Conditional "Y" flag)
2) User will be shown the first question and when he/she selects the answer, based on the Q&A combination, I need to load next set of sequential questions in the next screen (based on Is_Conditional flag set to "N")
Below are the DDL statements
USE [master]
GO
/****** Object: Database [PCCI] Script Date: 9/19/2014 1:17:23 PM ******/
CREATE DATABASE [PCCI]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'PCCI', FILENAME = N'D:\SQLData\Data\PCCI.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'PCCI_log', FILENAME = N'D:\SQLData\Data\PCCI_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [PCCI] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [PCCI].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [PCCI] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [PCCI] SET ANSI_NULLS OFF
GO
ALTER DATABASE [PCCI] SET ANSI_PADDING OFF
GO
ALTER DATABASE [PCCI] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [PCCI] SET ARITHABORT OFF
GO
ALTER DATABASE [PCCI] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [PCCI] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [PCCI] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [PCCI] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [PCCI] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [PCCI] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [PCCI] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [PCCI] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [PCCI] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [PCCI] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [PCCI] SET DISABLE_BROKER
GO
ALTER DATABASE [PCCI] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [PCCI] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [PCCI] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [PCCI] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [PCCI] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [PCCI] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [PCCI] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [PCCI] SET RECOVERY FULL
GO
ALTER DATABASE [PCCI] SET MULTI_USER
GO
ALTER DATABASE [PCCI] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [PCCI] SET DB_CHAINING OFF
GO
ALTER DATABASE [PCCI] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [PCCI] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [PCCI]
GO
/****** Object: StoredProcedure [dbo].[usp_GetAnswersforQuestion] Script Date: 9/19/2014 1:17:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Pavan Puligandla
-- Create date: 17-Sep-2014
-- Description:To fetch answers for questions that have conditional questions from ConditionalQuestions table
-- =============================================
CREATE PROCEDURE [dbo].[usp_GetAnswersforQuestion]
@QuestionID smallint
AS
BEGIN
SET NOCOUNT ON;
with cte as (
select cq.ConditionalQuestionID from ConditionalQuestions cq
inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=@QuestionID and qa.IsConditional='Y')
select distinct q.Question, a.Answer from QuestionAnswers qa
inner join Answers a on a.AnswerID = qa.AnswerID
inner join Questions q on q.QuestionID = qa.QuestionID
inner join cte c on c.ConditionalQuestionID = qa.QuestionID;
END
GO
/****** Object: Table [dbo].[Answers] Script Date: 9/19/2014 1:17:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Answers](
[AnswerID] [smallint] IDENTITY(1,1) NOT NULL,
[Answer] [nvarchar](max) NOT 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] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[ConditionalQuestions] Script Date: 9/19/2014 1:17:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ConditionalQuestions](
[QuestionID] [smallint] NOT NULL,
[ConditionalQuestionID] [smallint] NOT NULL,
[SequentialOrder] [smallint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[PatientQuestionAnswers] Script Date: 9/19/2014 1:17:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PatientQuestionAnswers](
[PatientID] [smallint] NOT NULL,
[SurveyID] [smallint] NULL,
[QuestionID] [smallint] NOT NULL,
[AnswerID] [smallint] NOT NULL,
[TotalScore] [smallint] NULL,
[DateCreated] [datetime] NOT NULL,
[DateModified] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[QuestionAnswers] Script Date: 9/19/2014 1:17:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QuestionAnswers](
[QuestionID] [smallint] NOT NULL,
[AnswerID] [smallint] NOT NULL,
[SurveyID] [smallint] NULL,
[IsConditional] [char](1) NOT NULL,
[ScoreValue] [smallint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Questions] Script Date: 9/19/2014 1:17:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Questions](
[QuestionID] [smallint] IDENTITY(1,1) NOT NULL,
[Question] [nvarchar](max) NOT 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] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Survey] Script Date: 9/19/2014 1:17:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Survey](
[SurveyID] [smallint] IDENTITY(1,1) NOT NULL,
[SurveyName] [nvarchar](max) NOT NULL,
[SurveyDescription] [nvarchar](max) NULL,
[DateCreated] [datetime] NOT NULL,
[DateModified] [datetime] NULL,
CONSTRAINT [PK_Survey] PRIMARY KEY CLUSTERED
(
[SurveyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Answers] ON
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (1, N'I haven’t needed to go')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (2, N'I couldn’t be seen at a convenient time')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (3, N'I couldn’t get to the GP surgery or health centre easily')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (4, N'I didn’t like or trust the doctors')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (5, N'Yes')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (6, N'No')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (7, N'Cant remember')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (8, N'There werent any appointments')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (9, N'The times offered didnt suit me')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (10, N'The appointment was with a doctor I didnt want to see')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (11, N'I could have seen a nurse but I wanted to see a doctor')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (12, N'In the past 3 months')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (13, N'In the past 6 months')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (14, N'More than 6 months ago')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (15, N'Between 3 and 6 months ago')
GO
INSERT [dbo].[Answers] ([AnswerID], [Answer]) VALUES (16, N'I have never seen health care centre')
GO
SET IDENTITY_INSERT [dbo].[Answers] OFF
GO
INSERT [dbo].[ConditionalQuestions] ([QuestionID], [ConditionalQuestionID], [SequentialOrder]) VALUES (1, 2, 1)
GO
INSERT [dbo].[ConditionalQuestions] ([QuestionID], [ConditionalQuestionID], [SequentialOrder]) VALUES (1, 3, 1)
GO
INSERT [dbo].[ConditionalQuestions] ([QuestionID], [ConditionalQuestionID], [SequentialOrder]) VALUES (1, 4, 1)
GO
INSERT [dbo].[ConditionalQuestions] ([QuestionID], [ConditionalQuestionID], [SequentialOrder]) VALUES (2, 4, 2)
GO
INSERT [dbo].[ConditionalQuestions] ([QuestionID], [ConditionalQuestionID], [SequentialOrder]) VALUES (5, 2, 1)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (1, 1, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (1, 2, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (1, 3, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (1, 4, NULL, N'Y', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (2, 5, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (2, 6, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (4, 12, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (4, 13, NULL, N'Y', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (4, 14, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (4, 15, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (4, 16, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (3, 5, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (3, 6, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (5, 1, NULL, N'Y', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (5, 2, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (5, 3, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (5, 4, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (5, 5, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (5, 6, NULL, N'N', NULL)
GO
INSERT [dbo].[QuestionAnswers] ([QuestionID], [AnswerID], [SurveyID], [IsConditional], [ScoreValue]) VALUES (5, 7, NULL, N'N', NULL)
GO
SET IDENTITY_INSERT [dbo].[Questions] ON
GO
INSERT [dbo].[Questions] ([QuestionID], [Question]) VALUES (1, N'Are you aware of your right to 48-hour access to general medical services under the GMs contract?')
GO
INSERT [dbo].[Questions] ([QuestionID], [Question]) VALUES (2, N'n the past 6 months, have you tried to see a GP or healthcare professional fairly quickly? ')
GO
INSERT [dbo].[Questions] ([QuestionID], [Question]) VALUES (3, N'Were you able to see a GP or healthcare professional on the same day or in the next 2 days the GP surgery or health centre was open? ')
GO
INSERT [dbo].[Questions] ([QuestionID], [Question]) VALUES (4, N'If you couldnt be seen within the next 2 days the GP surgery or health center was open, Why was that? ')
GO
INSERT [dbo].[Questions] ([QuestionID], [Question]) VALUES (5, N'If you haven’t seen a GP or healthcare professional in the past 6 months, why is that? ')
GO
SET IDENTITY_INSERT [dbo].[Questions] OFF
GO
ALTER TABLE [dbo].[ConditionalQuestions] WITH CHECK ADD CONSTRAINT [ConditionalQuestionID_ConditionalQuestions_Questions_QuestionID] FOREIGN KEY([ConditionalQuestionID])
REFERENCES [dbo].[Questions] ([QuestionID])
GO
ALTER TABLE [dbo].[ConditionalQuestions] CHECK CONSTRAINT [ConditionalQuestionID_ConditionalQuestions_Questions_QuestionID]
GO
ALTER TABLE [dbo].[ConditionalQuestions] WITH CHECK ADD CONSTRAINT [QuestionID_ConditionalQuestions_Questions-QuestionID] FOREIGN KEY([QuestionID])
REFERENCES [dbo].[Questions] ([QuestionID])
GO
ALTER TABLE [dbo].[ConditionalQuestions] CHECK CONSTRAINT [QuestionID_ConditionalQuestions_Questions-QuestionID]
GO
ALTER TABLE [dbo].[PatientQuestionAnswers] WITH CHECK ADD CONSTRAINT [AnswerID_PatientQuestionAnswers_Answers_AnswerID] FOREIGN KEY([AnswerID])
REFERENCES [dbo].[Answers] ([AnswerID])
GO
ALTER TABLE [dbo].[PatientQuestionAnswers] CHECK CONSTRAINT [AnswerID_PatientQuestionAnswers_Answers_AnswerID]
GO
ALTER TABLE [dbo].[PatientQuestionAnswers] WITH CHECK ADD CONSTRAINT [QuestionID_PatientQuestionAnswers_Questions_QuestionID] FOREIGN KEY([QuestionID])
REFERENCES [dbo].[Questions] ([QuestionID])
GO
ALTER TABLE [dbo].[PatientQuestionAnswers] CHECK CONSTRAINT [QuestionID_PatientQuestionAnswers_Questions_QuestionID]
GO
ALTER TABLE [dbo].[PatientQuestionAnswers] WITH CHECK ADD CONSTRAINT [SurveyID_PatientQuestionAnswers_Survey_SurveyID] FOREIGN KEY([SurveyID])
REFERENCES [dbo].[Survey] ([SurveyID])
GO
ALTER TABLE [dbo].[PatientQuestionAnswers] CHECK CONSTRAINT [SurveyID_PatientQuestionAnswers_Survey_SurveyID]
GO
ALTER TABLE [dbo].[QuestionAnswers] WITH CHECK ADD CONSTRAINT [AnswerID_QuestionAnswers_Answers_AnswerID] FOREIGN KEY([AnswerID])
REFERENCES [dbo].[Answers] ([AnswerID])
GO
ALTER TABLE [dbo].[QuestionAnswers] CHECK CONSTRAINT [AnswerID_QuestionAnswers_Answers_AnswerID]
GO
ALTER TABLE [dbo].[QuestionAnswers] WITH CHECK ADD CONSTRAINT [QuestionID_QuestionAnswers_Questions_QuestionID] FOREIGN KEY([QuestionID])
REFERENCES [dbo].[Questions] ([QuestionID])
GO
ALTER TABLE [dbo].[QuestionAnswers] CHECK CONSTRAINT [QuestionID_QuestionAnswers_Questions_QuestionID]
GO
ALTER TABLE [dbo].[QuestionAnswers] WITH CHECK ADD CONSTRAINT [SurveyID_QuestionAnswers_Survey_SurveyID] FOREIGN KEY([SurveyID])
REFERENCES [dbo].[Survey] ([SurveyID])
GO
ALTER TABLE [dbo].[QuestionAnswers] CHECK CONSTRAINT [SurveyID_QuestionAnswers_Survey_SurveyID]
GO
USE [master]
GO
ALTER DATABASE [PCCI] SET READ_WRITE
GO
My doubt is if user selects first question and answer, based on that i'll check for IsConditional flag and loads the next sequential or conditional questions respectively. What if the populated question has multiple sequential questions?
Thanks