Advice Needed To Normalise Database

Question

hey all,

im trying to create a database for a feedback application in ASP.net i have the following database design.

Username (PK)

QuestionNo (PK)
QuestionText

FeedbackNo (PK)
Username

UserFeedbackNo (PK)
FeedbackNo (FK)
QuestionNo (FK)
Answer
Comment

a user has a unique username a user can have multiple feedbacks

i was wondering if the database design i have here is normalised and suitable for the application

EDIT - a feedback has multiple questions, so there will be more than one feedback answer. hope this makes sense

EDIT - i have 20 questions in the feedback form, each question can be answered by using a radio button (hence the Answer field), and optional comments can be added to each question. a user can fill out this feedback form as many times as they want. that's why i have the link table which has feedbackNo and username.

EDIT

**Users Table**
UserID (PK) autonumber
Username

**Question Table**
QuestionID (PK) autonumber
QuestionNumber
QuestionText

**Questionnaire Table**
QuestionnaireID  (PK) autonumber
UserID (FK) `User Table`
Date

**Feedback Table**
ID (PK) autonumber
QuestionnaireID (FK) `Questionnaire Table`
QuestionID (FK) `Questions Table`
Answer
Comment

after reading the comments ... would i have restructured my design, will this new design be suitable for my needs ?

Answer 1

It looks fine to me.

Answer 2

You have an extraneous table in there. Looks like you have a many-to-many relationship between feedbacks and users. However, feedbacks only pertain to one user. The cardinality should be:

• One user per feedback
• One question per feedback

Your structure should look like:

User table

Username (PK)

Question table

Id (PK)
QuestionText

Feedback table

Id (PK)
UserName (FK)
QuestionId(FK)
Answer
Comment

---

With the updates c11ada provided, the design stands. The only difference I might make in your case is that I'd store the date and time of the answer in the feedback table.

An alternative would be to create another table, the Questionnaire table, which would record an instance of feedback filled out by a user.

Questionnaire table

Id (PK)
UserName (FK)
Date

Feedback table

Id (PK)
QuestionnaireId (FK)
QuestionId(FK)
Answer
Comment

This is assuming the questionnaire isn't about another user. In which case it would look like:

Questionnaire table

Id (PK)
AboutUser (FK)
AnsweringUser (FK)
Date

Answer 3

It sounds like it is normalized. Is Question to Feedback 1-1? If so, make sure you have a unique constraint on the UserFeedback table that includes FeedbackNo and QuestionNo.

Answer 4

First off, it's usually a bad practice to have a user inputted value as a primary key. For instance, what would happen if you have to change the Username?

I personally would go with something akin to this:

User_ID (PK) (GUID)
UserName

Question_ID (PK) (GUID)
QuestionNumber
Question

Feedback_ID (PK) (GUID)
Question_ID (FK)
User_ID (FK)
FeedbackDate
FeedbackText

Furthermore, are the answers and comments independent of each other? You might consider having an answers table and a comments table.

EDIT: The FeedbackDate is for ordering purposes. It's a natural sorter compared to keeping an Order Index.

Answer 5

I'd go with something like:

Users
UserID         int     primary key auto number/identity
UserName       

Questions
QuestionID     int    primary key auto number/identity
QuestionNumber
QuestionText   

Feedback
FeedbackID     int    primary key auto number/identity
QuestionID     int    fk 
UserID         int    fk
Answer
Comment

I'd consider putting a LastChgDate and LastChgID FK column on each table, possibly even CreateDate and CreateUserID. You will not need a special column in any of these tables to recreate the insert order, the auto number/identity values (while not always continuous are incremental) work for that.

I would avoid GUID and string keys (like Username) since they will make each index take up more memory. I'd use a surrogate key in place of Username because it is subject to change (divorce/marriage/etc).

The Feedback table is a little troubling, is it for answers or comments? possibly should be two tables, or at least have a FeedBackType column and a single text column. OP doesn't give enough info to fully answer this issue. Even after the OP's edit, I'm not sure I understand: a feedback has multiple questions, so there will be more than one feedback answer

Answer 6

Based on your edits, I think your design is correct--the feedback table represents the collection of questions/answers for a user, where the last table defines the individual answers given. I would not include the word "User" in the last table's name/PK, as it's the Feedback table that defines user. Call it something like "FeedbackAnswer".

Also, you are mixing surrogate keys and natural keys (Username as a key vs. FeedbackNo as a key). This is a matter of debate as to which is better, but I'm sure more people would agree that you should stick to one approach or the other and not mix them (if possible).

Finally, if the user is to select answers from a list of possible responses, consider having an QuestionAnswer table that defines the responses for wach question, which would then relate to the FeedbackAnswer table and better normalize response data as well.