[PHP-DB] table structure question
Hi everyone, I would like to set up the following two tables inside a db for a soccer league, but wanted to run their structure by you guys first. Here it is: dbname: soccer_stats tables: teams, players teams -- name | captain | division | paid | pmnt_notes | win | loss | tie | goals_for | goals_against players -- lname | fname | team_name | dob | address | telephone | email | sex | yellow | red | disc_notes | goals_scored | own goals I've never split the info over multiple tables, but this way made sense to me for some reason. I've been thinking, and if, for example, I want to (using mysql) pull the captain of a specific player, how would I do that? Is this a good table design in the first place? Comments and criticisms are welcome! Thanks - Kirk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] table structure question
I would do it a little bit different: 1. I would add ID's for each of the tables. 2. I would split teams in 2 tables; the captain stuff would be in a separate table (captain_team let's say), simply because I would have different players as captains in different games. If you keep the structure as it is now, you'll have a lot of duplication. So, I would do it like this: teams --- IDteam | name | ... teams_captain --- IDteam| IDplayer| Game (this could be date format) players IDplayer |... Then, if you want to see the players who were captains for a given team, you can write smth. like: select distinct p.lname from players p, teams_captain t, teams tt where p.IDplayer=t.IDplayer and t.IDteam=tt.IDteam and tt.name like 'Real Madrid' Check also the normalization forms of a database, which you can find in any database book, simply because you'll have complications if you record more than 1 season and some teams can move in different leagues. Hope this helps. Mihai On Mon, 29 Dec 2003, Kirk Babb wrote: Hi everyone, I would like to set up the following two tables inside a db for a soccer league, but wanted to run their structure by you guys first. Here it is: dbname: soccer_stats tables: teams, players teams -- name | captain | division | paid | pmnt_notes | win | loss | tie | goals_for | goals_against players -- lname | fname | team_name | dob | address | telephone | email | sex | yellow | red | disc_notes | goals_scored | own goals I've never split the info over multiple tables, but this way made sense to me for some reason. I've been thinking, and if, for example, I want to (using mysql) pull the captain of a specific player, how would I do that? Is this a good table design in the first place? Comments and criticisms are welcome! Thanks - Kirk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] table structure question
Hi Kirt, Your tables look ok to me... As for your question, if I were using your example, I would link the field teams.name to players.team_name. For example, I would set another column in the teams table to be auto incrementing. As you add the team information, the team will get a number automatically. Then when you add the players information you can use the value ID from the teams table as the value you enter in the players.team_name column. I do this with an html form that has a drop down list that is generated by pulling the team name and ID from the teams table. Something like this: $sql_teams = mysql_query(SELECT ID, name FROM teams ORDER BY name); Then I create a drop down list in my html form like this: select name=team ? // Creates the list of teams from the teams Table while ($LST = mysql_fetch_array($sql_teams)) { $TeamID = $LST[ID]; $team_name = htmlspecialchars($LST[Name]); echo(option value='$TeamID'$team_name/option\n); } ? /select Hope I did not gunk up your question with an answer you did not need... Anyway, hope this helps... Aleks -Original Message- From: Kirk Babb [mailto:[EMAIL PROTECTED] Sent: Monday, December 29, 2003 4:31 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] table structure question Hi everyone, I would like to set up the following two tables inside a db for a soccer league, but wanted to run their structure by you guys first. Here it is: dbname: soccer_stats tables: teams, players teams -- name | captain | division | paid | pmnt_notes | win | loss | tie | goals_for | goals_against players -- lname | fname | team_name | dob | address | telephone | email | sex | yellow | red | disc_notes | goals_scored | own goals I've never split the info over multiple tables, but this way made sense to me for some reason. I've been thinking, and if, for example, I want to (using mysql) pull the captain of a specific player, how would I do that? Is this a good table design in the first place? Comments and criticisms are welcome! Thanks - Kirk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php