SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Teams Database Question

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,044
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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
    The only code I hate more than my own is everyone else's.

  5. #5
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    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.

  6. #6
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?


    Code MySQL:
    $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 );

  9. #9
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code MySQL:
      $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.

  10. #10
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This is closed.

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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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 --
    Code:
    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...

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    $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.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,044
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by 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
    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.
    The only code I hate more than my own is everyone else's.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

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

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •