Table structure question

[toyfruit]

Hi All - I'm fairly new to MySQL and am setting up a small site for a 5-aside football team. I want to have a database structure that allows me to add a new game and assign players to that game. What I'm not sure about is how to set it up so I can have one match and multiple players.

 

I assume I need to create a table that links the 'players' table and the 'matches' table I have already created?

 

So if each player has an ID and each match has an ID I should be able to store these values together. What I'm not sure about is how to set this up. Should I have a new table that has a match ID and then one field with all the players stored as an array ... or should I have a column for each player and then create a new row per match and assign a value to each player column?

 

Any help on how to set this up properly would be greatly appreciated!

 

 

[haku]

You are creating a many-to-many relationship. What this means is that one match can have many players, and one player can have many matches. To set this up you want three tables:

 

table 1: players

columns: player_id, whatever else you want

 

table 2: matches

columns: match_id, whatever else you want

 

table 3: joining column

columns: player_id, match_id

 

First you create all your players. They will each have an ID. Then you create a match, which will also have an id. Then you link the two by adding all the players for a match to the third table, using the match_id and the player_id. So each match will have multiple rows in the third table, but each row will be a unique combination of match_id and player_id.

[toyfruit]

That is perfect - thankyou, exactly what I was looking for. So the third table will become fairly substantial over time as many rows will be added because each row will be a unique combination of player_id and match_id? So basically, it would look something like the below?

 

E.g:

 

player1, match1

player2, match1

player3, match1

 

player1, match2

player2, match2

player3, match2

 

etc

[Ken2k7]

Yup, pretty much.