SQL数据库（Django） - 将表B中的所有记录与表A中的每个记录相关联

I'm working in Django, but this question could apply to any database in general.

我在Django工作，但这个问题一般适用于任何数据库。

In my models, I have a UserProfile table. Another table UserQA contains a field for question_text and a field for question_answer. The problem with this model is that I would need a ManyToMany relation that has the overhead of two longints per question, and the question_text is duplicated for each user.

在我的模型中，我有一个UserProfile表。另一个表UserQA包含question_text的字段和question_answer的字段。这个模型的问题是我需要一个ManyToMany关系，每个问题有两个longint的开销，并且每个用户都会复制question_text。

Basically, the requirements are:

基本上，要求是：

Table of questions which users can contribute to by adding their own questions
用户可以通过添加自己的问题来贡献的问题表
Store 1 character answer (Y/N/O/Null) to each question for every user
为每个用户存储1个字符答案（Y / N / O / Null）到每个问题

2.1 Is there a way to extend this if I want some questions to have more complex answers?

2.1如果我想要一些问题来获得更复杂的答案，有没有办法扩展这个？

This seemed like an easy problem, but I can't figure it out... The way I thought of doing it seems very inefficient, is there a better way?

这似乎是一个简单的问题，但我无法弄清楚......我想做的方式看起来非常低效，有更好的方法吗？

Thanks!

谢谢！

1 个解决方案

#1

You will need another model, can be Question. The final result would be something like:

你需要另一个模型，可以是问题。最终结果如下：

class User(models.Model):
    user_name = models.CharField(...)

class Question(models.Model):
    question_text = models.CharField(...)

class UserAnswer(models.Model):
    question = models.ForeignKey(Question)
    user = models.ForeignKey(User)
    answer = models.CharField(...)

If you want more complicated answers, like especific values, lists of values, you can create one more model:

如果您想要更复杂的答案，例如特定值，值列表，您可以再创建一个模型：

class QuestionAlternative(models.Model):
    question = models.ForeignKey(Question)
    value = models.CharField(...)

And then redefine UserAnswer:

然后重新定义UserAnswer：

class UserAnswer(models.Model):
    question = models.ForeignKey(Question)
    user = models.ForeignKey(User)
    answer = models.ForeignKey(QuestionAlternative)

With this, you will have the Questions in one place, one UserAnswer per question, and the QuestionAlternatives how many times they must exist. Does not worry about the ForeignKey fields, they are not overheads and they build beautiful, reusable structures.

有了这个，您将在一个地方提出问题，每个问题一个UserAnswer，以及QuestionAlternatives它们必须存在多少次。不用担心ForeignKey字段，它们不是开销，而是构建漂亮，可重用的结构。

#1