规范化数据库所需的建议
我正在尝试为 ASP.net 中的反馈应用程序创建数据库，我有以下数据库设计。
Username (PK)
QuestionNo (PK)
QuestionText
FeedbackNo (PK)
Username
UserFeedbackNo (PK)
FeedbackNo (FK)
QuestionNo (FK)
Answer
Comment
用户有唯一的用户名 用户可以有多个反馈
我想知道我这里的数据库设计是否标准化并且适合应用程序
编辑 - 反馈有多个问题，因此会有多个反馈答案。希望这有意义
编辑 - 我在反馈表中有 20 个问题，每个问题都可以使用单选按钮（因此是“答案”字段）来回答，并且可以向每个问题添加可选注释。用户可以根据需要多次填写此反馈表。这就是为什么我有一个包含 FeedbackNo 和用户名的链接表。
编辑
**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
阅读评论后...我会重组我的设计吗？这个新设计适合我的需求吗？
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 ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
你那里有一张无关的桌子。反馈和用户之间似乎存在多对多关系。然而，反馈仅涉及一位用户。基数应为：
您的结构应如下所示：
用户表
用户名 (PK)
问题表
ID (PK)
QuestionText
反馈表
ID (PK)
用户名 (FK)
问题ID(FK)
回答
评论
随着 c11ada 提供的更新，设计保持不变。在您的情况下，我可能会做的唯一区别是我会将答案的日期和时间存储在反馈表中。
另一种方法是创建另一个表，即调查问卷表，该表将记录用户填写的反馈实例。
调查表
ID (PK)
用户名 (FK)
日期
反馈表
ID (PK)
调查问卷 ID (FK)
问题ID(FK)
回答
评论
这是假设调查问卷与其他用户无关。在这种情况下，它看起来像：
问卷表
ID (PK)
关于用户 (FK)
应答用户 (FK)
日期
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:
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
我会考虑这样的事情：
我会考虑在每个表上放置 LastChgDate 和 LastChgID FK 列，甚至可能是 CreateDate 和 CreateUserID。您不需要在任何这些表中使用特殊列来重新创建插入顺序，自动编号/标识值（虽然并不总是连续的，但是增量的）可以用于此目的。
我会避免 GUID 和字符串键（如用户名），因为它们会使每个索引占用更多内存。我会使用代理键代替用户名，因为它可能会发生变化（离婚/婚姻/等）。
反馈表有点麻烦，是用来回答的还是用来评论的？可能应该是两个表，或者至少有一个 FeedBackType 列和一个文本列。 OP 没有提供足够的信息来完全回答这个问题。即使在OP编辑之后，我也不确定我是否理解：
一个反馈有多个问题，所以会有多个反馈答案I'd go with something like:
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首先，将用户输入的值作为主键通常是一个不好的做法。例如，如果您必须更改用户名会发生什么？
我个人会采取类似的做法：
此外，答案和评论是否彼此独立？您可以考虑拥有一个答案表和一个评论表。
编辑：FeedbackDate 用于订购目的。与保留订单索引相比，它是一个自然的排序器。
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:
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.
根据您的编辑，我认为您的设计是正确的 - 反馈表代表用户的问题/答案的集合，其中最后一个表定义了给出的各个答案。我不会在最后一个表的名称/PK 中包含“用户”一词，因为它是定义用户的反馈表。称其为“FeedbackAnswer”之类的名称。
此外，您还混合了代理键和自然键（用户名作为键与 FeedbackNo 作为键）。这是一个关于哪种更好的争论问题，但我相信更多的人会同意你应该坚持一种方法或另一种方法，而不是混合使用它们（如果可能的话）。
最后，如果用户要从可能的响应列表中选择答案，请考虑使用 QuestionAnswer 表来定义每个问题的响应，然后该表将与 FeedbackAnswer 表相关并更好地标准化响应数据。
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.
听起来好像已经常态化了。问题与反馈是1-1吗？如果是这样，请确保您对包含 FeedbackNo 和 QuestionNo 的 UserFeedback 表具有唯一约束。
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.