Should relationship be forced or implied?

[Tim Cali]

Hi. I am revising a survey database and I would like to know if I should
create a formal relationship between 2 items in it.

The first table is simple:

tblQuestions
QuestionID
Question

In the database, certain questions are open-ended and others are
pick-from-list only. So for example a pick-from-list question "do you like
to surf?" might have responses:

- yes
- no

while another pick-from-list question would have other choices. I am
wondering how to represent this in the database. Duane Hookum's
AtYourSurvey.mdb allows QuestionID to be assigned at the response level,
something along the lines:

tblResponses
RspnsID
QstnID

with QstnID a foreign key to QuestionID of tblQuestions. I like this because
there is a solid relationship between the tables. However, in my survey
there are several questions that have yes/no choices, so I would have to
replicate as many yes/no responses for as many questions that allow them,
negating my ability to "re-use" the pick-from-list.

So, my question is, will setting up an unattached table violate the
commonly-accepted rules of database design? Or, is this an acceptable
workaround for my situation? Here is what I mean.

I could design it this like:

tblQuestions
QuestionID
Question
ItemRef

tblResponses
ResponseID
Response
ItemRef

e.g.
tblQuestions
QuestionID Question ItemRef
1 do you eat broccoli? 2
2 do you like to surf? 2
3 what is you fav color? 3

tblResponses
ResponseID Response ItemRef
1 Yes 2
2 No 2
3 Red 3
4 Blue 3
5 Black 3
6 Green 3
'etc

This allows me to reuse the lists as many times as I want. But the problem
is that the relationship is implied, not forced.

The ultimate solution that I can think of would be to set up a many-to-many
relationship between tblQuestions and tblResponses, which would allow me to
reuse the items in the responses. However, I would have to create some much
more comples forms to handle this and I am trying to find an appropriate
trade-off between amount of work to be done and the payoff of the fruits of
my labor.

To restate the question, do any of you set up database with some tables
"detached" and, therefore, a relationship implied, or is it preferred to
always set up proper relationships and avoid a "quick fix" such as this?
What would any of you do in this case?

 

[Tim Ferguson]

In the database, certain questions are open-ended and others are
pick-from-list only. So for example a pick-from-list question "do you
like to surf?" might have responses:

- yes
- no

while another pick-from-list question would have other choices.

I think this might be the same answer as Tina's, but here goes:

I make a table to contain all the valid answers:

ValidAnswers
============
QuestionNumber FK references Questions.QNumber
Code Text(1)
English Text(48)

contraint PK (QuestionNumber, Code)

which would look like this:

QNumber Code English
32 y Yes
32 n No
33 1 Single
33 2 Married
33 3 Separated
33 4 Divorced
33 5 Widowed
34 e English
34 s Spanish
34 f French
35 0 Less than 3
35 1 3 to 5
35 2 5 to 15
35 3 15 or more
35 9 Unknown


etc etc. You can relate this table to the RespondentsAnswers table, so that
all responses are forced to belong to the correct set, although obviously
the UI would be the first line of defence against invalid answers. Talking
of the UI, if you use combo boxes or lists for the answer codes, you can
get the RowSource from this table for each question, so that is a lot
simpler than hand coding all those controls.

Hope that helps


Tim F