Re: Need help with twisty design problem

From: Todd Benson <toddb_at_spectralogic.com>
Date: 13 May 2002 16:10:57 -0700
Message-ID: <97b61782.0205131510.45a3b6ec_at_posting.google.com>

aneely_at_softouch.on.ca (Amer Neely) wrote in message news:<331f54ba.0205112233.5566d12a_at_posting.google.com>...

> 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?

A person is a person whether they fall into the 'something else' category for your second survey or not.

Why not do something like this:

create table person (
  userid int primary key,
  lastname varchar(50) not null,
  firstname varchar(50) not null,
  email varchar(50) not null,

  phone_ac char(3) not null,
  phone_num char(8) not null,
  phone_ext char(10),

  KeyDM,
  referral int foreign key references person(userid) )

referral simply points to another person in the table.

Where you will run into problems with this, though, is if a person can refer more than one other person. In that case you could have an auxiliary table called referral:

create table referral (
  user int not null foreign key references person(userid),   suggestion int not null foreign key references person(userid),   primary key (user,suggestion)
)

Todd Received on Tue May 14 2002 - 01:10:57 CEST