Newish to mysql DBs here. I've got a table of Customers along with a table of TEAMS. A person could be on more the other team. What's the easiest method to keep relationship from a user and what teams he's on?
Allows say you will find 100s of teams, each team includes about 20 customers, and normally a person might be on about 10 teams, also observe that customers can alter teams every so often.
I'm able to think about possibly adding a column to my TEAMS table which holds a listing of user ids, however i'd need to give a column to my Customers table which holds a listing of team ids. Although this can be an answer it appears untidy for upgrading membership. It appears like there can be a wiser way additional information... Like another table possibly? Ideas?
ps, what is the very best area type for storing a listing, and what is the easiest method to delimit?
what is the very best area type for storing a listing, and what is the easiest method to delimit?
It's often a very bad idea to try and store multiple values in one column. It's hell to process and you will never get proper referential integrity.
What you are really searching for is really a join table. For instance:
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 might be a variety of
team_ids for just one
user and a variety of
user_ids for just one
team. (However the primary key guarantees there's not duplicate mappings of the identical user-and-team.)
Then to choose team particulars for any user you can say something similar to:
SELECT teams.* FROM user_teams JOIN teams ON teams.id= user_teams.team_id WHERE user_teams.user_id= (...some id...);