Football Data Database Markup Language - with joins

football-withjoins.DBML

Project project_name {
  database_type: 'BigQuery'
  Note: 'Football Data by https://github.com/dcaribou'
}

Table appearances {
  player_id INTEGER
  game_id INTEGER
  appearance_id STRING [pk]
  league_id STRING
  player_club_id INTEGER
  goals INTEGER
  assists INTERER
  minutes_played INTERER
  yellow_cards INTERER
  red_cards INTERER
}

Table clubs {
  club_id INTERER [pk]
  name STRING
  pretty_name STRING
  domestic_competition STRING
  league_id STRING
  total_market_value FLOAT
  squad_size INTERER
  average_age FLOAT
  foreigners_number INTERER
  foreigners_perecentage FLOAT
  national_team_players INTERER
  statium_name STRING
  statium_seats INTEGER
  net_transfer_record STRING
  coach_name STRING
  url STRING
}

Table games {
  game_id INTERER [pk]
  league_code STRING
  season INTERGER
  round STRING
  date DATE
  time TIME
  home_club_id INTERGER
  away_club_id INTERGER
  home_club_goals INTERGER
  away_club_goals INTERGER
  home_club_position INTERGER
  away_club_potition INTERGER
  stadium STRING
  attendance INTERGER
  url STRING
}

Table leagues {
  league_id STRING
  name STRING
  confederation STRING
}

Table players {
  player_id INTERGER
  current_club_id INTEGER
  name STRING
  pretty_name STRING
  country_of_birth STRING
  country_of_citzenshp STRING
  date_of_birth DATE
  position STRING
  sub_position STRING
  foot STRING
  height_in_cm INTEGER
  url STRING
}

Ref "ONECLUB-MANYPLAYERS" {
  clubs.club_id < players.current_club_id
}

Ref "ONECLUB-MANYGAMES-HOME" {
  clubs.club_id < games.home_club_id
}

Ref "ONECLUB-MANYGAMES-AWAY" {
  clubs.club_id < games.away_club_id
}

Ref "ONECLUB-APPEARANCES" {
  clubs.club_id < appearances.player_club_id
}

Ref "ONELEAGUES-MANYCLUBS" {
  leagues.league_id < clubs.league_id
}

Ref "ONELEAGUES-MANYAPPERANCES" {
  leagues.league_id < appearances.league_id
}

Ref "ONELEAGUES-MANYGAMES" {
  leagues.league_id < games.league_code
}

Ref "ONEGAME-MANYAPPEARANCES" {
  games.game_id < appearances.game_id
}

Ref "ONEPLAYER-MANYAPPEARANCES" {
  players.player_id < appearances.player_id
}