[RESOLVED] Store/display multiple values in same field

esiason14

player_id,first_name,last_name,position_id,team_id....

I'm creating baseball player pages.....Some players play multiple positions, so I'm trying to figure out the best way to store and retrieve those with multiple position_id's.

For example one player may only play one position, so his record may look like this:

player_id,first_name,last_name,position_id,team_id....

1415,Joe,Schmoe,4,15

However some players may have a record that looks like this:

player_id,first_name,last_name,position_id,team_id....

33415,John,Doe,'4,8',15

Is it ok to store a comma separated list of position_id's in one row?
since some players may have one postiion and some may have 2 or 3 different position_ids...How would I got about retrieving and displaying those?

laserlight

Well, there is a many-many relationship between baseball players and their positions. As such, you would use one table to record the details of baseball players, another table to record the details of the possible positions, and a third table to link them both. For example:

players table:
player_id, first_name, last_name, team_id, ...

positions table:
position_id, name, ...

players_positions table:
id, player_id, position_id, [...]

Is it ok to store a comma separated list of position_id's in one row?

Well, it can be done, but I would advise you to read about Database Normalization and Design Techniques.

esiason14

Ok, I got that down. I have a few tables...teams, players, positions, stats, etc.

My specific question is...can I store multiple player values in the position_id row in the players table? If so, how should I go about it...comma separated, tab separated,etc. If some players then have more than one position_id...how would I go about pulling and displaying the multiple values

laserlight

My specific question is...can I store multiple player values in the position_id row in the players table? If so, how should I go about it...comma separated, tab separated,etc. If some players then have more than one position_id...how would I go about pulling and displaying the multiple values

I showed you precisely how to handle that. Do not use multi-valued fields (i.e., do not use comma separated, tab separated, etc, fields at all). If players must have more than one position_id, it means that you need another table.

esiason14

OK, sorry. I'm at work and was reading through your first response quickly. Thanks!

So that would be the best solution...create a separate table for players with multiple positions?

laserlight

So that would be the best solution...create a separate table for players with multiple positions?

Yes, though more accurately: create a separate table to link players with their positions. Even if they only have one position, it will still be recorded in this new table. The players table itself would not record any position_id. When you want to find out the positions of a player, you would join the tables accordingly. The article I linked to gives an example on page 6.