I'm starting out with learning MySQL and so far I realized I need to JOIN three tables.
The three tables are
There is a list of users, a list of groups that users can join, and then the members table is a many-to-many relationship between Users and Groups that lists which user joined which group.
I have a user_id auto_increment for the user table, and a group_id auto_increment for the groups table.
In my Members table, I have user_id and group_id which are both primary keys and foreign keys to the respective tables.
Because my foreign keys are IDs (Integers), If I wanted to display a list of users in a certain group by username, I would have to use JOIN since my Members table only references ID integers...
I would appreciate a professional opinion on whether I'm on the right path and if it will be manageable in the future so I won't have to restart from scratch. I read online that using IDs is good because sometimes using other columns like usernames are vulnerable to change and querying integers is much faster. Is this what most of you guys do? It just seems like a lot of work to have to JOIN lots of tables together.