I have a table called user. Now I am building an app where a user can have many users as friends. So I think I should create a new table called friends_list and implement one-to-many relationship where (user) is one and (friends_list) is many. Then to get the list of friends the user has, I could do select * from friends where userId = XXXx. Is this the best approach? Or is there another better way to create a relationship with the same table?

CodePudding user response：

An approach (not sure if it's the best) would be to use an extra column to store a comma separated value of friend's user_ids.

If the answer to the question "who are my friends?" would not be too difficult to get, you'll need to rely on LIKE %xxx% conditions and shouldnt expect very fast response times.

Another drawback would be some complexity with the relationship maintenance.

Therefore, the two table schema is both the more semantically true and reliable one

CodePudding user response：

Your approach is the best approach. You want to represent an n-m relationship (one user has many friends and vice versa).

There are some considerations. If "friendship" is symmetric (that is A friends B automatically means that B friends A), then you probably want to include both in the table when one is inserted. You may also want to prevent a user from self-friending.

If you want to retrieve user's friends as a list, you can do so using a string concatenation function. In Standard SQL, this is:

select listagg(friendid, ',') within group (order by friendid) as friendids
from friends
where userid = XXX;

Different databases have different function names for listagg().