What would you do.

Heyzoos

I have an interesting problem (at least I think it is)

I have some Tables.

Player

pid | Player ID
.
.
.

Team

tid | Team ID
.
.
.
.
roster | All players who belong to this team.

League

lid | League ID
.
.
.
.
roster | All teams who belong to this league.

What I want to do is store player and team id's comma separated in a field called roster so that I know who belongs where. The reason I have chosen this approach is because there will be multiple instances of both players and teams in leagues in the years to come.

So my questions are;

Is this approach feasable?

What is a good way to add, modify and delete the comma separated data from the field?

I look forward to your thoughts.

mystrymaster

what is pid if you have another field PLAYER ID?

Sxooter

You want another table called players_teams or something like it:

create table players_teams (player_id int references player(id), team_id int references team(id));

Then you just use that to join players to their teams:

select * from player, team, player_teams where player.id=player_teams.player_id and team.id=player_teams.team_id);

Heyzoos

Originally posted by Sxooter
You want another table called players_teams or something like it:

create table players_teams (player_id int references player(id), team_id int references team(id));

Then you just use that to join players to their teams:

select * from player, team, player_teams where player.id=player_teams.player_id and team.id=player_teams.team_id);

This is exactly what I need to do. Thanks 🙂