I am working on a simple social app and have a user_friend table which has both the user_id and friend_id as its composite keys. In the front end, the current user can look at other people's profiles and then click on add friend button which updates the user_friend table. For example, user with id 100 can view user with id 9's and 15's profiles and click on add button and then the user_friend table gets updated as
user_id: 100, friend_id 9
and
user_id: 100, friend_id 15
What is the best approach to handling such request? I was thinking creating a new table called request_table which has requester column which has the id for the user, accepter column which has the id for the friend and status column with accepted and pending. So, when requester clicks add friend button, the status gets updated to accepted which then updates the user_friend table to to reflect the change (by adding a new row user_id: 9, friend_id 100 in the above example).
Please advice if there are cleaner or better ways to do this.
CodePudding user response：
I would make it much simpler than you are thinking .. Your current table looks like:
--------------------------------
| user_id | friend_id |
-------------- -=---------------
| 100 | 15 |
-------------- -----------------
| 100 | 9 |
-------------- -----------------
Add two columns .. requested and accepted:
-------------------------------- ----------------- ----------------
| user_id | friend_id | requested | accepted |
-------------- -=--------------- ----------------- ----------------
| 100 | 15 | 1 | 0 |
-------------- ----------------- ----------------- ----------------
| 100 | 9 | 1 | 1 |
-------------- ----------------- ----------------- ----------------
Although one could ASSUME that if the entry is in the table requested will always be 1 -- So really you only need to add the accepted column .. But you get the basic idea/principle.
NOTE if you need more statuses than just "accepted" like -- Say "blocked" or "suspended" etc etc you can create a third table and use the in a relational way.
-----------------------------------------------------------------
| user_firends (uf_id for indexing FASTER) |
-------------------------------- ----------------- --------------
| uf_id | user_id | friend_id | status |
-------------- -=--------------- ----------------- --------------
| 1 | 100 | 9 | 1 |
-------------- ----------------- ----------------- --------------
| 2 | 100 | 15 | 2 |
-------------- ----------------- ----------------- --------------
---------------------------------------
| statuses_table |
------------------ --------------------
| status_id | status |
------------------ --------------------
| 1 | requested |
------------------ --------------------
| 2 | accepted |
------------------ --------------------
| 3 | rejected |
------------------ --------------------
| 4 | blocked |
------------------ --------------------
- many (users) to many (friends) with a
users_friends"pivot" table AND - many (friends) to single (staus) with a direct insert of status in the column