Re: Data design question - newbie

rob_at_benefitscheckup.org wrote:
> Hi Stu,
>
> Thanks for the response!
>
> I am sorry that my question wasn't clear about the types of responses
> possible. I was trying to show that depending on the answerfield, the
> expected resonse could be a number, a boolean, a string or specific value
> from a set.
>
> I want to figure out a way to insure integrity so that I don't have a
> situation where I get the Answer: '82' to the question 'Are you male or
> female?'. Aside from having a separate table per answer I am not sure how to
> do this....
>
> Any thoughts?

There's two obvious ways you could do this in one table and enforce integrity:

1. Have more than one response field:

e.g.
answer_string, answer_boolean and answer_num

Then create a constraint like this:

alter table foo add constraint c_foo_c1 check (

(answer_type = 'boolean' and answer_boolean is not null and answer_string is null and answer_num is null) or
(answer_type = 'num' and answer_num is not null and answer_string is null and answer_boolean is null)
or
(answer_type = 'string' and answer_string is not null and answer_boolean is null and answer_num is null)
);

2) Stick with one string response field and have a constraint like:

(answer_type = 'boolean' and IsBool(answer)) or
(answer_type = 'num' and IsNum(answer))
or
(answer_type = 'string')

where IsBool and IsNum are functions you have created to check whether a string is a valid boolean value and number respectively.

Regards,
Kieran Elby Received on Mon Aug 05 2002 - 17:11:33 CEST