Hello,



A bit embarrassed, I have been away from the databasing side of system
development for quite a while so am a little rusty. This seems like a simple
issue.



I have a USER table:



userID

fName

lName

address

address2

city

province

country

referrerID



userID is the unique primary key.



My question pertains to referrerID. The referrerID specifies which OTHER
USER referred this new user. Thus it is a foreign key that links to a userID
in another user record. I do not recall ever linking attributes from the
same table (even for different records) so I have a hunch that the way to do
this properly is either:



(A)



User UserReferrer

--------- -------------------

userID (the new userID)-------userID

referrerID (the userID of
the referrer)



or (B)

just store referrerID as a number and use a simple query to get the info for
the referrer when it is required.



Select name from USER

Where userID=currentUsersID





Which is the correct way of doing this? If both, which would be ideal, this
will be a relatively large database with approx 250,000+ users, not all will
have referrerIDs, I would wager maybe 1-10% of these people will have been
referred. Priorities for us are simplicity/scalability, efficiency (in that
order)



Ben A. Hilleli

Programmer / Analyst