Help needed with design problem (second posting)

From: Amer Neely <aneely_at_softouch.on.ca>
Date: Sun, 12 May 2002 19:19:49 -0400
Message-ID: <3CDEF895.14CADE51_at_softouch.on.ca>



My apologies if this has appeared twice in someone's reader. Mine tells me the original posting has expired, yet it was posted May 11.

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?

-- 
/* All outgoing email scanned by Norton Antivirus 2002 */
Amer Neely, Softouch Information Services
W: www.softouch.on.ca
E: aneely_at_softouch dot on dot ca
Received on Mon May 13 2002 - 01:19:49 CEST

Original text of this message