Database Model for Feedback Module
-
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
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 -
Is this a blog post, or do you have a question?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I want someone to review and comment any suggestions regarding this design. sorry forgot to mention that
-
I have a bit of an issue with the questions table
you are fixed to option a,b,c,d
why not make a "question option table" and make it a key value pair - that way you can have different number of options for each question
imagine you want option E then you have to alter the table - this also limits you to A-Z options , so i'd advise using numbers
just my thoughts, feel free to ignore me 🙂
MVDBA
-
Thats great really. And could you suggest a way to avoid questions being saved as CSV in Feedback table
-
the same principle, a key value pair (maybe)
the brilliant pair of database engineers (mr boyce and mr codd) - (google them) devised normalisation in databases
1st normal form (remove repeating data groups) - that csv is a repeating data group
you want to aim for third normal form
again, just an opinion and a polite suggestion 🙂
MVDBA
-
You need to back up and actually do logical data modeling (Google "logical data modeling") not table modeling. You literally cannot normalize what you posted because there are no usable keys to do normalization against. The entire normalization process depends on data keys.
That is, you need to define the entities (things) about which you will store data. You've identified some of them above.
I'm still confused on the entire thing. For example, feedback vs question. It appears they are directly related but somehow also distinct? You stated:
Admin can enter feedback questions
Similary, I guess user in your model is the same as customer?! Very hard to tell.
Don't start by trying to lay out tables, instead lay out things about which you need to keep data and the data you need to keep. In logical data modeling, such things are called "Entities" (which typically become a table(s) in the physical model).
Possible Entities and Attributes (become column(s)) (Keys marked with *)
Entity = Customer Attributes = Customer Id*; Customer Name; main phone; main address
Entity = Customer Contacts Attributes = Customer Id*; Contact Id*; Contact Name; Contact Phone; ...
Entity = Questions Attributes = Question Id*; Question; Status; Create Date; Update Date; Delete Date;
and so on. Spend some time to get all the attributes ("columns") you can possibly identity.
Then go thru the normalization process on every Entity, step by step. Normalization is a process, you can't just skip to the end. Especially with no experience in modeling and table design. Adjust to 1NF, then to 2NF and finally to 3NF.
Only after you've done normalization down to 3NF, then convert it to a physical design, that is tables.
An identifier, such as a customer id (identifier) can exist within a logical model. It's just an abstract unique number. An "identity" cannot exist in a logical model, because identity is a physical implementation, not a logical construct.
When doing the physical model, NEVER, EVER blindly use an identity as the "key" to all tables. That's a horrible thing and it creates serious data and performance issues. Slapping an identity on every table is the single most damaging myth in db creation. Just slapping an identity onto every table is not a "design".
For example, intersection tables, that contain data for the match of other entities, such as Customer_Feedback, would use the parent keys as the main keys; you can add an identity as the last key if you need it for uniqueness.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply