Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Questionaire Database Design

    HI,

    I am trying to design a questionaire databse. The questionaires can have a combination of text, multiple choice, ranked questions (see attached example)

    However I am struggling to design the necessary tables to accomadate such complex questions similar to question 3 and 4 in attachment where you have 2 or more 'groups' of multiple choice options per question or where a mutiple choice group also includes an 'Other' as textbox.

    basically I need a structure that is flexible enough for me to create a questionnaire via asp/dot.net or php so that once the structure and layout has been defined some code can take the information from the database and dynamically create the form rather than me hand crafting a html form for each questionnaire to interface the database.

    Any help in improving this db structure to achieve the above would be much appreciated. I would also need some way to implement a layout for the questions i.e. for multiple choice questions whether they should be displayed vertically or horizontally.


    Code:
    -- Create the Forms table.
    CREATE TABLE [dbo].[Questionnaire](
        [QuestionnaireID] [int] IDENTITY(1,1) NOT NULL,
        [QuestionnaireName] [nvarchar](50) NOT NULL,
        [QuestionnaireDetails] [nvarchar] (1000) NOT NULL,
        [CreatedDate] [datetime] NOT NULL CONSTRAINT DF_CreatedDate DEFAULT (GETDATE()),
    	CONSTRAINT [PK_Questionnaire] PRIMARY KEY CLUSTERED 
    		([QuestionnaireID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    );
    GO
    
    
    -- Create the TextAnswers table.
    CREATE TABLE [dbo].[TextAnswers](
        [QuestionID] [int] NOT NULL,
        [ResponseID] [int] NOT NULL,
        [AnswerText] [nvarchar](1000) NULL,
    	CONSTRAINT [PK_TextAnswers] PRIMARY KEY CLUSTERED 
    		([QuestionID] ASC, [ResponseID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    	CONSTRAINT [FK_TextAnswers_Questions] FOREIGN KEY
    		([QuestionID]) REFERENCES [dbo].[Questions] ([QuestionID]) 
          CONSTRAINT [FK_TextAnswers_Reponses] FOREIGN KEY
    		([ResponseID]) REFERENCES [dbo].[Reponses] ([QuestionID]) 
    );
    GO
    
    
    -- Create the ChoiceAnswers table.
    CREATE TABLE [dbo].[ChoiceAnswers](
        [QuestionID] [int] NOT NULL,
        [ResponseID] [int] NOT NULL,
        [ChoiceID] [int] NOT NULL,
        [OtherText] [nvarchar](1000) NULL,
    	CONSTRAINT [PK_ChoiceAnswers] PRIMARY KEY CLUSTERED 
    		([QuestionID] ASC, [ResponseID] ASC, [ChoiceID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    	CONSTRAINT [FK_ChoiceAnswers_QuestionChoices] FOREIGN KEY
    		([ChoiceID]) REFERENCES [dbo].[QuestionChoices] ([ChoiceID])
           CONSTRAINT [FK_TextChoiceAnswers_Reponses] FOREIGN KEY
    		([ResponseID]) REFERENCES [dbo].[Reponses] ([QuestionID]) 
    );
    GO
    
    
    -- Create the Questions table.
    CREATE TABLE [dbo].[Questions](
        [QuestionID] [int] IDENTITY(1,1) NOT NULL,
        [QuestionnaireID] [int] NOT NULL,
        [TypeID] [int] NOT NULL, /* e.g textbox, radio, etc */
        [QuestionText] [nvarchar](500) NOT NULL,
        [QuestionDetails] [nvarchar](500) NOT NULL,
        [SequenceID] [int] NOT NULL, /* order in which question are displayed */
        [Mandatory] [varchar](1) NOT NULL ,
    	CONSTRAINT [PK_Questions] PRIMARY KEY CLUSTERED 
    		([QuestionID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    	CONSTRAINT [FK_Questions_Questionnaire] FOREIGN KEY
    		([QuestionnaireID]) REFERENCES [dbo].[Questionnaire] ([QuestionnaireID])
           
    );
    GO
    
    
    -- Create the QuestionChoices table.
    CREATE TABLE [dbo].[QuestionChoices](
        [QuestionID] [int] NOT NULL,
        [ChoiceID] [int] NOT NULL
        [TypeID] [int] NOT NULL, /* e.g radio, checkbox, etc
        [ChoiceText] [nvarchar](100) NOT NULL,
        [ChoiceDetails] [nvarchar](500) NOT NULL,
    	CONSTRAINT [PK_QuestionChoices] PRIMARY KEY CLUSTERED 
    		([QuestionID] ASC, [ChoiceID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    	CONSTRAINT [FK_QuestionsChoices_Questions] FOREIGN KEY
    		([QuestionID]) REFERENCES [dbo].[Questions] ([QuestionID]) ON DELETE CASCADE
    	
    );
    GO
    
    
    -- Create the FormQuestions table.
    CREATE TABLE [dbo].[FormQuestions](
    	[QuestionID] [int] NOT NULL,
        [FormID] [int] NOT NULL,
        [SequenceID] [int] NOT NULL,
    	CONSTRAINT [PK_FormQuestions] PRIMARY KEY CLUSTERED 
    		([QuestionID] ASC, [FormID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    	CONSTRAINT [FK_FormQuestions_Questions] FOREIGN KEY
    		([QuestionID]) REFERENCES [dbo].[Questions] ([QuestionID]) ON DELETE CASCADE
    	CONSTRAINT [FK_FormQuestions_Forms] FOREIGN KEY
    		([FormID]) REFERENCES [dbo].[Forms] ([FormID]) ON DELETE CASCADE
    );
    GO
    Attached Thumbnails Attached Thumbnails questionnaire.PNG  
    Last edited by ozzii; 07-18-12 at 15:10. Reason: error

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Seems to me like this is a candidate for a hierarchical table structure.

    Look at the BOL cite for "WITH common_table_expression" for an example of a hierarchical table structure.

    The BOL example uses an employee/manager relationship to demonstrate a hierarchical table structure.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by PracticalProgram View Post
    Seems to me like this is a candidate for a hierarchical table structure.

    Look at the BOL cite for "WITH common_table_expression" for an example of a hierarchical table structure.

    The BOL example uses an employee/manager relationship to demonstrate a hierarchical table structure.
    whats the BOL cite? Do you have a link?

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    BOL=Books On Line, SQL's help system.

    Or, Google: SQL Server WITH and it's the second item.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •