toyfruit Posted June 11, 2009 Share Posted June 11, 2009 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! Link to comment https://forums.phpfreaks.com/topic/161818-table-structure-question/ Share on other sites More sharing options...
haku Posted June 11, 2009 Share Posted June 11, 2009 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. Link to comment https://forums.phpfreaks.com/topic/161818-table-structure-question/#findComment-853765 Share on other sites More sharing options...
toyfruit Posted June 11, 2009 Author Share Posted June 11, 2009 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 Link to comment https://forums.phpfreaks.com/topic/161818-table-structure-question/#findComment-853790 Share on other sites More sharing options...
Ken2k7 Posted June 12, 2009 Share Posted June 12, 2009 Yup, pretty much. Link to comment https://forums.phpfreaks.com/topic/161818-table-structure-question/#findComment-854207 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.