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)

    Can this be made into a Relational Database and work?

    I just don't know where to start. They are all the SAME school, but all the fields can change, but I still want them all to be
    able to pulled up in a HISTORY query

    Code:
     YEAR  TEAM                 MASCOT                SPORT              CONFERENCE    CITY             STATE        COACH
     ----  -------------------  --------------------  -----------------  ------------  ---------------  -----------  -------------
     2012  Oak Ridge            Wildcats              Senior Football    4A            Lexington        Kentucky     Alan Smith (2012-01-01 | 2012-12-31)
     2011  Oak Ridge Central    Charging Wildcats     Senior Football    5A Central    New Lexington    Kentucky     John Williams (2011-03-01 | 2011-09-24) Alan Smith (2011-09-25 | 2011-12-31)
     2007  Oak Ridge            Charging Wildcats     Senior Football    3A East       Lexington        Kentucky     John Williams (2007-01-01 | 2007-10-16)
     2006  Oak Ridge            Wildcats              Senior Football    4A            Lexington        Kentucky     John Williams (2006-01-01 | 2006-12-31)
     2005  Oak Ridge            Wildcats              Senior Football    4A            Lexington        Kentucky     Alan Smith (2005-05-07 | 2005-11-21)
    Below is as far as I have gotten:
    
     SPORT
     -----
     SportID       1
     SportName     Senior Football
    
     STATE
     -----
     StateID       1
     StateABRV     KY
     StateName     Kentucky
    
    Would the above be a good many to many relational database? As you can see, most of these things that I changed
    normally wouldn't happen as often as they do, but just for the sake of it happening, I added them.
    
    The year 2005, everything is normal until the end of the season then the Coach retires.
    
    A new year, 2006, goes by with only the new Coach taking them through the season.
    
    The year 2007 comes along and the school board changed the mascot. The team is also dropped to a lower conference. The team
    doesn't get to finish the season, a wave of tornado's destroying Lexington.
    
    Four years later, the school is back but renamed Oak Ridge Central. They are also placed in a larger conference due to enrollment. They had
    gotten their 2005 Coach back but he can't finish the season, so mid-way, they hire a new coach who finishes out the season.
    
    In 2012, the board once more renames the School and Mascot. The team drops into another conference and the city drops the New, reverting back to Lexington once more.
    
    Is it possible to make a Relational Database handle all of this, or am I just Daydreaming?
    Thanks for any hint on where I should start in advance. I just don't see how I can make it into a relational database and
    making a history query work on the supplied data.

  2. #2
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Just to give you an idea, you'd probably start from teams:

    TEAMS
    ------------
    TEAM_ID
    CITY_ID


    TEAM_DETAILS
    -----------------
    TEAM_ID
    YEAR
    NAME
    MASCOT_ID
    SPORT_ID

    and then

    COACH_DETAILS
    -------------------
    COACH_ID
    YEAR
    TEAM_ID

    to finally get to


    CONFERENCE_DETAILS
    --------------------------
    CONFERENCE_ID
    YEAR
    SPORT_ID
    TEAM_ID


    The STATES, CITIES, SPORTS, CONFERENCES, COACHES, MASCOTS tables are simple enough to devise, I believe.

  3. #3
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for replying.

    Am I going to have to have a record for EVERY team? I have been looking all over the internet for creative ideas on how to FORM the tables and am still lost even after your post.

  4. #4
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Yes.

    The TEAMS table would hold just one record per team.

    The TEAM_DETAILS table however will have the TEAM_ID as FK from TEAMS and would host as many records for one team as needed.

    You should notice that what all these *_DETAILS tables have in common is the YEAR field which is the one allowing you to have an historic and keep track of changes in names, coaches, conferences.

    I'm not saying it's the best solution, I've devised this in 15 minutes, but it's a starting point.

    You should have this clear:

    - you have a few entities: TEAMS, SPORTS, CONFERENCES, COACHES, STATES, CITIES
    - you have details for them, on yearly basis: the *_DETAILS tables

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Pennsylvania
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gregs View Post
    Thank you for replying.

    Am I going to have to have a record for EVERY team? I have been looking all over the internet for creative ideas on how to FORM the tables and am still lost even after your post.
    Try to find a book on normalizing a database. itmitica did a great job on determining the classes that are in your data and breaking them down so that they can relate to each other.

    The key is to identify the major themes in your data and then thinking about their attributes. For instance, you have a number of 'coaches' in your data. This would be a flag that Coaches or Faculty will be a Entity Class. You would then think of the attributes that go with a coach. Teams he coaches, sport he specializes in (could be another table, different sporting classes)......

    The point is, what you wish to do is identify each piece of data, discover where it belongs, and store it in one place, and only in one place.

    This reduces or nearly eliminates data update and deletion errors.

  6. #6
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    Tables                                                          Dummy Data within Tables
    
    SP_STATES
      StateID            (int)(PK)                                  7
      StateAbbr          (varchar 2)                                KY
      StateName          (varchar 50)                               Kentucky
    
    SP_SCHOOLS
      ID                 (int)(PK)                                  1                27                    2
      SchoolID           (int)                                      1                1                     2
      SchoolName         (varchar 50)                               Oak Ridge        Oak Ridge Central     Ridgeview
      SchoolCity         (varchar 50)                               Lexington        Lexington             Lexington
      StateID            (int)(FK to SP_STATES:StateID)             7                7                     7
      StartDate          (date)                                     01-01-1950       01-01-2013            01-01-1950
      EndDate            (date)                                     12-31-2012       12-31-2013            12-31-2013
    
    SP_TEAMS
      TeamID             (int)(PK)                                  1                27                    2
      SchoolID           (int)(FK to SP_SCHOOLS:SchoolID)           1                1                     2
      StartDate          (date)                                     01-01-2012       01-01-2013            01-01-2013
      EndDate            (date)                                     12-31-2012       12-31-2013            12-31-2013
      SportID            (int)                                      1                1                     1
    
    SP_SPORTS
      SportID            (int)(PK)                                  1
      SportName          (varchar 50)                               Senior Football
    I have worked hard on this and hope I have went in the right direction.
    My main question is regarding the SP_TEAMS table. Is it needed? If so, am I using it correctly?
    Should it just be a connecting table for each team.. and a new one for each team each year?
    As you can see, I am still REALLY confused about how to work the tables.

    In the SP_SCHOOLS table, I put a link called SchoolID to link the same school's if they changed their name. Am I wrong in doing it that way?

    Is there anything anyone would do differently that might even make it more understandable than it is?

    Which table would I link a mascot table to?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, the teams table is needed, but there's a nuance you're missing -- the team name, and the fact that it can change

    the school name can also change

    any time you want to store data that changes with time, you'd use start and end dates, like you've done, but there would be multiple rows, and they need to be tied to a single "main" table

    you're also missing the coaches information

    on a very minor note, i would use the 2-char state_id as the PK/FK, not a surrogate integer key
    rudy.ca | @rudydotca
    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)
    1) I didn't know where to put the Team name to be honest. I thought that would be redundant data to have it in SP_SCHOOLS and SP_TEAMS tables.

    2) I thought that was what I was doing for when the school name changed, using the SchoolID field in the SP_SCHOOL table. You notice the 2 for Oak Ridge and Oak Ridge Central, denoting they are the same school but it was just renamed "Oak Ridge Central"

    3) I am not sure what you mean by "multiple rows". I thought the fewer.. the better.

    4) As for the Coaches information, I have their table made, but wasn't sure which table to link them to because I am still confused over how to make the TEAM table work.

    5) I have never heard of a "surrogate" integer key. I have read many sites and just trying what they do. Some tables don't even have an auto-increment field, which makes me scratch my head. They use a mixture of two or more keys it seems. Without an auto-increment field, how would you go about deleting a specific file later?

    6) YES!! That is what I need to know, how to link all the teams, schools, mascots, coaches, etc.. to ONE MAIN TABLE. I got excited seeing you type that and hope I can figure it out.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gregs View Post
    6) YES!! That is what I need to know, how to link all the teams, schools, mascots, coaches, etc.. to ONE MAIN TABLE.
    whoa, whoa, hold on there...

    a main teams table, not a main table for everything

    a main teams table would identify each team uniquely, full stop

    a separate table, in a one-to-multiple relationship with the main teams table, would have one row for each contiguous date range having the same attributes

    obviously when the team name changes, that generates another row

    can you see the multiples now? and why they would all have to relate back toi a single row in the main teams table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Okay. Instead of having an auto-incrementing field, you would just use "AR", "AZ", "KY", etc.. That I can probably figure out.

    On the SP_TEAMS table, would it have an auto-increment field that would point to each team, each year.

    For example, there are lots of things that can change about a team from year to year.
    The school can be renamed, their mascot change, their coach leave and have another and their conference even change.

    Code:
    SP_TEAMS
      TeamID             (int)(PK)
      SchoolID           (int)(FK to SP_SCHOOLS:SchoolID)
      SportID            (int)(FK to SP_SPORTS:SportID)
      ConferenceID       (int)(FK to SP_CONFERENCE:ConferenceID)
      CoachID            (int)(FK to SP_COACH:CoachID)
      StartDate          (date)
      EndDate            (date)
    or would I have linking tables for Coaches, Conferences and Sports like:

    Code:
    SP_TEAMS
      TeamID             (int)(PK)
      TeamName           (varchar 50)
      StartDate          (date)
      EndDate            (date)
    
    SP_TEAMSCHOOL
      TeamSchoolID       (int)(PK)
      TeamID             (int)(FK to SP_TEAMS:TeamID)
      SchoolID           (int)(FK to SP_SCHOOLS:SchoolID)
    
    SP_TEAMCOACH
      TeamCoachID        (int)(PK)
      TeamID             (int)(FK to SP_TEAMS:TeamID)
      CoachID            (int)(FK to SP_COACH:CoachID)
    
    SP_TEAMCONFERENCE
      TeamConferenceID   (int)(PK)
      TeamID             (int)(FK to SP_TEAMS:TeamID)
      ConferenceID       (int)(FK to SP_CONFERENCE:ConferenceID)
    
    SP_TEAMSPORT
      TeamSportID        (int)(PK)
      TeamID             (int)(FK to SP_TEAMS:TeamID)
      SportID            (int)(FK to SP_SPORT:SportID)

  11. #11
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Here's a better model than the one I provided before (I think ):

    Visual guide: http://i1054.photobucket.com/albums/...tica-teams.png

    ER-diagram: http://i1054.photobucket.com/albums/...er-diagram.png

    The SQL for tables creation:

    Code:
    CREATE TABLE States (
      id   SERIAL,
      name VARCHAR,
      CONSTRAINT 
        States_PK PRIMARY KEY(id)
    );
    
    CREATE TABLE Cities (
      id         SERIAL,
      name       VARCHAR,
      start_date DATE,
      end_date   DATE,
      old_id     INTEGER,
      state_id   INTEGER,
      CONSTRAINT 
        Cities_PK 
          PRIMARY KEY(id),
      CONSTRAINT 
        Cities_Cities_FK 
          FOREIGN KEY(old_id) 
          REFERENCES Cities(id),
      CONSTRAINT 
        States_Cities_FK 
          FOREIGN KEY(state_id) 
          REFERENCES States(id)
    );
    
    CREATE TABLE Sports (
      id   SERIAL,
      name VARCHAR,
      CONSTRAINT 
        Sports_PK PRIMARY KEY(id)
    );
    
    CREATE TABLE Conferences (
      id         SERIAL,
      name       VARCHAR,
      season     SMALLINT,
      sport_id   INTEGER,
      CONSTRAINT 
        Conferences_PK 
          PRIMARY KEY(id),
      CONSTRAINT 
        Sports_Conferences_FK 
          FOREIGN KEY(sport_id) 
          REFERENCES Sports(id)
    );
    
    CREATE TABLE Teams (
      id         SERIAL,
      name       VARCHAR,
      start_date DATE,
      end_date   DATE,
      old_id     INTEGER,
      city_id   INTEGER,
      CONSTRAINT 
        Teams_PK 
          PRIMARY KEY(id),
      CONSTRAINT 
        Teams_Teams_FK 
          FOREIGN KEY(old_id) 
          REFERENCES Teams(id),
      CONSTRAINT 
        Cities_Teams_FK 
          FOREIGN KEY(city_id) 
          REFERENCES Cities(id)
    );
    
    CREATE TABLE Coaches (
      id   SERIAL,
      name VARCHAR,
      CONSTRAINT 
        Coaches_PK PRIMARY KEY(id)
    );
    
    CREATE TABLE Mascotes (
      id         SERIAL,
      name       VARCHAR,
      start_date DATE,
      end_date   DATE,
      team_id    INTEGER,
      CONSTRAINT
        Mascotes_PK
          PRIMARY KEY(id),
      CONSTRAINT
        Teams_Mascotes_FK
          FOREIGN KEY(team_id)
          REFERENCES Teams(id)
    );
    
    CREATE TABLE Coaches_Teams (
      coach_id   INTEGER,
      team_id    INTEGER,
      start_date DATE,
      end_date   DATE,
      CONSTRAINT 
        Coaches__Coaches_Teams_FK
          FOREIGN KEY(coach_id)
          REFERENCES Coaches(id),
      CONSTRAINT 
        Teams__Coaches_Teams_FK
          FOREIGN KEY(team_id)
          REFERENCES Teams(id)
    );
    
    CREATE TABLE Conferences_Teams (
      team_id         INTEGER,
      conference_id   INTEGER,
      CONSTRAINT 
        Teams__Conferences_Teams_FK
          FOREIGN KEY(team_id)
          REFERENCES Teams(id),
      CONSTRAINT 
        Conferences__Conferences_Teams_FK
          FOREIGN KEY(conference_id)
          REFERENCES Conferences(id)
    );

  12. #12
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,286
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    @gregs -

    How is this different than this thread? It looks to be the same data, the same breakdown....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  13. #13
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry about that, DaveMaxwell.
    It had been a long time since I had visited and this site seems to have changed.
    I didn't know how to find my old post.

  14. #14
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all your help, Dave and Itmitica.

    Even after all the help you two have given, I still can't understand it and am just going to give up on this personal project.

  15. #15
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    That's OK.

    Good luck with the rest of your projects, and maybe, someday, you'll get back to this and own it.

  16. #16
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I have had a couple days to sit around and stew over this and thought of a way I can understand it better.

    The way I see it, EACH team is it's own separate entity, so why can't I just forego the STARTDATE and ENDDATE on
    the SP_TEAMS table and just insert them like so?

    Code:
    
    TeamID  TeamName    TeamMascot    TeamConference  TeamYear  TeamState
    1       Oak Ridge   Lumberjacks   1               2012      4
    2       Ridgeview   Eagles        1               2012      4
    
    Then the next year just make another entry, the teams being separate entities each season
    3       Oak Ridge   Lumberjacks   1               2013      4
    
    and over the years, just having it keep adding them like so
    4       Oak Ridge   Lumberjacks   1               2020      4
    
    Where would the problem be with this? The TeamMascot would be a link, but I just put
    it in for easier reading. I also don't foresee this database ever having over a
    million files. Just saying.


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
  •