many to many relationship relational db

Question

I know technically mysql doesn't support many to many. We should create a bridge table. But in my case I still confused about that.

how 2 tables (or more) look like when many users can have many friends? think of it like social network app.

user
=====
id
name

friend
=====
id
name

I'm confused and don't know how to link them, because in a friend table an id can be also a users' id.

Answer 1

Its best practicate to make that 3rd "mapping" table.

user
=====
id
name

m2m_user_friend
=====
user_id
friend_user_id

Both user_id and user_friend_id are foreign keys to that user table.

This allows most efficient querys like "Give me all friends of person id=17" and also, if friend-connections are one-directional (e.g. friendship requests), you can do things the other way around "show me people that are/want to be friends to id=17".

Social networks will use something called a graph database, most likely. Relational databases are great - unless you need to scale up. It gets though at some point to split your data on 10,000s of machines with tradional databases like MySQL.

Answer 2

The question is whether the relationship is necessarily symmetric, i.e. if Alice is Bob's friend implies that Bob is also Alice's friend.

In general this is not true and then you need instead of your table friend a table like this:

friends
======
user_id
friend_id

where both user_id and friend_id point to the table user (i.e. they are the foreign key constraints).

If the relationship is symmetric, I think that the above approach is still working and you can either always insert (or delete) two records, or you always need to check two columns.