当用户可以定义字段时的模式设计 [英] Schema design for when users can define fields

问题描述

问候堆垛机

我想为应用程序提供最好的数据库模式，让用户创建调查并向公众公布。有大量的标准人口统计字段，大多数调查（但不是全部）将包括，如名字，姓氏等。当然，用户可以创建无限数量的自定义问题。

I'm trying to come up with the best database schema for an application that lets users create surveys and present them to the public. There are a bunch of "standard" demographic fields that most surveys (but not all) will include, like First Name, Last Name, etc. And of course users can create an unlimited number of "custom" questions.

我想到的第一件事是这样的：

The first thing I thought of is something like this:

Survey
  ID
  SurveyName

SurveyQuestions
  SurveyID
  Question

Responses
  SurveyID
  SubmitTime

ResponseAnswers
  SurveyID
  Question
  Answer

吸吮每次我想查询数据。而且似乎危险地接近内部平台效果

But that's going to suck every time I want to query data out. And it seems dangerously close to Inner Platform Effect

改进之处是在响应表中包含尽可能多的字段：

An improvement would be to include as many fields as I can think of in advance in the responses table:

Responses
  SurveyID
  SubmitTime
  FirstName
  LastName
  Birthdate
  [...]

然后，至少查询来自这些常用列的数据是直接的，我可以查询，例如，每个谁回答了他们的出生日期的调查的平均年龄。

Then at least queries for data from these common columns is straightforward, and I can query, say, the average age of everyone who ever answered any survey where they gave their birthdate.

但似乎这会使代码复杂一点。现在看看调查中提出了哪些问题，我必须检查哪些公共响应字段被启用（使用，我猜测，一个位字段在调查）和什么在SurveyQuestions表。我必须担心特殊情况，如果有人试图创建一个自定义问题，在响应表中重复常见问题。

But it seems like this will complicate the code a bit. Now to see which questions are asked in a survey I have to check which common response fields are enabled (using, I guess, a bitfield in Survey) AND what's in the SurveyQuestions table. And I have to worry about special cases, like if someone tries to create a "custom" question that duplicates a "common" question in the Responses table.

最好我能做到吗？我缺少一些东西？

Is this the best I can do? Am I missing something?

推荐答案

你的第一个模式是两个更好的选择。在这一点上，你不应该担心性能问题。担心做出一个好的，灵活的，可扩展的设计。有各种各样的技巧，你可以做以后缓存数据和查询更快。

Your first schema is the better choice of the two. At this point, you shouldn't worry about performance problems. Worry about making a good, flexible, extensible design. There are all sorts of tricks you can do later to cache data and make queries faster. Using a less flexible database schema in order to solve a performance problem that may not even materialize is a bad decision.

此外，许多（也许大多数）调查结果只能查看定期和少数人（事件组织者，管理员等），所以你不会不断地查询数据库的所有结果。即使你是，性能会很好。你可能会以某种方式分页结果。

Besides, many (perhaps most) survey results are only viewed periodically and by a small number of people (event organizers, administrators, etc.), so you won't constantly be querying the database for all of the results. And even if you were, the performance will be fine. You would probably paginate the results somehow anyway.

第一个模式更灵活。默认情况下，您可以包括名称和地址等问题，但对于匿名调查，您可以不创建它们。如果调查创建者只想查看每个人对五百个问题中的三个问题的答案，这是一个非常简单的SQL查询。您可以设置级联删除，以在删除调查时自动删除回复和问题。

The first schema is much more flexible. You can, by default, include questions like name and address, but for anonymous surveys, you could simply not create them. If the survey creator wants to only view everyone's answers to three questions out of five hundred, that's a really simple SQL query. You could set up a cascading delete to automatically deleting responses and questions when a survey is deleted. Generating statistics will be much easier with this schema too.

以下是您提供的模式的略微修改版本。我假设你可以找出什么数据类型去哪里： - ）

Here is a slightly modified version of the schema you provided. I assume you can figure out what data types go where :-)

    surveys
      survey_id (index)
      title

    questions
      question_id (index, auto increment)
      survey_id (link to surveys->survey_id)
      question

    responses
      response_id (index, auto increment)
      survey_id (link to surveys->survey_id)
      submit_time

    answers
      answer_id (index, auto increment)
      question_id (link to questions-question_id)
      answer

这篇关于当用户可以定义字段时的模式设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！