Prevent double entry of data

dpinto04
dpinto04 used Ask the Experts™
on
Hi Experts,

I seem to have a very tricky problem thats just stopped all my dev work.

I'll explain. Its for a small community site where one user adds another user as a friend. I'm using three users as an example. James, Kirk and Lars.

So when james adds kirk as a friend, james has a friend kirk, and kirk has a friend james.

The way the data ends up in the table is like this:

user  |  friend |  status
--------------------------
james |   kirk  |    0

So to select the friends of james would be:
select friend from table where user = 'james' and status = 0

Now the problem is this: kirk too is a friend of james. So, how do I select the friends for kirk without adding kirk as a user and james as his friend? (Since it's already there)

The the sql statement is:
select friends from table where user = 'username' and status = 0

I'll have to add james as a friend to kirk again. But this would double all the data like this

user   |  friend  |  status
-------------------------
james |   kirk     |    0
kirk     |  james  |    0

How could I do this. You can change anything you want since its still in the dev phase.

I hope i'm clear :-)

Anyway you experts can think of a way to do this and not double all the data?

Many Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
select friends from table where (user = 'username' Or friend  = 'username') and status = 0
sorry it will be like

select user, friends from table where (user = 'username' Or friend  = 'username') and status = 0

then in the code check if  user = 'username' the show "friends" name or else show "user" name

Commented:
For this problem I would restructure your Database Design. I would Create a Table for Users and then a seperate table for User Friends Xref. such as:

CREATE TABLE tUser
(
  UserID  INTEGER IDENTITY(1,1) PRIMARY KEY,
  UserName VARCHAR(50)
)

GO

CREATE TABLE tUserFriend
(
  UserID    INTEGER FOREIGN KEY REFERENCES tUser(UserID),
  FriendUserID  INTEGER FOREIGN KEY REFERENCES tUser(UserID),
  Status BIT,
  CONSTRAINT pk_tUserFriend PRIMARY KEY CLUSTERED
  (UserID,FriendUserID)
)
GO

You are then able to Store additional Information about the user in the tUser Table and only store the Xref information in the tUserFriend Table.
Another way to run the query with your current design is with a UNION:

(SELECT friend FROM table WHERE user='username' and status = 0)
UNION DISTINCT
(SELECT user FROM table WHERE friend='username' and status = 0);


--Adrian

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial