For some discussion of schemas for surveys, check out:
http://dba.stackexchange.com/q/16002/2264 And this three part series from Vertabelo:
https://www.vertabelo.com/blog/technical-articles/a-database-model-for-an-online-survey-part-1 http://www.vertabelo.com/blog/technical-articles/a-database-model-for-an-online-survey-part-2 http://www.vertabelo.com/blog/technical-articles/a-database-model-for-an-online-survey-part-3 I don't think these address your key question though, which I read as:
"How do I ensure that we store answers in the correct data type?"
If so, your proposal of:
create table answers (
answer_id int not null primary key,
number_answer number,
varchar2_answer varchar2(4000),
clob_answer clob,
blob_answer blob
);
With a check constraint to ensure only one is set could work. You'll also want to ensure that only the correct data type is not null. Do this by:
- adding a answer_type column stating this
- creating a check constraint inspecting this value and checking the nullness as needed:
( answer_type = 'NUMBER' and number_answer is not null and varchar2_answer is null and ... ) or
( answer_type = 'VARCHAR2' and number_answer is null and varchar2_answer is not null and ... ) or
Personally I prefer a different method. Having a single table per data type (click for pic):
https://blogs.oracle.com/sql/resource/at-survey-answers-schema.png This is a bit more work to start with. And there's greater overhead (two inserts/answer, more (outer) joins to get the results).
But it avoids you having to deal with nulls. And conditionally selecting the answer column based on the type. For example, instead of:
select case answer_type
when 'VARCHAR2' then varchar2_answer
when 'NUMBER' then to_number(number_answer)
when 'CLOB' then clob_answer
...
from answers;
You just union the results together:
select answer
from varchar2_answers
union all
select answer
from number_answers
union all
select answer
from ...
Though you could unpivot the single table for similar results. It also makes it easier if you want to process certain types of answer. For example, to find all the blob/file uploads you just run:
select answer
from blob_answers;
For your "lists" table, are you referring to a single table to store all lookup values? For example, countries, currencies, etc.?
If so, this is a version of the One True Lookup Table (OTLT). I'm not keen on this because:
- You repeat the data type dilemma above
- It's hard/impossible to add further constraints on the lookup values
- To ensure answers are only from the correct list you need to add type columns
- The optimizer will find it harder to get the best plan when joining a few rows from a huge table (OTLT) vs. a table where these are the only rows you want
The big advantage comes when you add a new list. You can just insert them, instead of creating new tables and FKs. As you're giving this to customers to build their own questions it means you don't need to issue DDL in your application.
It also avoids the "polymorphic assocation" problem with FKs from answers to the lists. With a table/lookup set, the answer FK will point to different tables. You can't do this in a single column:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9532036700346467461 So weigh up the above and decide which downsides you're most willing to accept!