mysql: proper way to handle relation between users and teams?

Question

Newish to mysql DBs here. I have a table of USERS and a table of TEAMS. A user can be on more then one team. What's the best way to store the relationship between a user and what teams he's on?

Lets say there are hundreds of teams, each team consists of about 20 users, and on average a user could be on about 10 teams, also note that users can change teams from time to time.

I can think of possibly adding a column to my TEAMS table which holds a list of user ids, but then i'd have to add a column to my USERS table which holds a list of team ids. Although this might be a solution it seems messy for updating membership. It seems like there might be a smarter way to handle this information... Like another table perhaps? Thoughts?

Thanks!

ps, whats the best field type for storing a list, and whats the best way to delimit?

Answer 1

whats the best field type for storing a list, and whats the best way to delimit?

It's usually a really bad idea to try to store multiple values in a single column. It's hell to process and you'll never get proper referential integrity.

What you're really looking for is a join table. For example:

CREATE TABLE user_teams (
    user_id INT NOT NULL FOREIGN KEY REFERENCES users(id),
    team_id INT NOT NULL FOREIGN KEY REFERENCES teams(id),
    PRIMARY KEY (user_id, team_id)
);

so there can be any number of team_ids for one user and any number of user_ids for one team. (But the primary key ensures there aren't duplicate mappings of the same user-and-team.)

Then to select team details for a user you could say something like:

SELECT teams.*
FROM user_teams
JOIN teams ON teams.id= user_teams.team_id
WHERE user_teams.user_id= (...some id...);