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

I would suggest you always take a normalized approach to your database schema and then later decided if you need to create a solution for performance reasons. Premature optimization can be dangerous. Premature database de-normalization can be disastrous!

I would suggest that you stick with the original schema and later, if necessary, create a reporting table that is a de-normalized version of your normalized schema.

Answer 2

One change that may or may not help simplify things would be to not link the ResponseAnswers back to the SurveyID. Rather, create an ID per response and per question and let your ResponseAnswers table contain the fields ResponseID, QuestionID, Answer. Although this would require keeping unique Identifiers for each unit it would help keep things a little bit more normalized. The response answers do no need to associate with the survey they were answering just the specific question they are answering and the response information that they are associated.

Answer 3

I created a customer surveys system at my previous job and came up with a schema very similar to what you have. It was used to send out surveys (on paper) and tabulate the responses.

A couple of minor differences:

• Surveys were NOT anonymous, and this was made very clear in the printed forms. It also meant that the demographic data in your example was known in advance.

• There was a pool of questions which were attached to the surveys, so one question could be used on multiple surveys and analyzed independently of the survey it appeared on.

• Handling different types of questions got interesting -- we had a 1-3 scale (e.g., Worse/Same/Better), 1-5 scale (Very Bad, Bad, OK, Good, Very Good), Yes/No, and Comments.

  There was special code to handle the comments, but the other question types were handled generically by having a table of question types and another table of valid answers for each type.

To make querying easier you could probably create a function to return the response based on a survey ID and question ID.

Answer 4

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