Re: 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 > >