This post is the first of a multi-part series that describes the tables of the FMRD-Summary schema.  As the name suggests, this schema is derived from the Football Match Result Database, so I will show which FMRD tables have been retained.  A more detailed description is in the FMRD-Summary wiki page.

Personnel tables

I included the FMRD tables that model personnel data.  By ‘personnel’ I refer to football players, managers, and match referees.  (I don’t capture assistant linesman or sideline official data, but I can start doing so if enough people request it.)

Confederations: This contains the names of football confederations.  (This table is pre-loaded.)

Countries: Contains the name of countries represented by personnel in the competition. (Pre-loaded.)

Positions: Generic field positions of football players.  In FMRD this table is a composite of field position and flank position, but I simplify to one table. (Pre-loaded.)

Players: List of players who participate in a football competition.

Managers: List of managers who participate in a football competition.

Referees: List of central referees who officiate matches in football competitions.

Match Overview tables

These tables capture high-level information on the soccer match, including the competition, matchday, date, and competing teams.

Competitions: List of names of football competitions (e.g. ‘Barclays Premier League’, ‘Liga BBVA’).

Seasons: List of football season years (e.g. ‘2005’, ‘1991-92’).  (Pre-loaded.)

Rounds: List of league rounds (e.g. ‘Round 1’, ‘Round 34’).  (Pre-loaded.)

Teams: List of teams who participate in football competitions.

Venues: List of match venues.

Match tables

Matches: High-level information on match and its participants.

Lineups: List of players who participate in football match.  This is the table that explicitly ties a player to a team.

HomeTeams/AwayTeams: Linking teams to specific matches.

HomeManagers/AwayManagers: Linking managers to specific matches. (Not required to populate this table.)

Match Event tables

Cards: Disciplinary cards that are shown during football match.

PenaltyOutcomes: List of possible outcomes of a penalty shot.

Goals: Descriptions of goals scored during match.

Offenses: Descriptions of events that result in a yellow or red card.

Penalties: List of penalty kick events and outcomes during a match.

Substitutions: List of match substitutions.

InSubstitutions/OutSubstitutions: List of players subbed in or out of matches.