Database Model for Feedback Module

Question

I wish to generate an online feedback system. Requirements are

• Admin can enter feedback questions
• Admin will select these questions and create a feedback session
• One Question can be used in many feedback session
• User can select any feedback session and answer/rate
• one user can answer same feedback session more than once

My table structure is

enter image description here

dummy data (not all columns)

Questions
---------
QuestionId Question
1 Question 1
2 Question 2
3 Question 3
4 Question 4
5 Question 5
Feedback
--------
FeedbackId Questions 
2 1,2,3,4,5 -- Questions column has questionid from Question table
 FeedbackResults
 ---------------
 FeedbackResId FeedbackId QuetionID Answers UserId
 1 2 4 4 1
 2 2 1 3 1
 3 2 5 3 1
 4 2 1 2 2

Please rate this design and provide valuable suggestions. Comma separated values are affordable ?

• Surensiveaya: can anyone please help
• Gert Arnold: User = Customer? It would help to post the DDL of these tables to make it easier to propose improvements. My first impression is that this model doesn't enable one (same) feedback session to be answered by one user more than once.
• Surensiveaya: yes user is customer

Answer 1

The design need to be simplified. From your explanation, it seems you only need 3 tables (Questions, Answers, and Feedback) the rest are not needed, since you can JOIN these tables.

Comma separated values are affordable ?

For your design, no you don't need to, and it's not always a good idea to do it. There is some cases you can do it, and might be benefit you, but these are rare cases. Keep things simple and standard, don't join columns or rows into a single value, unless it's a requirement!

So, if your Feedback.Questions meant to store values of questions in comma separated value, I suggest you change that. just give each question a new feedback id, this way it'll be much more manageable. And instead of storing the question string, just store the questionId (FK) instead. this way it'll be more appropriate and would process faster.

So I suggest you remodel it to :

#ListStatus
- Id [PK|IDENTITY]
- Description
#ListRate 
- Id [PK|IDENTITY]
- Description
#FeedbackQuestions 
- Id [PK|IDENTITY]
- Question 
- CreatedAt
- UpdatedAt 
- Deleted
- StatusId
- UserId
#FeedbackAnswers 
- Id [PK|IDENTITY]
- QuestionId 
- Answer 
- CreatedAt 
- UpdatedAt 
- Deleted
- UserId 
#Feedback 
- Id [PK|IDENTITY]
- RateId
- QuestionId
- AnswerId 
- CreatedAt 
- Deleted
- UserId 

The Listxxx tables would store a list of fixed descriptive values. For instance, ListRate would store the rate options such as Excellent, Very Good, Good, Fair, Poor ..etc. When a user rates an answer, the Id of ListRate would be inserted into Feedback table.

Also, since we defined them with a prefix List it would be standardized in the system design, for general use purpose on other tables, and best yet, easier to understand (so developers would know the purpose of these tables just from the table name).

I got confused between UserId & CustomerId, but I used userId so you're working with logged-in users doesn't matter if is it from Admin or some customer using the system, at the end, you need to record the Username that made this changes .

Another thing you might need is to add a category for the questions, and maybe the Feedback as well. This will be very helpful in filtering the results. You don't want to depend on any column that store user input with open text, it'll be a real pain in the neck. So, adding a category to the questions, which will store it's values in another table to have two columns (one for displaying text for user, the other one is the id) will make things easier to work with, and if you see that you can add more filtering options (either supporting sub-category or tags ..etc) go for it.

Sample :

Questions Table

| Id | Question | CreatedAt | UpdatedAt | Deleted | StatusId | UserId |
|----|------------|----------------------|-----------|---------|----------|--------|
| 1 | Question 1 | 2019年12月14日T00:00:00Z | (null) | (null) | 1 | User 1 |
| 2 | Question 2 | 2019年12月14日T20:40:15Z | (null) | (null) | 2 | User 2 |
| 3 | Question 3 | 2019年12月14日T07:05:01Z | (null) | (null) | 3 | User 3 |

Answers Table

| Id | QuestionId | Answer | CreatedAt | UpdatedAt | Deleted | UserId |
|----|------------|----------|----------------------|-----------|---------|---------|
| 1 | 1 | Answer 1 | 2019年07月05日T00:00:00Z | (null) | (null) | User 50 |
| 2 | 1 | Answer 2 | 2019年07月05日T00:00:00Z | (null) | (null) | User 50 |
| 3 | 1 | Answer 3 | 2019年07月05日T00:00:00Z | (null) | (null) | User 70 |
| 4 | 2 | Answer 1 | 2019年07月05日T00:00:00Z | (null) | (null) | User 70 |

Feedback Table

| Id | RateId | QuestionId | AnswerId | CreatedAt | UpdatedAt | Deleted | UserId |
|----|--------|------------|----------|----------------------|-----------|---------|---------|
| 1 | 1 | 1 | 3 | 2019年07月05日T00:00:00Z | (null) | (null) | Admin |
| 2 | 2 | 2 | 4 | 2019年07月05日T00:00:00Z | (null) | (null) | User 23 |
| 3 | 2 | 1 | 2 | 2019年07月05日T00:00:00Z | (null) | (null) | User 5 |
| 4 | 5 | 1 | 3 | 2019年07月05日T00:00:00Z | (null) | (null) | User 23 |

Fiddle Demo

• Surensiveaya: Thank you for your answer. But i am not still clear with your design. which table has feedback sessions (refer my point 2: Admin will select these questions and create a feedback session). There can be multiple feedback sessions from which a user/customer can select the feedback to answer
• iSR5: @SachuMine Feedback table(the last one) should stores the feedback sessions.
• Surensiveaya: could you please put some sample data in tables. The id of Feedback table is unique? How we we know which feedbak session is answered?
• iSR5: @SachuMine I have added a sample data and a live demo so you'll have a better view on the model.