Need help with twisty design problem

From: Amer Neely <>
Date: 11 May 2002 23:33:34 -0700
Message-ID: <>

I'm having trouble with a design for storing results from an online survey. Fairly new at RDBMS. I'm slowly going through "Database design for mere mortals" by Michael J. Hernandez and "Data & Databases: Concepts in practice" by Joe Celko. Two excellent books.

Anyway ...
There are 2 separate surveys.
Results from survey 1 are captured and stored.

If the respondent meets specific criteria, as indicated by a response to a question, they are also deemed to be "something else" (forgive my shadiness, this is a proprietary project) and invited back for survey 2.

If they do NOT meet the criteria, they are asked to provide the name of someone who does meet the criteria.

The dilemma is that I need to store the name of both the respondent AND the name of the person they submit. But, the respondent may also be this other person IF they meet the criteria. Is this making sense?

My problem is I'm not sure how / where to store the information without duplicating it.

My (abbreviated) model so far:

----------------------8X cut here ---------------


|Field |
|UserID |
|UserLastName |
|UserFirstName |
|UserEmail |
|UserPhoneAC |
|UserPhoneNum |
|UserPhoneExt |
|KeyDM |
|DMLastName |
|DMFirstName |
|DMPhoneAC |
|DMPhoneNum |
|DMPhoneExt |
|DMEmail |

----------------------8X cut here ---------------

The field 'KeyDM' is the determining criteria - it returns either "Y" or "N".

The fields beginning with 'User...' are the respondent data. The fields beginning 'DM...' are what the respondent submits as the person meeting the criteria in 'KeyDM'. However, if the respondent is also the person meeting the criteria, I don't want to populate the 'DM..' fields with the same data. I also don't want to save it to a different table.

I've thought of using a table containing only the 'UserID' of respondents who also meet the criteria.

My brain is going numb. Anyone shed some light on this for me? Received on Sun May 12 2002 - 08:33:34 CEST

Original text of this message