I need some help with my database relations. My problem is that I don't know if i'm approaching this problem the right way. I have an application where users can befriend other users similar to friendships on Facebook.
╔═══════════╗
║ Users ║
╠═══════════╣
║ #id ║
║ *username ║
║ *password ║
╚═══════════╝
╔══════════════╗
║ User_friends ║
╠══════════════╣
║ *user_id ║
║ *friend_id ║
╚══════════════╝
╔════════════════╗
║ Friend_request ║
╠════════════════╣
║ #id ║
║ *user_id ║
║ *friend_id ║
║ *created_at ║
║ *updated_at ║
╚════════════════╝
User John Doe wants to login to my application and send a friendship request to Jane Doe so a record will be stored in Friend_request and when Jane Doe logs in to the application she will see a notice that someone wants a friendship. When Jane Doe accepts John Doe's friendship request I want to store two records into User_friends because John Doe is a friend of Jane Doe and Jane Doe has a friendship with John Doe
I am not sure if i'm approaching this problem in the right way.
What you have done is reasonable and straightforward.
PS There are further candidate keys (UNIQUE NOT NULL column sets not containing smaller ones) than the PRIMARY KEYs that you have indicated (via #):
Users (username) -- likely -- though not on Stack Overflow
User_friends (user_id, friend_id)
Friend_request (user_id, friend_Id)
PPS Re title "Many to many relationship with itself": I guess you mean that in User_friends and in Friend_request entity class Users has a "many to many relationship with itself". Tables represent (application) relationships. Values in rows are thus related, and entities they designate are thus related. There are the FOREIGN KEYs from the user_id and friend_id columns to Users id. These are often called "relationships" although they are facts/constraints. (If you queried for the user names of a user and requested or accepted friend then you would JOIN Users with itself.)