Normalizing a database schema

Question

I am trying to design a database schema that accurately represents a survey, with all its questions and responses. Here is the requirements for the database:

The DB itself needs to accommodate various surveys (each may have unique questions or may be the same). Each question can either have responses that are open ended or selected from a list. Those selected from a list can have 0, one, or more responses or they may only allow one response. The problem I am having in properly normalizing the question, response, and possible choices of responses per question. This is what I have so far:

The way this is designed is that the Survey table would hold multiple surveys. Each survey can have multiple question. Each survey can also be filled out by multiple respondents (or the same). Some questions can have 0, 1, or more pre-defined responses (think multiple choice for example). These responses may also not have any multiple choice selections but, instead might be open ended responses. So, the table "Possible_Responses" might look like this:

      Name                   QuestionID                Example Question

      Red                      1                        Favorite Color
      Blue                     1                        Favorite Color
      Green                    1                        Favorite Color
      Pizza                    2                        Favorite Food 
      Apple                    2                        Favorite Food
      Steak                    2                        Favorite Food

Then, the Answers table might look like this:

        ID            Response                QuestionID       Example Question

        1               1                       1                Favorite Color
        2               1                       2                Favorite Food
        3               "Subjective Respones"   3                Some subjective quest.

I could then trace the question back to the Questions table to determine if the response should be open ended or closed.

Although this might work, It doesn't feel properly normalized to me. Does anyone have any suggestions on how to best normalize this schema?

Answer 1

As philipxy commented, describe the possible problems or concerns you may have.

You defined one relationship for which you've not yet drawn a line (do so).

Tables should be named in singular. You’ve mixed some singular, some plural.

Indeed, you could do something different with answers. You may not be representing when a question is allowed to have an open-ended response and when not. In other words, the open-ended response is a possible response, so it should be represented as a "Possible_Response". You could represent it by either (a) having a Boolean column "open_ended_" on the "Possible_Response" table, or (b) have a row in that table with a special value such as "open-ended". In either case, the "Answer" table could be a child of the "Possible_Response" table. If "Answer" were a child of "Possible_Response", the Answer.Response column would only be used when it belongs to a "Possible_Response" marked as open-ended. So the Answer.Response column should have its name changed from "Response" to "open_ended_response_".

"Name" is probably not the best name for that column in "Possible_Response".

The word "Answer" does not end in 'e'.

What do you mean by 1st sentence of 2nd paragraph? Do you mean variations of a survey may be given to different participants? If so, there should be a "variation" table.

Usually I find it helpful to draw child tables lower on the page (where feasible).

Tips on naming: (a) For maximum portability across SQL database implementations, use all lowercase in the names (along with separation by underscores as you did). (b) The SQL spec promises to never use a trailing underscore. So adding a trailing underscore will prevent any naming collision with key/reserved words. That means "possible_response_" rather than "Possible_Responses".