Specification

Patch Release: 10/02/2011 (Draft 27) -- stats and event states table additions

• Schematic Diagram of Tables (JPEG)
• HF (Happy Fish format)
• SQL: MySQL 3 | SQL Server 2000 (and SQL Server Index File) | PostgreSQL 8
• What's New (in Draft 26):
  • series_index field added to events table
  • city, state, area, country fields added to locations table
  • motor_racing_event_stats table added
  • phase_type and sub_phase_type fields added to person_phases table
• Transition SQL from SportsDB 26 to 27: MySQL 3 | SQL Server 2000 | PostgreSQL 8

Patch Release: 05/12/2010 (Draft 26) -- stats and event states table additions

• Schematic Diagram of Tables (JPEG)
• HF (Happy Fish format)
• SQL: MySQL 3 | SQL Server 2000 (and SQL Server Index File) | PostgreSQL 8
• What's New (in Draft 26):
  • stats table additions for baseball and basketball
  • document_id field addition to all event_states tables to allow tracking of latest document to add to table
• Transition SQL from SportsDB 25 to 26: MySQL 3 | SQL Server 2000 | PostgreSQL 8

Patch Release: 03/29/2010 (Draft 25) -- additions to american football event states, periods, participants_events tables plus addition of new sports_property table

• Schematic Diagram of Tables (JPEG)
• Generated Documentation (rather sparse for now, but some fields filled in)
• HF (Happy Fish format)
• SQL: MySQL 3 | SQL Server 2000 (and SQL Server Index File) | PostgreSQL 8
• What's New (in Draft 25):
  • american football event states table field additions relating to scoring
  • periods table field additions relating to sub scores
  • new sports_property table to contain extra information relating to things like sites
• Transition SQL from SportsDB 24 to 25: MySQL 3 | SQL Server 2000 | PostgreSQL 8

Patch Release: 12/01/2009 (Draft 24) -- many additions to ice hockey and american football stats and events tables

• Schematic Diagram of Tables (JPEG)
• Generated Documentation (rather sparse for now, but some fields filled in)
• HF (Happy Fish format)
• SQL: MySQL 3 | SQL Server 2000 (and SQL Server Index File) | PostgreSQL 8
• What's New (in Draft 24):
  • many additions to existing ice hockey stats tables and events tables
  • new ice_hockey_faceoff_stats and ice_hockey_time_on_ice_stats tables
  • some additions to american football actions tables
  • many additions to american football stats tables
• Transition SQL from SportsDB 23 to 24: MySQL 3 | SQL Server 2000 | PostgreSQL 8

Patch Release: 4/1/2009 (Draft 23) -- some minor touchups

• Schematic Diagram of Tables (JPEG)
• Generated Documentation (rather sparse for now, but some fields filled in)
• HF (Happy Fish format)
• SQL: MySQL 3 | SQL Server 2000 (and SQL Server Index File) | PostgreSQL 8
• What's New (in Draft 23):
  • Added root_id field to affiliation_phases table
  • Added abstract_blob field to document_contents table
  • Adjusted sequence_number field in baseball_event_states, baseball_action_pitches, and baseball_action_substitutions to be DECIMAL(3,1) rather than INTEGER
• Transition SQL from SportsDB 22 to 23: MySQL 3 | SQL Server 2000 | PostgreSQL 8

Latest Release: 03/17/2009 (Drafts 21 + 22)

• Schematic Diagram of Tables (JPEG)
• Generated Documentation (rather sparse for now, but some fields filled in)
• HF (Happy Fish format)
• SQL: MySQL 3 | SQL Server 2000 (and SQL Server Index File) | PostgreSQL 8
• What's New (in Draft 22, as well as the interim (not previously released) Draft 21):
  • Added stat_membership_type and stat_membership_id fields to stats table
  • Added indexes for stat_coverage_type and stat_coverage_id fields of stats table
  • Added sportsml_blob field to document_contents table, for users who prefer to store entire SportsML source files, rather than just pathnames to the files
  • Added fields to ice_hockey_action_plays table:
    • penalty_type
    • penalty_length
    • penalty_code
    • recipient_type
    • team_id
    • strength
    • location_x
    • location_y
    • location_zone
    • shootout_shot_order
    • goal_order
    • shot_type
    • shot_distance
    • goal_zone
    • empty_net
    • goal_awarded
  • Added fields to ice_hockey_action_participants table:
    • goals_to_date
    • assists_to_date
    • points_to_date

  • ---

  • New table called rankings, for NCAA polls, etc.
  • New table called penalty_stats
  • Added fields goals_allowed and shots_allowed to ice_hockey_defensive_stats
  • Adjusted length of comment fields in wagering tables
  • Added time_certainty field to events table
  • Folded all fields from core_stats_player table into core_stats table
• Transition SQL from SportsDB 20 to 21: MySQL 3 | SQL Server 2000 | PostgreSQL 8
• Transition SQL from SportsDB 21 to 22: MySQL 3 | SQL Server 2000 | PostgreSQL 8

Previous Releases

• Specs and Documentation for Previous Releases

Suggestions for Next Draft

• Remove baseball_action_substitutions.sequence_number field

Other Suggestions

• remove publishers.publisher_name and use display_names instead. Same with fixture display_name
• Sharpen all the field definitions and NULL/NOT NULL properties
• Improve baseball_action_contact_details table?
• Add a default_address_id field to locations table?
• Analyze the whole composite-games hierarchy (for Olympics-style competitions)
• Add to db_info table, to hold configuration values such as "Document Path Prefix"