Friend List DB Structure for Game

Primary Key  :-  Primary key uniquely identify a record in the table.

Foreign Key  :-  Foreign key is a field in the table that is primary key in another table.

Table Name: User

Columns:

    UserID PK

    EmailAddress

    Password

    Gender

TableName: Friends

Columns:

    UserID PK FK

    FriendID PK FK

    (This table features a composite primary key made up of the two foreign 

     keys, both pointing back to the user table. One ID will point to the

     logged in user, the other ID will point to the individual friend

     of that user)

Example Usage:

Table User

—————

UserID EmailAddress           Password    Gender 

—————————————————————

1      satya@thered.black      satya@123     M      

2      siva@thered.black        siva@123       M      

3      naveen@thered.black   naveen@123  M      

Table Friends

——————

UserID FriendID

———————

1      2

1      3

2      3

This will show that satya is friends with both siva and naveen and that siva is also friends with naveen. 

Now when showing the friends-list to a particular user, a simple sql would do that:

Select FriendID 

From Friends 

Where UserID = " where is the id of the logged-in user “

If you want to find siva Friends ?

SELECT  FriendID

FROM    Friends

WHERE   UserID = ‘2’

UNION ALL

SELECT  UserID

FROM    Friends

WHERE   FriendID = ‘2’