CODEWITHSUNDEEP
mysqldatabase-design
harsh8888
harsh8888

Reputation: 477

add friends logic in database with efficiency

I'm looking forward to a logic to store friends list in a database. I'mthinking of adding things in an array with userids

example: user a's array in friends table would contain userid arrays of friends like 1,2,4,6,77,44 etc

I want to know whether this will be an efficient way of doing this. If not, what logic should be ideally implemented for large community?

Upvotes: 0

Views: 640

Answers (2)

Mike Brant
Mike Brant

Reputation: 71394

You likely need a separate many-to-many join table. To achieve this, if both the user and their friends reside in the same user table. The table could look like this:

user_id - id of user the friend lookup is being done for, is foreign key to user_id field in user table friend_id - id of friend associated with user, also is a foreign key to user_id field in user table

You would have a compound primary key across both fields, ensuring that each user_id to friend_id combination is unique.

This would be sample CREATE TABLE statement:

CREATE TABLE `friends` (
`user_id` INT(11) NOT NULL,
`friend_id` INT(11) NOT NULL,
PRIMARY KEY (`user_id`, `friend_id`)
)
ENGINE = INNODB;

And sample data may look like this:

INSERT INTO `friends` (`user_id`, `friend_id`)
VALUES
(1, 2), (1, 3), (1, 4), (2, 1), (2, 10), (4, 1), (4, 20);

Then say you wanted to do a lookup of all the user table data for the friends associated with a particular user (i.e. the logged in user). You could query that data like this:

SELECT users.*
FROM users
INNER JOIN friends ON users.user_id = friends.friend_id 
WHERE friends.user_id = [The ID of current user]

Upvotes: 2

juergen d
juergen d

Reputation: 204854

No, no, no!

Don't store multiple values in one db field. It will bring you very much problems.

You could use a stucture like this for instance

friends (user_id, user_id)

which indicates 2 friends.

Upvotes: 1

Related Questions