Schema design for when users can define fields

Question

Greetings stackers,

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.

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?

Answer 1

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.

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