Joining Three Tables by using IDs as Foreign Keys

Question

I'm starting out with learning MySQL and so far I realized I need to JOIN three tables.

The three tables are

1. Users

2. Groups

3. Members

   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.

Answer 1

You are on the right track. Don't let perceived complexity derail the design, as it is basically the standard way to represent many-to-many relationships. These tables are also a good place to store metadata or attributes of the entity relationship.

Your reasoning is mostly correct: there are data integrity benefits to using a DBMS-generated key in the intermediate table approach, but an integer isn't necessarily faster on key comparisons because ideally the joins would use indexes, not the column values.