Re: Schema Design for Surveys
- From: Jared Still <jkstill@xxxxxxxxx>
- To: niall.litchfield@xxxxxxxxx
- Date: Mon, 10 Jan 2005 10:42:17 -0800
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@xxxxxxxxx> 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
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
