confusion over relationships

Hey guys I have a confusing question, I have a user table and it stores all the usual data for a user that you would expect but im trying to figure out how a user could add another user?

Sounds strange but each user in the User table has his own UI which is UserID how could I add another table where UserID can have a relationship with another UserID?

I will give the answer to those that could take the time to upload a table diagram and who can help with some examples for sqlsyntax related to this que开发者_运维百科stion, i.e how would I right the syntax for the above question if I wanted to display all the UserIDs friends on a page. How would I add a friend.

---

You just need another table similar to the Pictures and wallposting table. You just need to be able to record many friend id's for one user id (one to many)

eg:

The query to then get friends would be:

DECLARE @UserID AS BIGINT

SET @UserID = 123

SELECT [friends].*
FROM [friends]
where [parentuserid]=@UserID#

to Insert a Friend:

DECLARE @UserID AS BIGINT
DECLARE @FriendID AS BIGINT

SET @UserID = 123
SET @FriendID = 321

INSERT INTO [Friends]
(
    [ParentUserID],
    [ChildUserID]
)
VALUES
(
    @UserID,
    @FriendID
)

Code to insert it example:

private void Test()
{
    string Query =
        @"INSERT INTO [Friends]
          (
            [ParentUserID],
            [ChildUserID]
          )
          VALUES
          (
            @UserID,
            @FriendID
          )";

    using ( SqlConnection oSqlConnection = new SqlConnection( "connect string" ) )
    {
        oSqlConnection.Open();

        using (SqlCommand oSqlCommand = new SqlCommand(Query,oSqlConnection))
        {
            oSqlCommand.Parameters.AddWithValue("@UserID", Session["UserID"]);
            oSqlCommand.Parameters.AddWithValue("@FriendID", Session["FriendID"]);

            oSqlCommand.ExecuteNonQuery();
        }
    }
}

Where would you get your friend ID from? what is the process for adding friends? do you search for them and select them from a list?

---

You just need a many to many relationship.

Create Table Friend(
  UserId int,
  FriendId int,
  Constraint pk_friends Primary Key (UserId, FriendId),
  Constraint fk_friend_user Foreign Key (UserId) References User(UserId),
  Constraint fk_friend_friend Foreign Key (FriendId) References User(UserId)
)

---

Your asking a bit much with the diagrams etc.... for me at least.

It depends on the kind of relationship, one to one, it will be another Column in your user table. If it is many to many you'll need a junction table, with two columns UserIDA, UserIDB. Depending why you need the relationship.

---

I think you have a simple requirement of a User Referring Other user to join. If that is the case then you can simply have one more column ReferenceUserID in User Table and whenever a new user refers another user then simply add him with a UserID of referring user. If he isn't a referred user then by default it will be NULL.

Later for retrieval you can use a Self Join.

Update:

For Friend (many to many relationship) you should look at following Question at StackOverflow

Database design: Best table structure for capturing the User/Friend relationship?

UserRelationship
====
RelatingUserID
RelatedUserID
Type[friend, block, etc]

A similar approach is used by Facebook where they have a kind of Cross join Table for relation between Friends.