CODEWITHSUNDEEP
sqldatabasedatabase-design
user1396717
user1396717

Reputation: 33

Is this database design proper for storing the survey results?

In my company, we asked all some departments employees to participate in a survey. The structure of this survey is as following: it consists of four categories; I, II, III, IV. Three categories have couple of questions, while the last category has subcategories from A to L. Each Subcategory has many questions. And most of these questions have subquestions.

There are two kinds of questions; multiple choice questions with different number of choices (sometimes 2 or 4 or 5 or 6 choices) and written question (such as please comment on the following stuff...). An example of a question that has subquestions: What do you think about the following services? Service 1
Service 2 Service 3

By the way, this question is a multiple choice question where each subquestions (such as service1) has 5 choices.

The problem now is just creating a database that stores the user information, the questions, and the user answers. The purpose of this database to come up with statistics later on based on the question. For instance, I have to write a query that shows how many employees said (Agree or Strongly Disagree) on Question #3 with showing the subquestions, too.

I came up with following incomplete database design, but it seems to be complicated:

Employee Table: Username, Name, DepartmentID
Department Table: DepartmentID, DepartmentName
Category Table: CategoryID, CategoryName
SubCategory Table: SubCategoryID, SubCategoryName, CategoryID
Question Table: QuestionID, Question
SubQuestion Table: SubQuestionID, SubQuestion, QuestionID
Answer Table: AnswerID, Answer, QuestionID, SubQuestionID

So what is the best database design that can stores all of this information and then gives me the general statistics that I want?

Upvotes: 0

Views: 1619

Answers (2)

Firoz Ansari
Firoz Ansari

Reputation: 2515

My suggestion will be something like this:

Employee Table: UserId, Username, Name, DepartmentId
Department Table: DepartmentId, DepartmentName
Category Table: CategoryId, CategoryName, ParentId

Question Table: QuestionId, Question, CategoryId
QuestionChoices Table: ChoiceId, QuestionId, Choice, Sequence

Answer Table: AnswerId, QuestionId, UserId, Text
AnswerChoices Table: AnswerId, QuestionId, UserId, ChoiceId, [Value]

Upvotes: 2

SQLCurious
SQLCurious

Reputation: 524

Your Employee & Department tables look fine. I have the following suggestions:

-- Include Sub-, Category, Sub- & Question numbers in their respective tables (i.e.: I, II, III, IV for Category, A, B, C etc for questions and so on, assuming Question/CategoryName is the full-length Question/Category)

-- QuestionID table should include a SubCategoryID FK.

-- I would also have an employeeID as suggested in comments (usernames may be reused after people leave).

-- Then I'd design the following tables:

Answer Table:

AnswerID
SubquestionID
AnswerType (choose between 'Multiple', 'Long')
AnswerName (NULL if AnswerType is 'Long', a, b, c, etc if 'Multiple')

SurveyResults Table: 

EmpID (FK from Employee table),
SubQuestionID (a, b, c, ...; again if no subquestion, then put in a)
Answer (varchar(4000), so you can accommodate both types of answers)

Note that you should keep an Answer table to hold the choices for the multiple choice questions.

Upvotes: 0

Related Questions