Advice Needed To Normalise Database

Question

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

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