Can this be made into a Relational Database and work?

Databases
1

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


[FONT=courier new] 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)[/FONT]
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

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

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

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

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 

[FONT=courier new]Tables                                                          Dummy Data within Tables

SP_STATES
  StateID            (int)(PK)                                  7
  StateAbbr          (varchar 2)                                KY
  StateName          (varchar 50)                               Kentucky[/FONT][FONT=courier new]

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[/FONT]
[FONT=courier new]
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[/FONT]
[FONT=courier new]
SP_SPORTS
  SportID            (int)(PK)                                  1
  SportName          (varchar 50)                               Senior Football[/FONT]

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

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

8

  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

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?

10

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.


[FONT=courier new]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)[/FONT]

or would I have linking tables for Coaches, Conferences and Sports like:


[FONT=courier new]SP_TEAMS
  TeamID             (int)(PK)
  TeamName           (varchar 50)
  StartDate          (date)
  EndDate            (date)[/FONT]
[FONT=courier new]
SP_TEAMSCHOOL
  TeamSchoolID       (int)(PK)
  TeamID             (int)(FK to SP_TEAMS:TeamID)
  SchoolID           (int)(FK to SP_SCHOOLS:SchoolID)[/FONT]
[FONT=courier new]
SP_TEAMCOACH
  TeamCoachID        (int)(PK)
  TeamID             (int)(FK to SP_TEAMS:TeamID)
  CoachID            (int)(FK to SP_COACH:CoachID)[/FONT]
[FONT=courier new]
SP_TEAMCONFERENCE
  TeamConferenceID   (int)(PK)
  TeamID             (int)(FK to SP_TEAMS:TeamID)
  ConferenceID       (int)(FK to SP_CONFERENCE:ConferenceID)[/FONT]
[FONT=courier new]
SP_TEAMSPORT
  TeamSportID        (int)(PK)
  TeamID             (int)(FK to SP_TEAMS:TeamID)
  SportID            (int)(FK to SP_SPORT:SportID)[/FONT]
11

Here’s a better model than the one I provided before (I think :slight_smile: ):

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

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

The SQL for tables creation:


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

@gregs -

How is this different than this thread? It looks to be the same data, the same breakdown…

13

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

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

That’s OK.

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

16

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?

[FONT=courier new]
[/FONT][FONT=courier new]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[/FONT]
[FONT=courier new]
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.[/FONT]