Re: Schema Design for Surveys

From: Jared Still <jkstill_at_gmail.com>
Date: Mon, 10 Jan 2005 10:42:17 -0800
Message-ID: <bf46380501101042525837d3@mail.gmail.com>

Hi Niall,

That's a simple question, but not one with a simple answer as you have seen.

As someone that rather enjoys data modeling, I would tend to start with a model of the data, and then determine how to implement it in the database.

I might first come up with something like this: ( these will appear better if you change to fixed font)

              | Question   |
              |              |
              ----------------


               |     |    |

               |     |    |

              \______________/


               |     |    |

               |     |    |

               |     |    |

     ----------|     |    |--------
     |               |             |
     |               |             |
    /|\             /|\           /|\

------------- ------------- ------------ | Multiple | | Numeric | | Text | | Choice | | | | | ------------- ------------- ------------

This is a model with a different entity for each type of answer. Though not seen from the model, each question could have either a single numeric or text answer, or 1 or more multiple choice answers.

The association from multiple choice -> question

Further reflection migth yield this:

     | Question     |
     |              |
     ----------------
           |
           |
           |
          /|\
------------------------
|                      |
|     Answer           |
|                      |
|    -------------     |
|    |  Multiple |     |
|    |  Choice   |     |
|    -------------     |
|                      |
|    -------------     |
|    |  Numeric  |     |
|    |           |     |
|    -------------     |
|                      |
|    ------------      |
|    |  Text    |      |
|    |          |      |
|    ------------      |
|                      |
|                      |
-------------------------

Here a supertype of Answer is probably a little more clear than the previous model, thought it depicts the same thing.

There are several methods of implementing this model: here are those that I can think of at the moment.

( This discussion has omitted other obviously needed data to keep this example simple. It would be difficult to include the survey and respondent entities/tables and relations in an ascii character model )

Implement a single ANSWERS Table

columns:
mult_choice varray
numeric number
text varchar2

A check constraint to ensure that only one is used.

This might be a good place to use a cluster table.

Implement separate tables

MULTCHOICE_ANSWERS table
There will be 1+N rows per question

TEXT_ANSWERS table
one row per question

NUMERIC_ANSWERS table
one row per question.

ANSWERS view
Create a view that would return the answers regardless of which table it is in. Easy enought to construct, but may take a little thought to deal with the differing data types. Or not: just return all as text.

An associative table will need to be created to associate a question to an answer. Though I haven't drawn this out, it would be necessary so that an answer may be associated to a respondent, a question and a survey.

One method to enforce that only one type of answer appears per question would be to create an FK relation from the QUESTIONS associationt table to each of the individual answer tables, and use a check constraint on the three columns to ensure that only one is populated.

This is effective, but somewhat awkward when querying. I only mention this because I did it once, though I can't recall the exact circumstances that made it useful.

A better method would be to use a single sequence to create the PK for all of the answer tables, and of course single column in the QUESTIONS association table to link the answer(s) to the question.

Getting the correct answers table would be resolved by the ANSWERS view.

My preference: Multiple anwer tables, with PK generated from a single sequence, and using an ANSWERS view.

More complex to create, easier to query.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


On Mon, 10 Jan 2005 17:40:56 +0000, Niall Litchfield
<niall.litchfield_at_gmail.com> wrote:
> We have a requirement for a system that will record answers to more
> than one survey. Each survey will consist of a number of questions
> with responses that can be
> 
> multiple choice (eg pick up to 3 preferences from 8)
> text
> numeric - single answer
> 
> How would people go
> 
> one question table, one wide response table
> one question table, multiple response tables varying on type of response
> some other construct.
> 
> This is an internal discussion that we are having here that interests
> me (and I do have a preference) and I'd be interested to see what folk
> think.
> --
> Niall Litchfield
> Oracle DBA
> http://www.niall.litchfield.dial.pipex.com
> --
> http://www.freelists.org/webpage/oracle-l
>
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 10 2005 - 12:43:47 CST