数据库设计与多对多关系有限制 [英] Database design for many-to-many relations with restrictions

问题描述

我有一个数据库与用户和一个有问题。我想要的是确保每个用户只能回答每个问题一次。

我想到了一个数据库，所有的问题id都是列，所有的用户标识都是记录，但是当问题和用户数量增长时，这变得非常大（并且我猜测慢）。

有更好的表现吗？

解决方案

您可能需要这样的设置。

 问题表（QuestionID主键，QuestionText）
用户表（UserID主键，用户名）
答案表（QuestionID，UserID，Date） - 加上AnswerText / Score / Etc如所须。 
  

在 Answers 表中，一起形成复合主键（QuestionID，UserID），并且都是的外键问题（QuestionID）和用户（UserID）

复合主键确保 QuestionID / UserID 只允许一次。如果您希望允许用户多次回答相同的问题，您可以扩展¨复合主键以包含日期（它将是一个复合键）。

这是一个规范化的设计，应该足够有效。通常使用代理主键（如 AnswerID ）而不是复合键，并使用唯一的约束来确保唯一性 - 使用代理键通常是易于使用的动力，但它是没有意味着必要。

I have one database with users and one with questions. What I want is to ensure that every user can answer every question only once.

I thought of a database that has all the question id's as columns and all the user id's as records, but this gets very big (and slow I guess) when the questions and the user count grow.

Is there another way to do this with better performance?

解决方案

You probably want a setup like this.

Questions table (QuestionID Primary Key, QuestionText)
Users table (UserID Primary Key, Username)
Answers table (QuestionID, UserID, Date) -- plus AnswerText/Score/Etc as needed.

In the Answers table the two first columns together form a compound primary key (QuestionID, UserID) and both are foreign keys to Question(QuestionID) and Users(UserID) respectively.

The compound primary key ensures that each combination of QuestionID/UserID is only allowed once. If you want to allow users to answer the same question multiple times you could extend the ¨compound primary key to include the date (it would then be a composite key).

This is a normalized design and should be efficient enough. It's common to use a surrogate primary key (like AnswerID) instead of the compound key and use a unique constraint instead to ensure uniqueness - the use of a surrogate key is often motivated by ease of use, but it's by no means necessary.

这篇关于数据库设计与多对多关系有限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！