Re: Need help with twisty design problem

Todd Benson wrote:
>
> 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

Hmmm. Another good suggestion. I'm not feeling so bad now - was worried about duplication, but Joe's reply set me straight on that. And yours gave me a new perspective on the data. Both are similar in suggesting a single table for all respondents. Thanks for the reply.

-- 
/* All outgoing email scanned by Norton Antivirus 2002 */
Amer Neely, Softouch Information Services
W: www.softouch.on.ca
E: aneely_at_softouch.on.ca
V: 519.438.5887
Perl | PHP | MySQL | CGI programming for shopping carts, data entry
forms.
"We make web sites work!"