Facebook database design?

Question

I have always wondered how Facebook designed the friend <-> user relation.

I figure the user table is something like this:

user_email PK

user_id PK

password 

I figure the table with user's data (sex, age etc connected via user email I would assume).

How does it connect all the friends to this user?

Something like this?

user_id

friend_id_1

friend_id_2

friend_id_3

friend_id_N 

Probably not. Because the number of users is unknown and will expand.

Answer 1

Table Name: User

Columns:

    UserID PK

    EmailAddress

    Password

    Gender

    DOB

    Location

TableName: Friends

Columns:

    UserID PK FK

    FriendID PK FK (This table will feature a composite primary key made up of the two foreign 

     keys, both will point back to the user table. Here, one ID will point to the

     logged in user, and the other ID will point to the individual friend of that user)

Example Usage are as follows:

Table User

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

UserID EmailAddress Password Gender DOB      Location

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

1      [email protected]  bobbie   M      1/1/2009 New York City

2      [email protected]  jonathan M      2/2/2008 Los Angeles

3      [email protected]  joseph   M      1/2/2007 Pittsburgh

Table Friends

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

UserID FriendID

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

1      2

1      3

2      3

This shows that Bob is friends with both Jon and Joe and that Jon is also friends with Joe. In the above example we will consider that friendship is always two ways, so you don't need a row in the table such as (2,1) or (3,2) because they are already represented in the other direction. But, if friendship or other relations aren't explicitly two way, then you need to have those rows to indicate the two-way relationship.