[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
[PHP-DB] Table structure
Hi, I would be very grateful if someone could look at my table design for me please. The sytem will be used to allow users to log in to a web site and book staff. Here is my design so far. # -- MySQL dump -- # # Table structure for table 'WMS_Allocations' # CREATE TABLE WMS_Allocations ( Allocation_ID int(11) DEFAULT '' NOT NULL auto_increment, Project_ID int(11) DEFAULT '0' NOT NULL , User_ID int(11) DEFAULT '0' NOT NULL , PRIMARY KEY (Allocation_ID), KEY Project_ID (Project_ID,User_ID) ); # # Table structure for table 'WMS_Bookings' # CREATE TABLE WMS_Bookings ( Booking_ID int(11) DEFAULT '' NOT NULL auto_increment, Booking_Date date DEFAULT '-00-00' NOT NULL , PCT_address varchar(255), PCT_postcode varchar(255), PCT_telephone varchar(255), PCT_manager varchar(255), PCT_gp varchar(255), P_address varchar(255), P_postcode varchar(255), P_telephone varchar(255), P_manager varchar(255), P_gp varchar(255), Allocation_ID int(11), PRIMARY KEY (Booking_ID), KEY Allocation_ID (Allocation_ID) ); # # Table structure for table 'WMS_Projects' # CREATE TABLE WMS_Projects ( Project_ID int(11) DEFAULT '' NOT NULL auto_increment, Project_Name varchar(255), PRIMARY KEY (Project_ID) ); # # Table structure for table 'WMS_User' # CREATE TABLE WMS_User ( User_ID int(11) DEFAULT '' NOT NULL auto_increment, User_Username varchar(100) DEFAULT '' NOT NULL , User_Password varchar(100) DEFAULT '' NOT NULL , User_Name varchar(100) DEFAULT '' NOT NULL , User_Type int(11) DEFAULT '0' NOT NULL , User_Email varchar(100), PRIMARY KEY (User_ID), UNIQUE User_Username (User_Username) ); A user could be a client, a staff member, or an administrator. Clients can log in and book staff to their own projects. Administrators can log in, allocate staff to any project, add/edit/delete - projects/staff/clients, and book staff on a project they are allocated to. A staff member will be able to log in and complete a booking they have undertaken and add in data needed for the project. At the moment i have a single table to incorporate all three types of user but i think i need to change this as the relationship between a client and a project is different to a staff member/administrator and a project. A client can have many projects but a project will only belong to one client where as a staff member/administrator could be allocated to many projects and project could be allocated to many staff members/administrators. My MySQL installation doesnt support Foreign Keys which makes life a little difficult! Any thoughts comments on this would be greatly appreciated. Thanks for your time. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Table structure and displaying certain thing
Hi all, I have a table called items here is a dump: CREATE TABLE items ( id int(10) NOT NULL auto_increment, name varchar(50) NOT NULL default '', item_type int(4) NOT NULL default '0', image varchar(100) NOT NULL default '', price int(10) NOT NULL default '0', quantity int(5) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Here is my code and I know there has to be an easier way to do this so any help would be appreciated. form action=buyitem.php method=post ?php // selects the image of the item from db $query = SELECT image FROM items where id = 1; $ret = mysql_query($query); while(list($image) = mysql_fetch_row($ret)) echo input type=image src=images/blueholo.gif name=\1\BR; //select the name of the item $query = SELECT name FROM items where id = 1; $ret = mysql_query($query); while(list($name) = mysql_fetch_row($ret)) echo B$name/B; //shows the quantity of the item $query = SELECT quantity FROM items where id = 1; $ret = mysql_query($query); while(list($quantity) = mysql_fetch_row($ret)) echo Quantity $quantityBR; //the price of the item $query = SELECT price FROM items where id = 1; $ret = mysql_query($query); while(list($price) = mysql_fetch_row($ret)) echo Cost: $price SC; ? /form What I am trying to do is make it so the user clicks on the image and is taken to buyitem.php through the form where it takes the item out of the items table and updates the user table with that item. Any ideas how I can accomplish this? TIA Jennifer -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
