Diagramma Entity-Relationship
Diagramma completo delle relazioni tra le tabelle del database fantacalcio.
erDiagram
%% === ENTITA' CENTRALI ===
players {
int id PK
varchar tm_id UK
varchar name
date date_of_birth
int height
enum foot
int sofascore_id UK
int fantacalcio_id UK
int understat_id UK
}
clubs {
int id PK
varchar tm_id UK
varchar name
int understat_id
int sofascore_id
varchar fc_name
char fc_abbr
}
seasons {
varchar season_id PK
varchar season_display
date start_date
date end_date
tinyint is_current
}
matches {
varchar match_id PK
date date
int home_team_id FK
int away_team_id FK
int home_goal
int away_goal
varchar season_id FK
tinyint matchday
}
%% === RELAZIONI ===
player_club_periods {
int id PK
int player_id FK
int club_id FK
date date_from
date date_until
}
season_club_players {
int id PK
varchar season_id FK
int club_id FK
int player_id FK
}
serie_a_clubs_seasons {
int id PK
varchar season_id FK
int club_id FK
}
%% === FANTACALCIO ===
fc_player_seasons {
int id PK
int player_id FK
varchar season_id FK
char role_short
varchar role_mantra
decimal average_vote
decimal fantasy_average
int club_id FK
tinyint seriea
}
fc_player_matches {
int id PK
int player_season_id FK
tinyint match_day
decimal vote
decimal fantasy_vote
varchar match_id FK
int player_team_id FK
varchar allenatore
tinyint seriea
}
fc_quotation_history {
int id PK
int player_season_id FK
tinyint match_day
smallint quotation_classic
smallint quotation_mantra
tinyint seriea
}
fc_fvm_history {
int id PK
int player_season_id FK
decimal match_day
smallint fvm_value
tinyint seriea
}
%% === BERA ===
bera {
int id PK
int player_id FK
varchar season_id FK
int fantacalcio_id
decimal xg_90
decimal npxg_90
decimal xa_90
}
%% === SOFASCORE ===
sofascore {
int id PK
int player_id FK
varchar season_id FK
int club_id FK
varchar league
int goals
int assists
decimal rating
}
%% === TRANSFERMARKT ===
tm_market_value {
int id PK
int player_id FK
int club_id FK
int market_value
tinyint age
date date
}
tm_transfers {
varchar id PK
int player_id FK
int club_from_id FK
int club_to_id FK
varchar season_id FK
bigint fee
bigint market_value
}
tm_player_injuries {
int id PK
int player_id FK
int condition_id FK
varchar season_id FK
date from_date
int days
int games_missed
}
condition_types {
int id PK
varchar name UK
enum type
}
tm_injury_missed_clubs {
int id PK
int injury_id FK
int club_id FK
varchar tm_id
}
%% === UNDERSTAT ===
us_players {
int id PK
int player_id FK
varchar season_id PK
decimal xG
decimal xA
int npg
}
us_player_season_clubs {
int id PK
int understat_player_id FK
varchar season_id FK
int club_id FK
tinyint ordinal
}
us_player_shots {
int id PK
int player_id FK
varchar season_id FK
decimal xG
varchar result
varchar shot_type
varchar situation
}
us_player_match_details {
int id PK
int player_id FK
varchar season_id FK
decimal xG
decimal xA
int time_played
}
us_matches {
int id PK
varchar season_id FK
int home_club_id FK
int away_club_id FK
decimal home_xG
decimal away_xG
}
us_team_history {
int id PK
int club_id FK
varchar season_id FK
decimal xG
decimal xGA
int ppda_att
int ppda_def
}
%% === TABELLE DERIVATE ===
db_player_injuries {
int id PK
int player_id FK
varchar season_id FK
int condition_id FK
date from_date
int days
}
db_missed_matches_injury {
int id PK
int player_id FK
varchar match_id FK
int team_id FK
int injury_id FK
}
%% === RELAZIONI FK ===
matches ||--o{ clubs : "home_team_id"
matches ||--o{ clubs : "away_team_id"
matches }o--|| seasons : "season_id"
player_club_periods }o--|| players : "player_id"
player_club_periods }o--|| clubs : "club_id"
season_club_players }o--|| players : "player_id"
season_club_players }o--|| clubs : "club_id"
season_club_players }o--|| seasons : "season_id"
serie_a_clubs_seasons }o--|| clubs : "club_id"
serie_a_clubs_seasons }o--|| seasons : "season_id"
fc_player_seasons }o--|| players : "player_id"
fc_player_seasons }o--|| seasons : "season_id"
fc_player_seasons }o--o| clubs : "club_id"
fc_player_matches }o--|| fc_player_seasons : "player_season_id"
fc_player_matches }o--o| matches : "match_id"
fc_quotation_history }o--|| fc_player_seasons : "player_season_id"
fc_fvm_history }o--|| fc_player_seasons : "player_season_id"
bera }o--|| players : "player_id"
bera }o--|| seasons : "season_id"
sofascore }o--|| players : "player_id"
sofascore }o--|| seasons : "season_id"
sofascore }o--|| clubs : "club_id"
tm_market_value }o--|| players : "player_id"
tm_market_value }o--o| clubs : "club_id"
tm_transfers }o--|| players : "player_id"
tm_transfers }o--o| clubs : "club_from_id"
tm_transfers }o--o| clubs : "club_to_id"
tm_transfers }o--|| seasons : "season_id"
tm_player_injuries }o--|| players : "player_id"
tm_player_injuries }o--|| condition_types : "condition_id"
tm_player_injuries }o--|| seasons : "season_id"
tm_injury_missed_clubs }o--|| tm_player_injuries : "injury_id"
tm_injury_missed_clubs }o--o| clubs : "club_id"
us_players }o--o| players : "player_id"
us_players }o--|| seasons : "season_id"
us_player_season_clubs }o--|| clubs : "club_id"
us_player_season_clubs }o--|| seasons : "season_id"
us_player_shots }o--o| players : "player_id"
us_player_shots }o--|| seasons : "season_id"
us_player_match_details }o--o| players : "player_id"
us_player_match_details }o--|| seasons : "season_id"
us_matches }o--|| seasons : "season_id"
us_matches }o--o| clubs : "home_club_id"
us_matches }o--o| clubs : "away_club_id"
us_team_history }o--o| clubs : "club_id"
us_team_history }o--|| seasons : "season_id"
db_player_injuries }o--|| players : "player_id"
db_player_injuries }o--|| seasons : "season_id"
db_player_injuries }o--|| condition_types : "condition_id"
db_missed_matches_injury }o--|| players : "player_id"
db_missed_matches_injury }o--|| matches : "match_id"
db_missed_matches_injury }o--|| db_player_injuries : "injury_id"