Data Model for Social Network?

Question

If I wanted to create a site that allowed users to have 0 or more "friends", how would I model such a relationship in a database? Would something this simple work:

Table Friends
- Id (PK)
- UserId (FK)
- FriendId (FK)

???

Would this allow me to later on do things like Facebook does (e.g. "3 of your friends knows this user, maybe you do too")? Or something like 6-degrees-to-Kevin-Bacon?

EDIT 1:

Table Friends
- UserId (FK)
- FriendId (FK)
- Status ('Pending', 'Approved', 'Rejected', 'Blocked'?)

Answer 1

This will work. Following are points to be noted:

• Do you have something like friend confirmation. If yes, you will have to think on how to store 'pending'
• Indexing both UserId and FriendId. These are the values on which you will be joining the tables.
• The unordered pair (UserId, FriendId) is contender for Primary key.
• Suppose Uid_1 and Fid_1 are friends where Uid_1 != Fid_1 then does your Friends Table store (Fid_1, Uid_1) as well as (Uid_1, Fid_1).
• How far in degrees of relationship are you going to search.

Everytime you have to query for DOR(Degree of relationship) you will have to initialize a graph and run Shortest Path Algo (This is the least optimization I can think of). If your member-count rises to some kilos then how are you going to handle this?