Does this structure look correct?

I'm trying to come up with a relationship between a user table and a friends table and I've been tossing some ideas around. This is one of the structures I came up with.

__________________          ____________________
|Table_Users     |          |Table_Friends     |
------------------          --------------------
|UserID          |-1:M----| |FriendShipID      |
|                |        |<|Requestor         |
|                |        |<|Acceptor          |
|                |          |TimeRequested     |
|                |          |TimeAccepted      |
-----------------           --------------------

Code (markup):

Then to determine friends one could run a query such as:

SELECT "Requestor"
FROM Table_Friends
WHERE "Acceptor" = UserID(This would be the userID of the person's page being viewed. Would probably be passed in programatically)
AND "TimeAccepted" != null

This would grab all of the friends you have that requested friendship then you could save the array returned into an Array Friends[];
Then you would run another query such as:

SELECT "Acceptor"
FROM Table_Friends
WHERE "Requestor" = UserID(This would be the userID of the person's page being viewed. Would probably be passed in programatically)
AND "TimeAccepted" != null

Then append these values to the existing Array Friends[]. This would create a list of all the users current friends


This would allow for the database to only require one other table for the friendship, however I'm not certain this will be efficient if the numbers of users with friends grows. Also I'm not a big fan of two foreign keys pointing to one primary key of a parent table...My reason for posting this is to get some feedback from people who may have tackled this type of thing before and would be able to enlighten me as to if this structure is sound. My biggest concern is whether it's going to take a day and a half to determine if a user has any friends once the friends table gets up to say 500k-1M records

Any thoughts, ideas, or opinions concerning this structure would be greatly appreciated!

 

The easiest way to test this is to run phpmyadmin from cpanel, setup the tables with some dummy information and run the query in the sql window.

I'm a little rusty on sql, but it seems that you are doing a join the hard way.

 

You may be correct...I've been researching this all day and so far I haven't found a concrete way to go about handling this situation efficiently. Some people say to set it up like I have, others suggest a structure that uses complex joins that select values into other values, hell...some people say to have one table with two records for each friendship(where each user in the relationship has a record with their userid as the foreign key and the other individual's userid in a field).

My only concern is handling the situation efficiently so that if by some stroke of luck this system were to reach say (let's go for an astronomical figure) a million users, then it would still function at the same, or close to the same speed of which it did whenever it only had 1,000 users...which probably isn't possible, so lets say "wont crash" with a million users.

I did some tweaking on the above and ditched the adding of values to an array programatically thing...guess I forgot about the good ol' UNION command...so the revised is now:

SELECT "Requestor"
FROM Table_Friends
WHERE "Acceptor" = UserID(This would be the userID of the person's page being viewed. Would probably be passed in programatically)
AND "TimeAccepted" != null
UNION
SELECT "Acceptor"
FROM Table_Friends
WHERE "Requestor" = UserID(This would be the userID of the person's page being viewed. Would probably be passed in programatically)
AND "TimeAccepted" != null

Code (markup):

Not sure this is the most efficient way of handling the situation though since it took .2 seconds for the result set to be displayed and there were only about 5 records in each table...so I moved on to this next example:

SELECT u.username
FROM Table_Friends f, Table_Users u
WHERE (f.Acceptor = var AND u.UserID = f.Requestor)
OR (f.Requestor = var AND u.UserID = f.Acceptor)

Code (markup):

Where var is equal to the user's id being passed into the query....and imagine "username" as a field in Table_Users

This Query took 0.0004 seconds to execute....which isn't bad...Still open to other's thoughts/opinions on this matter!

 

You dont need two queries or union.



select CASE
WHEN requestor=USERID THEN acceptor
ELSE requestor
END
as friend from friends
WHERE (requestor=USERID or acceptor=USERID)