Best way to store "friends" in a database?

[ttocskcaj]

I'm working on  a Social Network from scratch. I'm stuck on how to store friend relationships between users?

On one topic here, I found an idea.

Have a table with 3 columns; id,user_1,user_2

each relationship would have a new row in the database.

Would this be the best way to do this? I can imagine the table would get quite big.

If each user has 100 friends and there's 10000 users that's 1000000 rows. etc

[revraz]

That probably would be the best way to approach it.

[the182guy]

I also agree that is the best way to do it. 1,000,000 rows is not massive really in terms of RDMS. Especially when the row of data is just a PK and two FKs, it won't take up much storage and the database engine will have no problem searching and joining that table.

[ignace]

Personally I think you should lose the ID, user_1 and user_2 should be sufficient

[revraz]

I can see more advantages to having a row id than not having one.  It's auto incremented, what can it hurt?

[BlueSkyIS]

yes, you might want that primary key later.

[ignace]

I can see more advantages to having a row id than not having one.  It's auto incremented, what can it hurt?

 

The compound user_1 and user_2 is unique why add more unique values to something that's already unique? Adding the extra ID shows a lack of DB knowledge as you can easily reference a compound key from another table:

 

CREATE TABLE compound (
  user ..
  friend ..
  ..
  FOREIGN KEY (user, friend) REFERENCES friends (user, friend)
);

 

Don't add an ID just for the sake of having an ID in each table. On a side note ID's are not by definition INT UNSIGNED NOT NULL AUTO_INCREMENT they could aswell be a VARCHAR, just so you know

[ttocskcaj]

Cool. Thanks for the suggestions guys

[BlueSkyIS]

I can see more advantages to having a row id than not having one.  It's auto incremented, what can it hurt?

 

The compound user_1 and user_2 is unique why add more unique values to something that's already unique? Adding the extra ID shows a lack of DB knowledge as you can easily reference a compound key from another table:

 

 

 

Good insights! I always add a primary key, thinking that's just the way it's done. Obviously not so. I gain another gram of knowledge!

[davelearning]

I would add a field like 'active' if user2 accepts user1's request

[ignace]

I can see more advantages to having a row id than not having one.  It's auto incremented, what can it hurt?

 

The compound user_1 and user_2 is unique why add more unique values to something that's already unique? Adding the extra ID shows a lack of DB knowledge as you can easily reference a compound key from another table:

 

 

 

Good insights! I always add a primary key, thinking that's just the way it's done. Obviously not so. I gain another gram of knowledge!

 

Glad I could help