Teams Database Question

[gregs]

To all of you database gurus!

I am working on a sports-type database that goes from year to year. Each year the team changes, but not the name. Does anyone have any idea where I might find something similiar online I could get some ideas from?

For example:

Team: Oak Ridge
Mascot: Timbers
Sport: Football
Year: 2011
State: Somewhere

Each year, the Team and Mascot SHOULD stay the same, but how would I go about storing data for that year?

Is something like this the only way?:

ID, Team, Mascot, Sport, Year
1, Oak Ridge, Timbers, 7, 2011, stats here
2, Oak Ridge, Timbers, 7, 2012, stats here
3, Adamfield, Hornets, 7, 2011, stats here
4, Oak Ridge, Savages, 7, 2013, stats here
5, Pvt Dan, Soldiers, 7, 2014, stats here

* 7 = Football ( from another table )

You should also notice the mascot change in line 4, from Timbers to Savages. It would be the same school, but they decided to change their name.

In reality, the actual school name COULD change over time, like naming it for a fallen soldier or something. I would like to be able to pull up a listing of them like that also, as a history.

2011 Oak Ridge Timbers - stats here
2012 Oak Ridge Timbers - stats here
The school decided to change their mascot
2013 Oak Ridge Savages - stats here
Then the school even changed their name AND mascot
2014 Private Dan Soldiers - stats here

This might be too complicated, but I thought I would just try and ask.

Any ideas please?

[r937]

you need a table where you can identify the team, so that it has a unique id over time, regardless of its name, which will be a data column that is dependent upon the unique id plus the year (assuming teams don't change names mid-season)

i would have a separate table for the mascot, identified by mascot name and year and team id (a three-column primary key) -- a mascot id isn't really necesary, since if a mascot changes its name, it probably isn't the same mascot any more

start with that and see how you get on...

[gregs]

This is all I can come up with. It still doesn't cover what would happen if I tried to get a listing of a school and it changed its name.

If I wanted to view a schools history, I would want it to look like:
2011 Oak Ridge Timbers
2012 Oak Ridge Timbers
2013 Oak Knoll Timbers ( if they changed their name )

Not this:
2011 Oak Knoll Timbers
2012 Oak Knoll Timbers
2013 Oak Knoll Timbers

I hope I am being clear with my worries about schools sometimes changing and wanting to keep up with it.



tb_stats
--------------------
id int(10) NOT NULL AUTO INCREMENT
school int(11) NOT NULL
mascot int(11) NOT NULL
year varchar(4) NOT NULL
state char(2) NOT NULL
... Gobs of stats here? ...


Example:
id school mascot year state sport
1 1 1 2011 17 1 // Valleytown Yellowjackets, AK 2011 Football
2 2 2 2011 17 1 // Eastside Raiders, AK 2011 Football
12 12 42 2011 23 1 // Oak Ridge Timbers, KS 2011 Football


tb_schools
--------------------
id int(10) NOT NULL AUTO INCREMENT
school varchar(30) NOT NULL


Example:
id mascot
1 Valleytown
2 Eastside
12 Oak Ridge


tb_mascots
--------------------
id int(10) NOT NULL AUTO INCREMENT
mascot varchar(30) NOT NULL


Example:
id mascot
1 Yellowjackets
2 Raiders
42 Timbers


tb_sport
--------------------
id int(10) NOT NULL AUTO INCREMENT
sport varchar(25) NOT NULL


Example:
id sport
1 Football
17 Basketball
23 Baseball


tb_state
--------------------
id int(10) NOT NULL AUTO INCREMENT
state varchar(2) NOT NULL


Example:
id state
1 MD
17 AK
23 KS

[oddz]

You are essentially referring to revision history:

schools
- schools_id (pk)

schools_revisions
- revisions_id (pk)
- schools_id ( fk schools(schools_id) )
- name
- mascot
- ...
- created

[gregs]

You are essentially referring to revision history:

schools
- schools_id (pk)

schools_revisions
- revisions_id (pk)
- schools_id ( fk schools(schools_id) )
- name
- mascot
- ...
- created

I will admit I dont understand what you just typed, especially how it was intended to work with my posting.

[gregs]

That's another question of mine.

Can you actually have a table with only one field..

Possibly do that to make each team unique, then make an FK in tb_stats linking them that way. Would have all the information needed then, wouldn't I?

[r937]

we are making excellent progress

your schools, sports, and states tables are fine

your mascot table is okay too, except i think you should relate the mascot to the school right there, rather than doing this in the stats table (it's unlikely that the same mascot belongs to more than one school over time)

please give serious consideration to ~not~ naming your tables with the horrible "tb_" prefix

yes, you can have a table with only one column

oh, and another hint -- if you want to show us what your tables look like, just copy and post the output of a SHOW CREATE TABLE command, instead of retyping everything yourself

[gregs]

This is what I have came up with:

This hopefully would select the team I wanted to see, but only shows it as numbers.
Is there a way within the query to draw what each number is into the printout?
Instead of getting this printout: 2011 1, 3 - 1
..it print this out without using a bunch of queries: 2011 Oak Ridge, AZ - Sr Football
Or is that even possible having the query do all that work?

$query = doquery(
"SELECT * FROM $stats
WHERE year='2011 
AND school = 1
AND sport = 1
AND state = 3
ORDER BY school LIMIT 1"
);
$row = mysql_fetch_array($query);
 
CREATE TABLE `$state` (
`id` int(10) unsigned NOT NULL auto_increment primary key,
`state` varchar(25) NOT NULL default '',
`state_a` VARCHAR(2) NOT NULL default ''
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `$state` VALUES ( 1, 'Alabama', 'AL');
INSERT INTO `$state` VALUES ( 2, 'Alaska', 'AK');
INSERT INTO `$state` VALUES ( 3, 'Arizona', 'AZ');
---
CREATE TABLE `$sport` (
`id` int(10) unsigned NOT NULL auto_increment primary key,
`sport` varchar(25) NOT NULL default ''
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `$sport` VALUES ( 1, 'Sr Football');
---
CREATE TABLE `$school` (
`id` int(10) unsigned NOT NULL auto_increment primary key,
`school` varchar(50) NOT NULL default ''
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `$school` VALUES ( 1, 'Oak Ridge');
---
CREATE TABLE `$city` (
`id` int(10) unsigned NOT NULL auto_increment primary key,
`city` varchar(50) NOT NULL default ''
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `$city` VALUES ( 1, 'Oak Ridge');
---
CREATE TABLE `$conference` (
`id` int(10) unsigned NOT NULL auto_increment primary key,
`conference` varchar(50) NOT NULL default ''
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `$conference` VALUES ( 1, 'North');
---
CREATE TABLE `$classification` (
`id` int(10) unsigned NOT NULL auto_increment primary key,
`classification` varchar(50) NOT NULL default ''
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `$classification` VALUES ( 1, '1A');
---
CREATE TABLE `$team` (
`id` int(10) unsigned NOT NULL auto_increment primary key
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
---
CREATE TABLE `$mascot` (
`id` int(10) unsigned NOT NULL auto_increment primary key,
`mascot` varchar(50) NOT NULL default ''
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `$mascot` VALUES ( 1, 'Timbers');
---
CREATE TABLE `$stats` (
`id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`team_id` INT( 11 ) UNSIGNED NOT NULL default '0',
`year` VARCHAR( 4 ) NOT NULL default '0000',
`school` INT( 11 ) UNSIGNED NOT NULL default '0',
`mascot` INT( 11 ) UNSIGNED NOT NULL default '0',
`sport` INT( 11 ) UNSIGNED NOT NULL default '0',
`state` INT( 11 ) UNSIGNED NOT NULL default '0'
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `$stats` VALUES ( 1, 1
, '2011', 1, 1, 1, 3 );

[gregs]

I am so stressed I can hardly see straight. This database forum has been almost like my dad when I was young in regards to help. To teach me to swim, he just carried me to the middle of a river and tossed me in, saying "Swim or Drown!"

After toiling around with little help at all, I have came up with this:

  $query = doquery("SELECT * FROM ws_stats as t1 " .
                   "LEFT JOIN ws_school as t2 " .
                   "on t1.school = t2.id " .
                   "LEFT JOIN ws_sport as t3 " .
                   "on t1.sport = t3.id " .
                   "LEFT JOIN ws_state as t4 " .
                   "on t1.state = t4.id " .
                   "where year = \"2011\" ", "stats");

It prints out everything correctly but the state information.
I want it to print out the abbreviation ( state_a ), but it prints out the state ( state ).

This is just frustrating.

[gregs]

This is closed.

The query works and I just wasn't printing out the correct variable.

[r937]

okay, let's make a few revisions to your query, and i'll explain them so you don't feel like you're drowning

first of all, i think it's a little confusing to assign aliases like t1 and t2, because the actual table names are more conducive to comprehension

so let's rewrite your query without the aliases --

SELECT * 
  FROM ws_stats
LEFT OUTER
  JOIN ws_school 
    ON ws_school.id = ws_stats.school
LEFT OUTER
  JOIN ws_sport 
    ON ws_sport.id = ws_stats.sport 
LEFT OUTER
  JOIN ws_state 
    ON ws_state.id = ws_stats.state
 WHERE ws_stats.year = '2011'

don't those joins look a lot easier to understand now?

i also made a change to the WHERE clause, to clarify which table that column came from, and also to simplify the character string (using escaped double-quotes is mysql-specific, whereas using single quotes is standard sql for character strings and will work in all databases, so that's the method you want to learn)

next step is to consider the joins themselves

by using LEFT OUTER JOIN, you are anticipating that there will be a row in the left table that doesn't have a match in the right table, and yet you want to keep that row from the left table in the query's output result set

under what circumstances will you have a row in the stats table for a school that doesn't exist? answer: hopefully never!!!

apply the same logic to the other joins, and you will see that they should all be INNER JOINs

finally, with regard to "printing out" the state information, by using the dreaded, evil "select star" you are returning all columns in all tables, so i'm not sure why you're having a problem, but the dang sure way to solve it is to ~not~ use the dreaded, evil "select star" but rather to list all the columns that you do want

if you would care to give that a try, i'll help you with any problems that arise...

[gregs]

Thanks again for coming back to help me. I feel like I had just made my way out of the water when you arrived, then told me what I did would work, but now show me an easier way. It is appreciated though.

$year = '2011';
 
$statequery = doquery("SELECT " .
    "ws_stats.school, ws_stats.sport, ws_stats.state, " .
    "ws_school.school, ws_sport.sport, ws_state.state " .
    "FROM ws_stats " .
    "LEFT OUTER " .
      "JOIN ws_school " .
        "ON ws_school.id = ws_stats.school " .
    "LEFT OUTER " .
      "JOIN ws_sport " .
        "ON ws_sport.id = ws_stats.sport " .
    "LEFT OUTER " .
      "JOIN ws_state " .
        "ON ws_state.id = ws_stats.state " .
    "WHERE ws_stats.year = '".$year."'" .
    "ORDER BY ws_school.school", "stats");

I think I fixed it up the way you mentioned, getting rid of the asterick. It has been tough to make it work with the doquery() routine I have been using, but I have been able to keep up so far.

With a query like such, would I possibly be able to use it to print out the items in such a way, if they are within the stats table?

Alabama
-- Team 1 -- Team 2 -- Team 3
-- Team 4

Connecticutt
-- Team 1

Hawaii
-- Team 1 -- Team 2

Wyoming
-- Team 1 -- Team 2 -- Team 3
-- Team 4 -- Team 5

Or would I have to do a query to find all the states, then run a query searching for each school in that state to print out? It would look through the ws_stats table and if there is any team within it, it will list it on that page.

[r937]

yes, you can print stuff out that way (although that part isn't a mysql issue) -- just make sure to ORDER BY state and then team

oh, wait, you don't have a team column in the SELECT clause

but you do appear to have two of everything -- two school columns, two sports columns, two state columns -- and that might be causing problems in getting php to print the right ones

and the joins should be INNER JOINs

[oddz]

yes, you can print stuff out that way (although that part isn't a mysql issue) -- just make sure to ORDER BY state and then team

I have always been cautious of this statement. It seems it would be much more efficient to do that data massaging in the application language without adding a order by to query. It is especially easy to achieve in PHP with the way arrays behave.

[r937]

if php can handle all the returned data at once in memory arrays, then yes, eliminating the ORDER BY is almost always going to improve the performance of the query

[gregs]

Hello again

This is what I came up with. I am sure fault will be found in it, but it was all I could think of in order to print each state and then all the teams under each, BY state

function teams() {
 
  $page = "<h3>Show Team</h3>\n";
  $page .= "<table id=\"info\">\n";
  $year = '2011';
 
  $statequery = doquery("SELECT DISTINCT " .
                        "ws_stats.state, " .
                        "ws_state.id, ws_state.state " .
                        "FROM ws_stats " .
                        "INNER " .
                          "JOIN ws_state " .
                            "ON ws_state.id = ws_stats.state " .
                        "WHERE ws_stats.year = '".$year."'" .
                        "ORDER BY ws_state.state", "stats");
 
  while ($state = mysql_fetch_array($statequery)) {
    $page .= "<tr><td>".$state["state"]."</td></tr>\n";
 
    $schoolquery = doquery("SELECT DISTINCT " .
                           "ws_stats.school, ws_stats.state, " .
                           "ws_school.school, ws_state.state " .
                           "FROM ws_stats " .
                           "INNER " .
                             "JOIN ws_school " .
                               "ON ws_school.id = ws_stats.school " .
                           "INNER " .
                             "JOIN ws_state " .
                               "ON ws_state.id = ws_stats.state " .
                           "WHERE ws_stats.state = '".$state["id"]."'" .
                           "ORDER BY ws_school.school", "stats");
 
    $count = 0;
 
    while ($school = mysql_fetch_array($schoolquery)) {
        if ($count == 0 ) { $page .= "<tr>"; }
        $page .= "<td width=\"150\">".$school["school"]."</td>";
        if ($count % 3 ) { $page .= "</tr>\n"; }
        $count++;
    }
  }
 
  if (mysql_num_rows($statequery) == 0) { $page .= "<tr class=\"alt\"><td>No teams found.</td></tr>\n"; }
  $page .= "</table>";
  admindisplay($page, "Teams");
}

It works okay, but I may have to move over to another forum to figure out how to make the second WHILE statement with the Modulo print out the closing </tr>'s.

I keep getting this source output, a missing </TR> before the last <TABLE>

    <divid="content">
 
      <h3>Show Team</h3>
<tableid="info">
<tr><td>California</td></tr>
<tr><tdwidth="150">Eastside</td><tdwidth="150">Westside</td></tr>
<tr><td>Kentucky</td></tr>
<tr><tdwidth="150">Oak Ridge</td></table>
    </div>