to implement friend module what datatype structure should used?

Hi all,

I am developing an application for which I require a friend module

and I am stuck at this module. i.e. How to know if the particular users are one's friends.

I googled a lot but didn't get any satisfactory answers. What I got is : there will be many friends of a person and implementing users and their friends in seperate table will only increase redundancy and large DB size.

so please give me the solution....

Thanks in advance

Hi,

I will suggest this schema:

CREATE TABLE [User]
    (
      [UserID] INT IDENTITY ,
      [Name] NVARCHAR(50)
    )
    
GO
--Cross table to store friend id and user id
--Belos UserID+ FriendUserID combination should be unique
CREATE TABLE [User_Friend]
    (
      [UserID] INT IDENTITY ,
      [FriendUserID] INT
    )
GO    

Thanks Raghav

It's nice reply

Regards,

pallavi

Hi Raghav,

I have created the following table schema

CREATE TABLE `tbl_friend` (
  `User_id` int(11) NOT NULL,
  `Friend_id` int(11) NOT NULL,
  PRIMARY KEY (`User_id`,`Friend_id`),
  UNIQUE KEY `User_id` (`User_id`,`Friend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=0;

I want unique combination of both columns

i.e in the following tbl i want to restrict data appear on 2nd row meanse 8& 16 is fine but if i swap it ,it should not be allowed

I hope you will understand wht i want to say

Please help

User_id	Friend_id
8	16
16	8

Hi,

Following is the schema and constraints:

GO
CREATE TABLE [User]
    (
      [UserID] INT IDENTITY ,
      [Name] NVARCHAR(50)
    )
     
GO
--Cross table to store friend id and user id
--Below UserID + FriendUserID combination should be unique
CREATE TABLE [User_Friend]
    (
      [UserID] INT  ,
      [FriendUserID] INT
    )
GO


ALTER TABLE [User_Friend]
ADD CONSTRAINT UQ_User_Friend_MyConstraint UNIQUE([UserID], [FriendUserID])


GO

CREATE FUNCTION CheckUser_Friend_MyConstraintInReverseOrder
    (
      @UserID INT ,
      @FriendUserID INT
    )
RETURNS INT
AS 
    BEGIN
        DECLARE @ret INT	
        IF ( @UserID = @FriendUserID ) 
            BEGIN
                SET @ret = 1
            END
        ELSE 
            BEGIN
                IF EXISTS ( SELECT  1
                            FROM    [User_Friend]
                            WHERE   [UserID] = @FriendUserID
                                    AND [FriendUserID] = @UserID ) 
                    BEGIN
                        SET @ret = 0
                    END
                ELSE 
                    BEGIN
                        SET @ret = 1
                    END
            END
        RETURN @ret

    END ;
GO

ALTER TABLE [User_Friend]
  ADD CONSTRAINT CheckReverseConstraint CHECK (dbo.CheckUser_Friend_MyConstraintInReverseOrder([UserID],[FriendUserID]) = 1);

GO

and following is the statements to check

--Now let's test it 


INSERT INTO [dbo].[User_Friend]
        ( [UserID], [FriendUserID] )
VALUES  ( 1, -- UserID - int
          1  -- FriendUserID - int
          )
          
--
--(1 row(s) affected)  

INSERT INTO [dbo].[User_Friend]
        ( [UserID], [FriendUserID] )
VALUES  ( 1, -- UserID - int
          2  -- FriendUserID - int
          )     
--(1 row(s) affected)    



INSERT INTO [dbo].[User_Friend]
        ( [UserID], [FriendUserID] )
VALUES  ( 1, -- UserID - int
          2  -- FriendUserID - int
          )     
--Msg 2627, Level 14, State 1, Line 2
--Violation of UNIQUE KEY constraint 'UQ_User_Friend_MyConstraint'. Cannot insert duplicate key in object 'dbo.User_Friend'.
--The statement has been terminated.
 



INSERT INTO [dbo].[User_Friend]
        ( [UserID], [FriendUserID] )
VALUES  ( 2, -- UserID - int
          1  -- FriendUserID - int
          )         
          
--Msg 547, Level 16, State 0, Line 2
--The INSERT statement conflicted with the CHECK constraint "CheckReverseConstraint". The conflict occurred in database "Test", table "dbo.User_Friend".
--The statement has been terminated.
       

 

CREATE DEFINER = 'root'@'localhost' FUNCTION `cons_ontblfriend`(
        userid INTEGER,
        friendid INTEGER
    )
    RETURNS int(11)
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
  	DECLARE ret INTEGER(11);
    
    	IF userid = friendid THEN SET ret=1;
        ELSEIF EXISTS (select 1 from `tbl_friend` where User_id=friendid AND Friend_id=userid) THEN SET ret=0;
        ELSE SET ret=1;
    	End IF;                       
     RETURN ret;
        
END;



ALTER table `tbl_friend` ADD CONSTRAINT chk CHECK (`cons_ontblfriend`(userid,friendid) =1);

Hi Raghav,

i have implemented the above function  as above

but when i am trying to insert a record into reverse order(1,2)(2,1) it is not restricting me from inserting records in reverse order 
what's wrong 

i also tried to put that alter statement into trigger i don't know whether it is correct or not

but trigger is not getting compiled fetching the error : Explicit or implicit commit is not allowed in stored function or trigger.

 

Follow the example exactly which I explained above. It is running at my end.

Hi raghav,

I have followed your example exactly but it dosen't work i don't know why..

actually i have another problem ,

i tried it a lot but does'nt get the correct solution

my requirement is, like facebook or codeasp.net i want to show friend list(profile photo & user_name) of a particular user

so i have to fetch a sql querry with join on table tbl_user &  tbl_friend

 

pallavi karpe said:

I have followed your example exactly but it dosen't work i don't know why..

I have given the output too with samples. The output is delivered by Query Analyzer, the same thing is working at my end.