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:

IDteam | name | ...

IDteam| IDplayer| Game (this could be date format)

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

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.

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

Reply via email to