Project Specification

Overview

This project involves the building of an online, relational database to support the interconnection of data related a sports team. A certain school, Franklin High School, has had a boys soccer program since 1970. The project is an effort to capture the data related to the coaches and players of each of the teams formed in each of the years since the beginning of the program's existence. The information is to be presented in an organized way such that a user of the site can easily move from a season to a team to a player and all around amongst these types of data. We would expect the site that is created by this project will have interest to any of the former players, coaches, and supporters.

A model for the project can be viewed on the website for Major League Baseball (MLB). The statistics (navigate to STATS) is similar to what we are after. If one clicks on All-Time Totals, All Time, MLB, All Teams, All Positions one can see a list of players and their aggregate statics for major categories. One can click on the heading of those categories and bring the leaders to the top. One can click on a player's name and see specific information about that player including the years that the player played and statistics for that year.

Our project will be similar to the MLB site, but a few differences. First, we have far fewer players to manage (less than 500 initially and growing slowly per year). Second, the number of different statistics that we track per player is a much smaller set than is tracked by MLB. Lastly, we would like functionality that is not demonstrated on the MLB site, namely the ability to click on a Team within a Player's Information to see the statistics related to that team.

Desired Queries

To illustrate what the project intends to accomplish, we offer some queries that a user of the website may consider and want to learn from the data presented. Here are several queries:

  • Select Team and see a Season List for that Team
  • Looking at Season List, Select Season and see Team Information - list of players and all attributes
  • Looking at Team Information, Select Player and see all Player Information
  • Looking at Player, Select Team and return to Team Information
  • Select Players and see Players List (all players, all seasons), one line per player
  • Sort Players List by Goals, by Assists, by Shutouts, by Captain, by Position, by Year of Graduation, etc.

Specification

In an effort to clarify the requirements of the project, here are some related notes:

  • Tools — We would like the project to integrate with Joomla or Wordpress so would expect use of PHP and MySQL. The level of integration is up for discussion, so there is room for input from the development team.
  • Security — Given that the data may include personal data (e.g. dates of birth), security is important.
  • Ease of Use — The presentation of the data should be clear and organized, and the movement between views of data should be intuitive and convenient.
  • Futures — It seems conceivable that the project could expand later to include the existing of some "user profile" such that a former player or coach could log in and update his/her own personal details. This is not an initial goal of the project, but a thought to this end may be prudent. Integration with a Content Management System (CMS) like Joomla or Wordpress may allow us to leverage tools that make this profile management easier, but integration will be required, we expect.
  • Admin Operations — We will need some facilities to manage and expand the site and database in the future. Certainly, we need the ability to load in a new year's team each year with its players and statistics for team and player. Adding, removing, and modifying data will be necessary. Such Administrative Operations need not have a fancy user interface. In some (all?) cases, a recipe for achieving the necessary results of the operations may suffice.

Details

The project involves the managemetn of Major Data Items, including (at least):

  • Person
  • Player
  • Team
  • Coach
  • .

Each of the Major Data Items will have attributes:

Person Attributes:

First Nametext
Last Nametext
Date of Birthdate
more?want ability to add more

A Player is a Person with additional Attributes:

Year of Graduationnumber
Nicknametext
Position 1[Forward|Midfield|Defender|Goalkeeper|Manager]
Position 2[Forward|Midfield|Defender|Goalkeeper|Manager]
Goalsnumber
Assistsnumber
Savesn/a unless Position 1=Goalkeeper or Position 2=Goalkeeper
Shutoutsn/a unless Position 1=Goalkeeper or Position 2=Goalkeeper
Awardstext
Commentstext
Season 1number
Season 2number
Season 3number
Season 4number
Captain 1number
Captain 2number
Captain 3number
Captain 4number

Not sure how to manage seasons and captains. If a Team has a Player on it, then that Player will have a Season to correspond. If a Team has a Player that is a captain, then that player will have a Captain notation for that Season. This is related to Desired Queries.

A Coach is a Person with additional Attributes:

Type["Head Coach", "Assistant Coach"]
Start Yearnumber
End Yearnumber
Wins[derived from all teams in range of Start-End]
Losses[derived from all teams in range of Start-End]
Ties[derived from all teams in range of Start-End]

Team Attributes

Name[Boys Varsity | Girls Varsity | Others some day?]
Season[1970|1971|1972| .... |2018]
Roster[List of Players]
Wins[calculated: League Wins + Non League Wins]
Losses[calculated: League Losses + Non League Losses]
Ties[calculated: League Ties + Non League Ties]
Goals Scorednumber
Goals Allowednumber
Shutoutsnumber
Captains[List of Players, matches Player data]
League Winsnumber
League Lossesnumbeer
League Tiesnumber
League Positionnumber [1st, 2nd, 3rd]
Non League Winsnumber
Non League Lossesnumber
Non League Tiesnumber
Tourney[yes or no]
Tourney Wins[n/a if Tourney=no]
Tourney Losses[n/a if Tourney=no]
Norfolk County Champs[yes or no]
Division 1 Quarterfinalist[yes or no]
Division 1 Runner Up[yes or no]
Division 1 EMass Champions[yes or no]
South Sectional Quarterfinalist[yes or no]
South Sectional Semifinalistt[yes or no]
South Sectional Finalist[yes or no]