ERD Diagram and Database Implementation Plan - aneeshp4/epl-database GitHub Wiki

ERD Diagram

We plan to structure the database after the following Entity Relationship Diagram:

Implementation Plan

1. First we will import the csv file into MySQL Workbench as a single table.
2. Then we will create each of the tables as defined by the ERD diagram above in the following order: Team, Matches, FullTimeResults, HalfTimeResults, Odds, AsianHandicapBetting, OverUnderBetting. We will create these tables in this order to ensure that proper foreign key constraints are established. We've included the EPL_Project.sql file in this repository to see the creation of these tables and their constraints.
3. Next we will fill the Teams table by taking the UNION of the HomeTeam and AwayTeam columns as a subquery, and using the resulting column to populate the Teams table. The TeamID's should be automatically created by AUTO_INCREMENT.
4. To fill the Matches table, we will need to use the 'STR_TO_DATE' function to convert the Date and Time fields from text to their proper types, a JOIN with the Team table to get the right TeamID's, and the rest of the information should easily be selected from the csv's table. The MatchID's should be automatically created by AUTO_INCREMENT.
5. Creating both the FullTimeResults and HalfTimeResults tables should be fairly straightforward after creating the Matches table. The columns are named the same between the Matches table and each of the Results tables, so inserting is just a matter of selecting the right columns from the Matches table for the corresponding table. Both tables will keep track of their own ResultID's via AUTO_INCREMENT.
6. To create the Odds table, first we will need to perform a join between the Matches table and Teams table (via TeamID) to get the team names with each column (Let's call this joined table MT). Then we join the MT table with both the FullTimeResults and HalfTimeResults tables to get both ResultIDs. We also multi-column join with the csv table using the Date, Time, HomeTeam (the name), AwayTeam (the name) to ensure we are joining all the tables properly. This allows us to have the MatchID for each row, which we can then use to insert the correct columns into the Odds table. As stated previously, the OddsID's should be automatically created by AUTO_INCREMENT.
7. For both the Asian Handicap Betting and the Over Under Betting tables, we take the table create by the joining of the 5 tables above (Matches, Team, FullTimeResults, HalfTimeResults, and the csv table) and simply choose the columns necessary from that large combination of tables to properly fill both of these betting tables.