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
