/*============================================================================*/
/* DDL SCRIPT                                                                 */
/*============================================================================*/
/* Title:      XTOSS                                                          */
/* Filename:   New Model 2.hf                                                 */
/* Platform:   Microsoft SQL Server 2000                                      */
/* Generated:  Monday, June 26, 2006                                          */
/*============================================================================*/

/*============================================================================*/
/* Domains                                                                    */
/*============================================================================*/

EXEC sp_addtype primary_id, 'INT', 'NULL'
GO

/*============================================================================*/
/* Tables                                                                     */
/*============================================================================*/

CREATE TABLE addresses (
    id primary_id NOT NULL,
    location_id INT NOT NULL,
    language VARCHAR(100),
    suite VARCHAR(100),
    floor VARCHAR(100),
    building VARCHAR(100),
    street_number VARCHAR(100),
    street_prefix VARCHAR(100),
    street VARCHAR(100),
    street_suffix VARCHAR(100),
    neighborhood VARCHAR(100),
    district VARCHAR(100),
    locality VARCHAR(100),
    county VARCHAR(100),
    region VARCHAR(100),
    postal_code VARCHAR(100),
    country VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE affiliations (
    id primary_id NOT NULL,
    affiliation_key VARCHAR(100) NOT NULL,
    tier VARCHAR(100),
    parent_id INT,
    start_season_id INT,
    start_date_time VARCHAR(100),
    end_season_id INT,
    end_date_time VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE document_classes (
    id primary_id NOT NULL,
    name VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE publishers (
    id primary_id NOT NULL,
    publisher_key VARCHAR(100) NOT NULL,
    publisher_name VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE document_fixtures (
    id primary_id NOT NULL,
    fixture_key VARCHAR(100),
    publisher_id INT NOT NULL,
    name VARCHAR(100),
    document_class_id INT NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE documents (
    id primary_id NOT NULL,
    doc_id INT NOT NULL,
    publisher_id INT NOT NULL,
    date_time DATETIME,
    title VARCHAR(255),
    language VARCHAR(100),
    priority VARCHAR(100),
    revision_id INT,
    stats_coverage VARCHAR(100),
    document_fixture_id INT NOT NULL,
    source_id INT NOT NULL,
    db_loading_date_time VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE affiliations_documents (
    affiliation_id INT NOT NULL,
    document_id INT NOT NULL,
    PRIMARY KEY (affiliation_id, document_id)
)
GO

CREATE TABLE sites (
    id primary_id NOT NULL,
    site_key INT NOT NULL,
    publisher_id INT NOT NULL,
    location_id INT,
    PRIMARY KEY (id)
)
GO

CREATE TABLE events (
    id primary_id NOT NULL,
    event_key VARCHAR(100),
    publisher_id INT NOT NULL,
    start_date_time DATETIME,
    site_id INT NOT NULL,
    site_alignment VARCHAR(100),
    event_status VARCHAR(100),
    duration VARCHAR(100),
    attendance VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE affiliations_events (
    affiliation_id INT NOT NULL,
    event_id INT NOT NULL,
    PRIMARY KEY (affiliation_id, event_id)
)
GO

CREATE TABLE locations (
    id primary_id NOT NULL,
    timezone VARCHAR(100),
    latitude VARCHAR(100),
    longitude VARCHAR(100),
    country_code VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE seasons (
    id primary_id NOT NULL,
    season_key INT NOT NULL,
    publisher_id INT NOT NULL,
    league_id INT NOT NULL,
    start_date_time VARCHAR(100),
    end_date_time VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE person_league_phases (
    id primary_id NOT NULL,
    person_id INT NOT NULL,
    league_id INT NOT NULL,
    start_date_time VARCHAR(100),
    start_season_id INT NOT NULL,
    end_date_time VARCHAR(100),
    end_season_id INT NOT NULL,
    person_role VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE positions (
    id primary_id NOT NULL,
    affiliation_id INT NOT NULL,
    abbreviation VARCHAR(20) NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE teams (
    id primary_id NOT NULL,
    team_key VARCHAR(100) NOT NULL,
    publisher_id INT NOT NULL,
    home_site_id INT NOT NULL,
    league_id INT NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE team_phases (
    id primary_id NOT NULL,
    person_id INT NOT NULL,
    role_id INT NOT NULL,
    team_id INT NOT NULL,
    uniform_number VARCHAR(100),
    regular_position_id INT NOT NULL,
    start_date_time VARCHAR(100),
    start_season_id INT NOT NULL,
    end_date_time VARCHAR(100),
    end_season_id INT NOT NULL,
    height VARCHAR(100),
    weight VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE persons (
    id primary_id NOT NULL,
    person_key VARCHAR(100) NOT NULL,
    publisher_id INT NOT NULL,
    gender VARCHAR(100),
    current_injury_phase_id INT,
    current_team_phase_id INT,
    current_league_phase_id INT,
    birth_date VARCHAR(100),
    death_date VARCHAR(100),
    birth_location_id INT,
    hometown_location_id INT,
    residence_location_id INT,
    death_location_id INT,
    PRIMARY KEY (id)
)
GO

CREATE TABLE roles (
    id primary_id NOT NULL,
    role_key VARCHAR(100) NOT NULL,
    role_name VARCHAR(100),
    comment VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE injury_phases (
    id primary_id NOT NULL,
    person_id INT NOT NULL,
    injury_status VARCHAR(100),
    injury_type VARCHAR(100),
    injury_comment VARCHAR(100),
    disabled_list VARCHAR(100),
    start_date_time VARCHAR(100),
    end_date_time VARCHAR(100),
    season_id INT NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE media (
    id VARCHAR(100) NOT NULL,
    object_id INT,
    source_id INT,
    revision_id INT,
    media_type VARCHAR(100),
    publisher_id INT NOT NULL,
    date_time VARCHAR(100),
    credit_id INT NOT NULL,
    db_loading_date_time VARCHAR(100),
    creation_location_id INT NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE affiliations_media (
    affiliation_id INT NOT NULL,
    media_id INT NOT NULL
)
GO

CREATE TABLE affiliations_teams (
    affiliation_id INT NOT NULL,
    team_id INT NOT NULL,
    PRIMARY KEY (affiliation_id, team_id)
)
GO

CREATE TABLE american_football_event_states (
    id primary_id NOT NULL,
    chronology VARCHAR(100) NOT NULL,
    event_id INT NOT NULL,
    publisher_key VARCHAR(100) NOT NULL,
    period_value INT,
    period_time_elapsed VARCHAR(100),
    period_time_remaining VARCHAR(100),
    clock_state VARCHAR(100),
    down INT,
    team_in_possession_id INT NOT NULL,
    distance_for_1st_down INT,
    field_side VARCHAR(100),
    field_line INT,
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_action_plays (
    id primary_id NOT NULL,
    american_football_event_state_id INT NOT NULL,
    sequence_number INT,
    play_type VARCHAR(100),
    score_attempt_type VARCHAR(100),
    drive_result VARCHAR(100),
    points INT,
    comment VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_action_participants (
    id primary_id NOT NULL,
    play_id INT NOT NULL,
    person_id INT NOT NULL,
    participant_role VARCHAR(100) NOT NULL,
    score_type VARCHAR(100),
    field_line INT,
    yardage INT,
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_defensive_stats (
    id primary_id NOT NULL,
    tackles_total VARCHAR(100),
    tackles_solo VARCHAR(100),
    tackles_assists VARCHAR(100),
    interceptions_total VARCHAR(100),
    interceptions_yards VARCHAR(100),
    interceptions_average VARCHAR(100),
    interceptions_longest VARCHAR(100),
    interceptions_touchdown VARCHAR(100),
    quarterback_hurries VARCHAR(100),
    sacks_total VARCHAR(100),
    sacks_yards VARCHAR(100),
    passes_defensed VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_down_progress_stats (
    id primary_id NOT NULL,
    first_downs_total VARCHAR(100),
    first_downs_pass VARCHAR(100),
    first_downs_run VARCHAR(100),
    first_downs_penalty VARCHAR(100),
    conversions_third_down VARCHAR(100),
    conversions_third_down_attempts VARCHAR(100),
    conversions_third_down_percentage VARCHAR(100),
    conversions_fourth_down VARCHAR(100),
    conversions_fourth_down_attempts VARCHAR(100),
    conversions_fourth_down_percentage VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_fumbles_stats (
    id primary_id NOT NULL,
    fumbles_committed VARCHAR(100),
    fumbles_forced VARCHAR(100),
    fumbles_recovered VARCHAR(100),
    fumbles_lost VARCHAR(100),
    fumbles_yards_gained VARCHAR(100),
    fumbles_own_committed VARCHAR(100),
    fumbles_own_recovered VARCHAR(100),
    fumbles_own_lost VARCHAR(100),
    fumbles_own_yards_gained VARCHAR(100),
    fumbles_opposing_committed VARCHAR(100),
    fumbles_opposing_recovered VARCHAR(100),
    fumbles_opposing_lost VARCHAR(100),
    fumbles_opposing_yards_gained VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_offensive_stats (
    id primary_id NOT NULL,
    offensive_plays_yards VARCHAR(100),
    offensive_plays_number VARCHAR(100),
    offensive_plays_average_yards_per VARCHAR(100),
    possession_duration VARCHAR(100),
    turnovers_giveaway VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_passing_stats (
    id primary_id NOT NULL,
    passes_attempts VARCHAR(100),
    passes_completions VARCHAR(100),
    passes_percentage VARCHAR(100),
    passes_yards_gross VARCHAR(100),
    passes_yards_net VARCHAR(100),
    passes_yards_lost VARCHAR(100),
    passes_touchdowns VARCHAR(100),
    passes_touchdowns_percentage VARCHAR(100),
    passes_interceptions VARCHAR(100),
    passes_interceptions_percentage VARCHAR(100),
    passes_longest VARCHAR(100),
    passes_average_yards_per VARCHAR(100),
    passer_rating VARCHAR(100),
    receptions_total VARCHAR(100),
    receptions_yards VARCHAR(100),
    receptions_touchdowns VARCHAR(100),
    receptions_first_down VARCHAR(100),
    receptions_longest VARCHAR(100),
    receptions_average_yards_per VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_penalties_stats (
    id primary_id NOT NULL,
    penalties_total VARCHAR(100),
    penalty_yards VARCHAR(100),
    penalty_first_downs VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_rushing_stats (
    id primary_id NOT NULL,
    rushes_attempts VARCHAR(100),
    rushes_yards VARCHAR(100),
    rushes_touchdowns VARCHAR(100),
    rushing_average_yards_per VARCHAR(100),
    rushes_first_down VARCHAR(100),
    rushes_longest VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_sacks_against_stats (
    id primary_id NOT NULL,
    sacks_against_yards VARCHAR(100),
    sacks_against_total VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_scoring_stats (
    id primary_id NOT NULL,
    touchdowns_total VARCHAR(100),
    touchdowns_passing VARCHAR(100),
    touchdowns_rushing VARCHAR(100),
    touchdowns_special_teams VARCHAR(100),
    touchdowns_defensive VARCHAR(100),
    extra_points_attempts VARCHAR(100),
    extra_points_made VARCHAR(100),
    extra_points_missed VARCHAR(100),
    extra_points_blocked VARCHAR(100),
    field_goal_attempts VARCHAR(100),
    field_goals_made VARCHAR(100),
    field_goals_missed VARCHAR(100),
    field_goals_blocked VARCHAR(100),
    safeties_against VARCHAR(100),
    two_point_conversions_attempts VARCHAR(100),
    two_point_conversions_made VARCHAR(100),
    touchbacks_total VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE american_football_special_teams_stats (
    id primary_id NOT NULL,
    returns_punt_total VARCHAR(100),
    returns_punt_yards VARCHAR(100),
    returns_punt_average VARCHAR(100),
    returns_punt_longest VARCHAR(100),
    returns_punt_touchdown VARCHAR(100),
    returns_kickoff_total VARCHAR(100),
    returns_kickoff_yards VARCHAR(100),
    returns_kickoff_average VARCHAR(100),
    returns_kickoff_longest VARCHAR(100),
    returns_kickoff_touchdown VARCHAR(100),
    returns_total VARCHAR(100),
    returns_yards VARCHAR(100),
    punts_total VARCHAR(100),
    punts_yards_gross VARCHAR(100),
    punts_yards_net VARCHAR(100),
    punts_longest VARCHAR(100),
    punts_inside_20 VARCHAR(100),
    punts_inside_20_percentage VARCHAR(100),
    punts_average VARCHAR(100),
    punts_blocked VARCHAR(100),
    touchbacks_total VARCHAR(100),
    touchbacks_total_percentage VARCHAR(100),
    touchbacks_kickoffs VARCHAR(100),
    touchbacks_kickoffs_percentage VARCHAR(100),
    touchbacks_punts VARCHAR(100),
    touchbacks_punts_percentage VARCHAR(100),
    touchbacks_interceptions VARCHAR(100),
    touchbacks_interceptions_percentage VARCHAR(100),
    fair_catches VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE baseball_action_pitches (
    id primary_id NOT NULL,
    baseball_event_state_id INT,
    baseball_defensive_group_id INT,
    umpire_call VARCHAR(100),
    pitch_location VARCHAR(100),
    pitch_type VARCHAR(100),
    pitch_velocity INT,
    comment TEXT,
    trajectory_coordinates VARCHAR(100),
    trajectory_formula VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE baseball_action_contact_details (
    id primary_id NOT NULL,
    baseball_action_pitch_id INT NOT NULL,
    location VARCHAR(100),
    strength VARCHAR(100),
    velocity INT,
    comment TEXT,
    trajectory_coordinates VARCHAR(100),
    trajectory_formula VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE baseball_event_states (
    id primary_id NOT NULL,
    current TINYINT NOT NULL,
    event_id INT NOT NULL,
    sequence_number INT NOT NULL,
    at_bat_number INT,
    inning_value INT,
    inning_half VARCHAR(100),
    outs INT,
    balls INT,
    strikes INT,
    runner_on_first_id INT,
    runner_on_second_id INT,
    runner_on_third_id INT,
    runner_on_first TINYINT,
    runner_on_second TINYINT,
    runner_on_third TINYINT,
    runs_this_inning_half INT,
    pitcher_id INT,
    batter_id INT,
    batter_side VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE baseball_action_plays (
    id primary_id NOT NULL,
    baseball_event_state_id INT NOT NULL,
    play_type VARCHAR(100),
    notation VARCHAR(100),
    notation_yaml TEXT,
    baseball_defensive_group_id INT,
    comment VARCHAR(100),
    runner_on_first_advance INT,
    runner_on_second_advance INT,
    runner_on_third_advance INT,
    outs_recorded INT,
    rbi INT,
    runs_scored INT,
    earned_runs_scored VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE baseball_action_substitutions (
    id primary_id NOT NULL,
    baseball_event_state_id INT NOT NULL,
    sequence_number INT,
    person_type VARCHAR(100),
    person_original_id INT,
    person_original_position_id INT NOT NULL,
    person_original_lineup_slot INT,
    person_replacing_id INT,
    person_replacing_position_id INT NOT NULL,
    person_replacing_lineup_slot INT,
    substitution_reason VARCHAR(100),
    comment VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE baseball_defensive_group (
    id primary_id NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE baseball_defensive_players (
    id primary_id NOT NULL,
    baseball_defensive_group_id INT,
    player_id INT,
    position_id INT,
    PRIMARY KEY (id)
)
GO

CREATE TABLE baseball_defensive_stats (
    id primary_id NOT NULL,
    double_plays VARCHAR(100),
    triple_plays VARCHAR(100),
    putouts VARCHAR(100),
    assists VARCHAR(100),
    errors VARCHAR(100),
    fielding_percentage VARCHAR(100),
    defensive_average VARCHAR(100),
    errors_passed_ball VARCHAR(100),
    errors_catchers_interference VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE baseball_offensive_stats (
    id primary_id NOT NULL,
    average VARCHAR(100),
    runs_scored VARCHAR(100),
    at_bats VARCHAR(100),
    hits VARCHAR(100),
    rbi VARCHAR(100),
    total_bases VARCHAR(100),
    slugging_percentage VARCHAR(100),
    bases_on_balls VARCHAR(100),
    strikeouts VARCHAR(100),
    left_on_base VARCHAR(100),
    left_in_scoring_position VARCHAR(100),
    singles VARCHAR(100),
    doubles VARCHAR(100),
    triples VARCHAR(100),
    home_runs VARCHAR(100),
    grand_slams VARCHAR(100),
    at_bats_per_rbi VARCHAR(100),
    plate_appearances_per_rbi VARCHAR(100),
    at_bats_per_home_run VARCHAR(100),
    plate_appearances_per_home_run VARCHAR(100),
    sac_flies VARCHAR(100),
    sac_bunts VARCHAR(100),
    grounded_into_double_play VARCHAR(100),
    moved_up VARCHAR(100),
    on_base_percentage VARCHAR(100),
    stolen_bases VARCHAR(100),
    stolen_bases_caught VARCHAR(100),
    stolen_bases_average VARCHAR(100),
    hit_by_pitch VARCHAR(100),
    defensive_interferance_reaches VARCHAR(100),
    on_base_plus_slugging VARCHAR(100),
    plate_appearances VARCHAR(100),
    hits_extra_base VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE baseball_pitching_stats (
    id primary_id NOT NULL,
    runs_allowed VARCHAR(100),
    home_runs_allowed VARCHAR(100),
    innings_pitched VARCHAR(100),
    hits VARCHAR(100),
    earned_runs VARCHAR(100),
    unearned_runs VARCHAR(100),
    bases_on_balls VARCHAR(100),
    bases_on_balls_intentional VARCHAR(100),
    strikeouts VARCHAR(100),
    strikeout_to_bb_ratio VARCHAR(100),
    number_of_pitches VARCHAR(100),
    era VARCHAR(100),
    inherited_runners_scored VARCHAR(100),
    pick_offs VARCHAR(100),
    errors_hit_with_pitch VARCHAR(100),
    errors_wild_pitch VARCHAR(100),
    balks VARCHAR(100),
    wins VARCHAR(100),
    losses VARCHAR(100),
    saves VARCHAR(100),
    shutouts VARCHAR(100),
    games_complete VARCHAR(100),
    games_finished VARCHAR(100),
    winning_percentage VARCHAR(100),
    event_credit VARCHAR(100),
    save_credit VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE basketball_defensive_stats (
    id primary_id NOT NULL,
    steals_total VARCHAR(100),
    steals_per_game VARCHAR(100),
    blocks_total VARCHAR(100),
    blocks_per_game VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE basketball_event_states (
    id VARCHAR(100) NOT NULL,
    chronology VARCHAR(100),
    event_id INT,
    publisher_key VARCHAR(100),
    period_value VARCHAR(100),
    period_time_elapsed VARCHAR(100),
    period_time_remaining VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE basketball_offensive_stats (
    id primary_id NOT NULL,
    field_goals_made INT,
    field_goals_attempted INT,
    field_goals_percentage VARCHAR(100),
    field_goals_per_game VARCHAR(100),
    field_goals_attempted_per_game VARCHAR(100),
    field_goals_percentage_adjusted VARCHAR(100),
    three_pointers_made INT,
    three_pointers_attempted INT,
    three_pointers_percentage VARCHAR(100),
    three_pointers_per_game VARCHAR(100),
    three_pointers_attempted_per_game VARCHAR(100),
    free_throws_made VARCHAR(100),
    free_throws_attempted VARCHAR(100),
    free_throws_percentage VARCHAR(100),
    free_throws_per_game VARCHAR(100),
    free_throws_attempted_per_game VARCHAR(100),
    points_scored_total VARCHAR(100),
    points_scored_per_game VARCHAR(100),
    assists_total VARCHAR(100),
    assists_per_game VARCHAR(100),
    turnovers_total VARCHAR(100),
    turnovers_per_game VARCHAR(100),
    points_scored_off_turnovers VARCHAR(100),
    points_scored_in_paint VARCHAR(100),
    points_scored_on_second_chance VARCHAR(100),
    points_scored_on_fast_break VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE basketball_rebounding_stats (
    id primary_id NOT NULL,
    rebounds_total VARCHAR(100),
    rebounds_per_game VARCHAR(100),
    rebounds_defensive VARCHAR(100),
    rebounds_offensive VARCHAR(100),
    team_rebounds_total VARCHAR(100),
    team_rebounds_per_game VARCHAR(100),
    team_rebounds_defensive VARCHAR(100),
    team_rebounds_offensive VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE core_person_stats (
    id primary_id NOT NULL,
    time_played INT,
    events_played INT,
    events_started INT,
    position_id INT NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE core_stats (
    id primary_id NOT NULL,
    score VARCHAR(100),
    score_opposing VARCHAR(100),
    score_attempts VARCHAR(100),
    score_attempts_opposing VARCHAR(100),
    score_percentage VARCHAR(100),
    score_percentage_opposing VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE display_names (
    id primary_id NOT NULL,
    language VARCHAR(100) NOT NULL,
    entity_id INT NOT NULL,
    entity_type VARCHAR(100) NOT NULL,
    full_name VARCHAR(100),
    first_name VARCHAR(100),
    middle_name VARCHAR(100),
    last_name VARCHAR(100),
    alias VARCHAR(100),
    abbreviation VARCHAR(100),
    short_name VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE document_contents (
    id primary_id NOT NULL,
    document_id INT NOT NULL,
    sportsml TEXT,
    abstract TEXT,
    PRIMARY KEY (id)
)
GO

CREATE TABLE document_fixtures_events (
    id primary_id NOT NULL,
    document_fixture_id INT NOT NULL,
    event_id INT NOT NULL,
    latest_document_id INT NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE document_packages (
    id VARCHAR(100) NOT NULL,
    package_key VARCHAR(100),
    package_name VARCHAR(100),
    date_time DATETIME,
    PRIMARY KEY (id)
)
GO

CREATE TABLE document_package_entry (
    id VARCHAR(100) NOT NULL,
    document_package_id INT NOT NULL,
    rank VARCHAR(100),
    document_id INT NOT NULL,
    headline VARCHAR(100),
    short_headline VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE media_captions (
    id VARCHAR(100) NOT NULL,
    media_id INT NOT NULL,
    caption_type VARCHAR(100),
    caption VARCHAR(100),
    caption_author_id INT NOT NULL,
    language VARCHAR(100),
    caption_size VARCHAR(100),
    PRIMARY KEY (id)
)
GO

/* identifies the relationship between a document and its zero-or-more Media Objects */

CREATE TABLE documents_media (
    id VARCHAR(100) NOT NULL,
    document_id INT NOT NULL,
    media_id INT NOT NULL,
    media_caption_id INT NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE events_documents (
    event_id INT NOT NULL,
    document_id INT NOT NULL
)
GO

CREATE TABLE events_media (
    event_id INT NOT NULL,
    media_id INT NOT NULL
)
GO

CREATE TABLE sub_seasons (
    id primary_id NOT NULL,
    sub_season_key VARCHAR(100),
    season_id INT NOT NULL,
    sub_season_type VARCHAR(100) NOT NULL,
    start_date_time VARCHAR(100),
    end_date_time VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE events_sub_seasons (
    event_id INT NOT NULL,
    sub_season_id INT NOT NULL,
    PRIMARY KEY (event_id, sub_season_id)
)
GO

CREATE TABLE ice_hockey_defensive_stats (
    id VARCHAR(100) NOT NULL,
    shots_power_play_allowed VARCHAR(100),
    shots_penalty_shot_allowed VARCHAR(100),
    goals_power_play_allowed VARCHAR(100),
    goals_penalty_shot_allowed VARCHAR(100),
    goals_against_average VARCHAR(100),
    saves VARCHAR(100),
    save_percentage VARCHAR(100),
    penalty_killing_amount VARCHAR(100),
    penalty_killing_percentage VARCHAR(100),
    shots_blocked VARCHAR(100),
    takeaways VARCHAR(100),
    shutouts VARCHAR(100),
    minutes_penalty_killing VARCHAR(100),
    hits VARCHAR(100)
)
GO

CREATE TABLE ice_hockey_event_states (
    id VARCHAR(100) NOT NULL,
    chronology VARCHAR(100),
    event_id INT,
    publisher_key VARCHAR(100),
    period_value VARCHAR(100),
    period_time_elapsed VARCHAR(100),
    period_time_remaining VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE ice_hockey_offensive_stats (
    id VARCHAR(100) NOT NULL,
    goals_game_winning VARCHAR(100),
    goals_game_tying VARCHAR(100),
    goals_power_play VARCHAR(100),
    goals_short_handed VARCHAR(100),
    goals_even_strength VARCHAR(100),
    goals_empty_net VARCHAR(100),
    goals_overtime VARCHAR(100),
    goals_shootout VARCHAR(100),
    goals_penalty_shot VARCHAR(100),
    assists VARCHAR(100),
    points VARCHAR(100),
    power_play_amount VARCHAR(100),
    power_play_percentage VARCHAR(100),
    shots_penalty_shot_taken VARCHAR(100),
    shots_penalty_shot_missed VARCHAR(100),
    shots_penalty_shot_percentage VARCHAR(100),
    giveaways VARCHAR(100),
    minutes_power_play VARCHAR(100),
    faceoff_wins VARCHAR(100),
    faceoff_losses VARCHAR(100),
    faceoff_win_percentage VARCHAR(100),
    scoring_chances VARCHAR(100)
)
GO

CREATE TABLE key_aliases (
    id primary_id NOT NULL,
    publisher_id INT NOT NULL,
    key_type VARCHAR(100),
    key_value VARCHAR(100),
    id_value INT,
    common_key VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE latest_revisions (
    id primary_id NOT NULL,
    revision_id INT NOT NULL,
    latest_document_id INT NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE media_contents (
    id VARCHAR(100) NOT NULL,
    media_id INT NOT NULL,
    object VARCHAR(100),
    format VARCHAR(100),
    mime_type VARCHAR(100),
    height VARCHAR(100),
    width VARCHAR(100),
    duration VARCHAR(100),
    file_size VARCHAR(100),
    resolution VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE media_keywords (
    id VARCHAR(100) NOT NULL,
    keyword VARCHAR(100),
    media_id INT NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE standings (
    id INT NOT NULL,
    affiliation_id INT NOT NULL,
    standing_type VARCHAR(100),
    sub_season_id VARCHAR(100) NOT NULL,
    last_updated VARCHAR(100),
    duration_scope VARCHAR(100),
    competition_scope VARCHAR(100),
    competition_scope_id VARCHAR(100),
    alignment_scope VARCHAR(100),
    site_scope VARCHAR(100),
    scoping_label VARCHAR(100),
    publisher_id INT NOT NULL,
    source VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE standing_subgroups (
    id VARCHAR(100) NOT NULL,
    standing_id INT NOT NULL,
    affiliation_id INT NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE outcome_totals (
    id VARCHAR(100) NOT NULL,
    standing_subgroup_id INT NOT NULL,
    outcome_holder_type VARCHAR(100),
    outcome_holder_id INT,
    rank VARCHAR(100),
    wins VARCHAR(100),
    losses VARCHAR(100),
    ties VARCHAR(100),
    undecideds VARCHAR(100),
    winning_percentage VARCHAR(100),
    points_scored_for VARCHAR(100),
    points_scored_against VARCHAR(100),
    points_difference VARCHAR(100),
    standing_points VARCHAR(100),
    streak_type VARCHAR(100),
    streak_duration VARCHAR(100),
    streak_total VARCHAR(100),
    streak_start DATETIME,
    streak_end DATETIME,
    PRIMARY KEY (id)
)
GO

CREATE TABLE participants_events (
    id primary_id NOT NULL,
    participant_id INT,
    participant_type VARCHAR(100),
    event_id INT,
    alignment VARCHAR(100),
    score VARCHAR(100),
    event_outcome VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE periods (
    id primary_id NOT NULL,
    participant_event_id INT,
    period_value VARCHAR(100),
    score VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE person_event_metadata (
    id primary_id NOT NULL,
    status VARCHAR(100),
    weight VARCHAR(100),
    role_id INT NOT NULL,
    position_id INT NOT NULL,
    PRIMARY KEY (id)
)
GO

CREATE TABLE persons_documents (
    person_id INT NOT NULL,
    document_id INT NOT NULL,
    PRIMARY KEY (person_id, document_id)
)
GO

CREATE TABLE persons_media (
    person_id INT NOT NULL,
    media_id INT NOT NULL
)
GO

CREATE TABLE stats (
    id primary_id NOT NULL,
    stat_repository_type VARCHAR(100),
    stat_repository_id INT NOT NULL,
    stat_holder_type VARCHAR(100),
    stat_holder_id INT,
    stat_coverage_type VARCHAR(100),
    stat_coverage_id INT,
    final TINYINT NOT NULL,
    PRIMARY KEY (id)
)
GO

/* Especially for Tennis. sub_period is for game, period is for set, score is for total number of sets won. */

CREATE TABLE sub_periods (
    id primary_id NOT NULL,
    period_id INT,
    sub_period_value VARCHAR(100),
    score VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE team_affiliation_phases (
    id primary_id NOT NULL,
    team_id INT NOT NULL,
    start_season_id INT NOT NULL,
    end_season_id INT NOT NULL,
    affiliation_id INT NOT NULL,
    start_date_time VARCHAR(100),
    end_date_time VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE team_american_football_stats (
    id primary_id NOT NULL,
    yards_per_attempt VARCHAR(100),
    average_starting_position VARCHAR(100),
    timeouts VARCHAR(100),
    time_of_possession VARCHAR(100),
    turnover_ratio VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE teams_documents (
    team_id INT NOT NULL,
    document_id INT NOT NULL,
    PRIMARY KEY (team_id, document_id)
)
GO

CREATE TABLE teams_media (
    team_id INT NOT NULL,
    media_id INT NOT NULL
)
GO

CREATE TABLE tennis_action_points (
    id VARCHAR(100) NOT NULL,
    sub_period_id VARCHAR(100),
    sequence_number VARCHAR(100),
    win_type VARCHAR(100),
    PRIMARY KEY (id)
)
GO

/* One row per time the ball makes contact with a racquet. Including the first serve and the second serve. */

CREATE TABLE tennis_action_volleys (
    id VARCHAR(100) NOT NULL,
    sequence_number VARCHAR(100),
    tennis_action_points_id INT,
    landing_location VARCHAR(100),
    swing_type VARCHAR(100),
    result VARCHAR(100),
    spin_type VARCHAR(100),
    trajectory_details VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE tennis_event_states (
    id VARCHAR(100) NOT NULL,
    tennis_set VARCHAR(100),
    game VARCHAR(100),
    server_person_id INT,
    server_score VARCHAR(100),
    receiver_person_id INT,
    receiver_score VARCHAR(100),
    service_number VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE tennis_return_stats (
    id VARCHAR(100) NOT NULL,
    returns_played VARCHAR(100),
    matches_played VARCHAR(100),
    first_service_return_points_won VARCHAR(100),
    first_service_return_points_won_pct VARCHAR(100),
    second_service_return_points_won VARCHAR(100),
    second_service_return_points_won_pct VARCHAR(100),
    return_games_played VARCHAR(100),
    return_games_won VARCHAR(100),
    return_games_won_pct VARCHAR(100),
    break_points_played VARCHAR(100),
    break_points_converted VARCHAR(100),
    break_points_converted_pct VARCHAR(100)
)
GO

CREATE TABLE tennis_service_stats (
    id VARCHAR(100) NOT NULL,
    services_played VARCHAR(100),
    matches_played VARCHAR(100),
    aces VARCHAR(100),
    first_services_good VARCHAR(100),
    first_services_good_pct VARCHAR(100),
    first_service_points_won VARCHAR(100),
    first_service_points_won_pct VARCHAR(100),
    second_service_points_won VARCHAR(100),
    second_service_points_won_pct VARCHAR(100),
    service_games_played VARCHAR(100),
    service_games_won VARCHAR(100),
    service_games_won_pct VARCHAR(100),
    break_points_played VARCHAR(100),
    break_points_saved VARCHAR(100),
    break_points_saved_pct VARCHAR(100)
)
GO

CREATE TABLE wagering_moneylines (
    id primary_id NOT NULL,
    bookmaker_key VARCHAR(100),
    event_id INT NOT NULL,
    date_time VARCHAR(100),
    team_id INT NOT NULL,
    person_id INT,
    rotation_key VARCHAR(100),
    comment VARCHAR(100),
    vigorish VARCHAR(100),
    line VARCHAR(100),
    line_opening VARCHAR(100),
    prediction VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE wagering_odds_lines (
    id primary_id NOT NULL,
    bookmaker_key VARCHAR(100),
    event_id INT NOT NULL,
    date_time VARCHAR(100),
    team_id INT NOT NULL,
    person_id INT,
    rotation_key VARCHAR(100),
    comment VARCHAR(100),
    numerator VARCHAR(100),
    denominator VARCHAR(100),
    prediction VARCHAR(100),
    payout_calculation VARCHAR(100),
    payout_amount VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE wagering_runlines (
    id primary_id NOT NULL,
    bookmaker_key VARCHAR(100),
    event_id INT NOT NULL,
    date_time VARCHAR(100),
    team_id INT NOT NULL,
    person_id INT,
    rotation_key VARCHAR(100),
    comment VARCHAR(100),
    vigorish VARCHAR(100),
    line VARCHAR(100),
    line_opening VARCHAR(100),
    line_value VARCHAR(100),
    prediction VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE wagering_straight_spread_lines (
    id primary_id NOT NULL,
    bookmaker_key VARCHAR(100),
    event_id INT NOT NULL,
    date_time VARCHAR(100),
    team_id INT NOT NULL,
    person_id INT,
    rotation_key VARCHAR(100),
    comment VARCHAR(100),
    vigorish VARCHAR(100),
    line_value VARCHAR(100),
    line_value_opening VARCHAR(100),
    prediction VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE wagering_total_score_lines (
    id primary_id NOT NULL,
    bookmaker_key VARCHAR(100),
    event_id INT NOT NULL,
    date_time VARCHAR(100),
    team_id INT NOT NULL,
    person_id INT,
    rotation_key VARCHAR(100),
    comment VARCHAR(100),
    vigorish VARCHAR(100),
    line_over VARCHAR(100),
    line_under VARCHAR(100),
    total VARCHAR(100),
    total_opening VARCHAR(100),
    prediction VARCHAR(100),
    PRIMARY KEY (id)
)
GO

CREATE TABLE weather_conditions (
    id primary_id NOT NULL,
    event_id INT NOT NULL,
    temperature VARCHAR(100),
    humidity VARCHAR(100),
    clouds VARCHAR(100),
    wind_direction VARCHAR(100),
    wind_velocity VARCHAR(100),
    PRIMARY KEY (id)
)
GO

/*============================================================================*/
/* Indexes                                                                    */
/*============================================================================*/

CREATE INDEX
    IDX_sub_seasons_1 ON sub_seasons (sub_season_key);
GO


/* -- END OF SCRIPT -- */