May 20, 2015 at 9:53 am
Hi, I've read quite a few Articles/Posts on this website and it's been a great asset. Figured I'd see if someone else could come up with a better schema then the current one I've devised.
Problem: Needing to create a Table Schema to house Customer Satisfaction Survey Results, Questions & Responses. We want to ensure Customer Anonymity with the records, so no additional information beyond Date Entered is being recorded for each unique Result set recorded.
My Proposed Solution *(Psuedo Code, I didn't wanna put in all my code, as I prefer using CONSTRAINT at end of statement, so might be mistakes)
Table: Questions
CREATE TABLE Questions
(
QuestionID INT PRIMARY KEY CLUSTERED IDENTITY(1,1) NOT NULL,
QuestionNumber VARCHAR(255) NOT NULL, --This would be like 'Question1', 'Question2', etc
QuestionDetail VARCHAR(255) NOT NULL
)
Table: Responses
CREATE TABLE Responses
(
ResponseID INT PRIMARY KEY CLUSTERED IDENTITY(1,1) NOT NULL,
QuestionFK INT NOT NULL,
ResponseValue INT NOT NULL,
ResponseDetails VARCHAR(255) NULL,
CONSTRAINT FK_QuestionFK FOREIGN KEY (QuestionFK) REFERENCES schema.Questions(QuestionID)
)
Table: Summary
CREATE TABLE Summary
(
SummaryID INT PRIMARY KEY CLUSTERED IDENTITY(1,1) NOT NULL,
DateEntered SMALLDATETIME NOT NULL,
Question1 TINYINT,
Question2 TINYINT,
CONSTRAINT CK_Question1 CHECK (Question1 BETWEEN 1 AND 5),
CONSTRAINT CK_Question2 CHECK (Question2 BETWEEN 1 AND 10)
)
In this scenario, the questions range in datatype - Some questions have a range of INT 1-5 or 1-10 some are VARCHAR comments etc. This allows me to input the actual question into the Question Table and tie it to the response in the Response table with a Parent/Child relationship. The Check Constraints on the Summary table allows me to restrict the responses that are allowed in the specific column, but there is no relationship between the Question1 Column and it's possible responses in the Response table.
I know that I could go ahead and create a table for each question, but I dislike that option.
My question: Can anyone think of how I can enforce a relationship between Summary.Question1 and the possible responses for that question listed in schema.Responses.QuestionFK where QuestionFK matches the column name in Summary?
May 20, 2015 at 1:20 pm
Well here's what I'm thinking:
**EDIT - Forgot to DEFAULT the Summary.QuestionFK column to it's default Question#, I know this isn't necessary - but I see no reason not to**
Table: Questions - Keep the same
CREATE TABLE Questions
(
QuestionID INT PRIMARY KEY CLUSTERED IDENTITY(1,1) NOT NULL,
QuestionNumber VARCHAR(255) NOT NULL, --This would be like 'Question1', 'Question2', etc
QuestionDetail VARCHAR(255) NOT NULL
)
Table: Responses - Add Unique Key Constraint on QuestionFK & ResponseValues
CREATE TABLE Responses
(
ResponseID INT PRIMARY KEY CLUSTERED IDENTITY(1,1) NOT NULL,
QuestionFK INT NOT NULL,
ResponseValue INT NOT NULL,
ResponseDetails VARCHAR(255) NULL,
CONSTRAINT FK_QuestionFK FOREIGN KEY (QuestionFK) REFERENCES schema.Questions(QuestionID),
CONSTRAINT UK_Question_ResponseValues UNIQUE (QuestionFK, ResponseValues)
)
Table: Summary - Remove Check Constraint, Change columns to Question1FK & Question1Value, Create Composite FK on Responses(QuestionFK, ResponseValues)
CREATE TABLE Summary
(
SummaryID INT PRIMARY KEY CLUSTERED IDENTITY(1,1) NOT NULL,
DateEntered SMALLDATETIME NOT NULL,
Question1FK INT NOT NULL DEFAULT '1',
Question1Value INT NOT NULL,
Question2FK INT NOT NULL DEFAULT '2',
Question2Value INT NOT NULL,
CONSTRAINT FK_Question1 FOREIGN KEY (Question1FK, Question1Value) REFERENCES Responses (QuestionFK, ResponseValue),
CONSTRAINT FK_Question2 FOREIGN KEY (Question2FK, Question2Value) REFERENCES Responses (QuestionFK, ResponseValue)
)
Time to see if I can make this work....
May 20, 2015 at 2:26 pm
Yep, works like a charm. I'm just gonna leave this here in case some other person stumbles upon it when they need help figuring out something like this.
May 20, 2015 at 5:10 pm
I'm late, but a few flaws. One, your questions table doesn't allow for separate surveys. If someone has a new survey they want, or change one, you can't "version" yours, or add a second. Really you want a surveyID in there so that questions are bundled into a survey. Or you want a separate table that links questions together.
If you do that, then your "question number" is really poorly named. Really you have a question title of some sort now, but you might want ordering to be included here as well. A separate tinyint (or smallint)
Responses looks OK for the response, but how it this linked to an individual. How do you look at responses v the first time Question1 is answered v the second time? Do you care? I'd add something here that links to a specific response bundle so I know which answers were given at the same time as which other answers.
Hard to tell what summary is for or what this means.
May 20, 2015 at 9:30 pm
I definitely appreciate your response, but for this project it should be sufficient. In this specific case, we do not want to capture the individuals identity or user information - it's intended for an internal User Satisfaction Survey that is completed yearly and we want to ensure their annonymity.
I definitely think I'm going to incorporate your survey version ID, though the questions also will not be changing - they were all arrived at after a board meeting completed last year and we will be using DateEntered to identify the records for a specific date period. Beyond that, the individual responses is all we are concerned with.
Basically, each Record in the Summary table would be a unique completion of the survey. Also I know the 'QuestionNumber' column title is...poor. The actual column names are more specific, but as the questions vary (and the datatype being inserted into the column varies as well) I had to come up with a sort of generic naming convention for them [Not to mention the existing standards I have to follow for naming conventions]
You gave me a few things to think about for certain, at this point I was thinking of just archiving the table during yearly maintenance tasks...but versioning the survey seems far more elegant and gives me more scalability. I don't foresee the questions/responses changing any time soon, but I definitely have a couple things to mull over tomorrow while I'm verifying existing backup strategies.
Anymore insight is always greatly appreciated!
May 21, 2015 at 12:19 pm
You are welcome and my pleasure, and I wasn't implying your design was wrong. Just that I saw a few holes based on how I'd view the system.
If it works for your requirements, good. I try to think ahead, and it isn't so much to capture user information, but to perhaps know that a particular set of answers came together on a survey. Might help determine why one answer looks a certain way. I might also include a datestamp on the answers, but that's me.
May 21, 2015 at 12:56 pm
A few more thoughts...
Is there any mechanism for a "parent" and "child" type questions?
Example 1, if you answer NO to question 5, you get taken to question 10. If you answer yes, you need to answer question 6, 7, 8, and 9.
Example 2:
Question 5 is formed as a "check all that apply" type question.
If you select one of the answers, more sub-questions appear.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 21, 2015 at 2:07 pm
@steve-2 - I definitely wasn't taking it as any negative commentary. From what I understand the responses for each survey taken will be inserted simultaneously in a single INSERT statement, upon each completion of the entire survey. With this being the case the DateEntered column alone should be sufficient to track the responses for each Unique completion of the Survey.
I'm definitely trying to think ahead and very open to any dialogue that helps expand my knowledge, currently I'm trying to devise another schema that will allow different types of Survey's to be captured in a single Table Topology...though currently we only have two Survey's being tracked, with different schema's, so I'm thinking I'll wait until I have at least one more to start coming up with a model that will be able to incorporate all three of them together.
All that being said, I'm still not 100% happy with this schema as it looks cluttered to me in the Summary Table. There's a QuestionFK & QuestionValue column for each unique question (excepting 'Comment' questions, which I'm just having them INSERT as just plain text, still deciding if we want to encrypt these or not), which just doesn't sit overly well with me...though as opposed to creating a Table for each question to have a list of acceptable responses, in order to enforce relationships and data integrity, this seems like the better option.
@michael-2 - To answer your question, kinda. What I'm currently doing, is giving the subquestions a unique QuestionNumber value - Example: 'Question 8' has 10 Subquestion with radio options that evaluate to 7 possible responses. I've given each subquestion a QuestionNumber of 'Question 8A', 'Question 8B', etc within the Questions table and in the QuestionDetail defined what the actual question is, then I specify the acceptable responses for these Subquestions within the Responses table for each QuestionNumber (Question 8A can be Response 1,2,3, etc)
This allows me to record the parent question and the child questions all within the same table, though in this instance the Parent question is simply a statement that does not actually record any responses. It's simply in the Questions table to track what Question 8 is.
Currently there are no 'IF THEN' questions and should not be, for this particular survey, in the future.
That being said, I was thinking of creating a creating a Child table for the 'Questions' table (Something like 'SubsectionQuestions') and then tying that into the Parent table. This is what I did with the previous survey (it is far different because of how and what information we needed to capture) but I didn't see a reason to do so with this one.
Can you think of a better schema than that, to tie a question and it's subquestions together?
May 21, 2015 at 2:22 pm
@steve-2 - Oh forgot to add this, there's additional metadata columns for 'When was record entered', 'Who entered record', 'When was record changed', 'Who changed' etc for the Questions and Responses. I just didn't include them in the schema as I was trying to keep it as understandable as possible without adding extra complexity in my explanation.
Got distracted in my response and forgot to mention that, sorry!
May 21, 2015 at 2:56 pm
Sounds like you have a good idea of what's needed and what's required.
Good luck.
If you want to write up some of this, talk about what/why you did things and made decisions, it would be an interested case study. Ping me if you're interested.
May 22, 2015 at 7:27 am
Michael L John (5/21/2015)
A few more thoughts...Is there any mechanism for a "parent" and "child" type questions?
Example 1, if you answer NO to question 5, you get taken to question 10. If you answer yes, you need to answer question 6, 7, 8, and 9.
Example 2:
Question 5 is formed as a "check all that apply" type question.
If you select one of the answers, more sub-questions appear.
I think that is called a "branching" survey - so you're right - depending on the requirements, the OP may need to design that in as well.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 22, 2015 at 7:28 am
Steve Jones - SSC Editor (5/21/2015)
Sounds like you have a good idea of what's needed and what's required.Good luck.
If you want to write up some of this, talk about what/why you did things and made decisions, it would be an interested case study. Ping me if you're interested.
I agree. Surveys probably come up time and time again and it would be great if this thread could lead to some standard design guidelines for survey db designs.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 22, 2015 at 11:49 am
webrunner (5/22/2015)
Steve Jones - SSC Editor (5/21/2015)
Sounds like you have a good idea of what's needed and what's required.Good luck.
If you want to write up some of this, talk about what/why you did things and made decisions, it would be an interested case study. Ping me if you're interested.
I agree. Surveys probably come up time and time again and it would be great if this thread could lead to some standard design guidelines for survey db designs.
- webrunner
Oh definitely I wouldn't be against writing up something along these lines, unfortunately I just moved to this town to accept this position (been working in SQL for over a decade now, but this is my first actual dedicated DBA position) and I have limited options for internet service at my house...and the options are quite horrible to be honest.
Once I have a third survey and come up with a more inclusive schema, hopefully by that time I should have that all settled and I can set aside some time to do a write up on this. And I believe you're correct Webrunner, it's a branching Survey if I recall correctly. I'll definitely try and come up with a more streamlined schema, I like tackling these kinds of things...it's like when you get a new lego set and you think of all the different ways you can put the pieces together!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply