[PHP-DB] table structure question

2003-12-29 Thread Kirk Babb
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

2003-12-29 Thread Mihail Bota
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

2003-12-29 Thread Aleks @ USA.net
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