The Sports Standards Alliance

Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.

Specifications for Previous Releases

Draft 19, 05/27/2008
- 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 | PostgreSQL 8
- What's New (in Draft 19):
  - Lengthened comment column for these tables:
    - american_football_action_plays
    - baseball_action_plays
    - baseball_action_substitutions
    - baseball_action_contact_details
    - ice_hockey_action_plays
  - Created new tables:
    - awards
    - records
    - soccer_action_fouls
    - soccer_action_plays
    - soccer_action_participants
    - soccer_action_penalties
    - soccer_action_substitutions
  - Added columns to
    - baseball_action_plays: out_type
    - participants_events: result_effect, score_attempts
    - periods: label, score_attempts, rank
    - sub_periods: score_attempts
  - Adjusted length of column sites.site_key
  - Adjusted several columns in standings, standing_subgroup, and outcome_totals tables
  - Adjusted columns baseball_action_plays.runner_on_*_advance columns to be VARCHAR
- Transition SQL from SportsDB 18 to 19: MySQL 3 | SQL Server 2000 | PostgreSQL 8
Draft 18, 03/30/2008
- 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 | PostgreSQL 8
- What's New (in Draft 17):
  - lengthened positions.abbreviation field
  - added ice_hockey_action_plays and ice_hockey_action_participants tables
  - added new columns to person_phases: entry_reason, exit_reason, selection_level, selection_sublevel, selection_overall
  - modified person_phases.regular_position_id to allow NULLs; lengthened person_phases.regular_position_depth
- What's New (in Draft 18):
  - modified seasons.league_id to allow NULLs
  - added indexes to display_names.entity_id and entity_type columns (very critical for performance!)
  - renamed team_american_football_stats table to be called american_football_team_stats (for consistency)
- Transition SQL from SportsDB 16 to 17 (do this prior to moving up to 18): MySQL 3 | SQL Server 2000 | PostgreSQL 8
- Transition SQL from SportsDB 17 to 18: MySQL 3 | SQL Server 2000 | PostgreSQL 8
Draft 17 (not publicly released; changes listed within Draft 18)
Draft 16, 1/8/2008
- 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 | PostgreSQL 8
- What's New:
  - added score_credit and yards_gained fields to american_football_action_participants
  - added weather_code and temperature_units fields to weather_conditions
  - adjusted position_id field in core_person_stats
  - adjusted ball_type, strike_type, sequence_number, baseball_event_state_id, and baseball_action_play_id fields in baseball_action_pitches
- Transition from SportsDB 15: MySQL 3 | SQL Server 2000 | PostgreSQL 8
Draft 15, 10/12/2007
- JPG
- Generated Documentation (rather sparse for now, but some fields filled in)
- HF (Happy Fish format)
- SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 7
- What's New:
  - fixed foreign-key problem with injury_phases
  - Allowed NULL for injury_phases.season_id column
  - Added injury_phases.phase_type and injury_phases.injury_side columns
  - Added american_football_action_participants.score_credit and american_football_action_participants.yards_gained columns
  - Added context column to these tables:
    - american_football_event_states
    - baseball_event_states
    - basketball_event_states
    - ice_hockey_event_states
    - motor_racing_event_states
    - soccer_event_states
    - tennis_event_states
  - Made american_football_action_plays.comment and baseball_action_plays.comment columns longer
  - Allowed NULL for baseball_action_substitutions.person_original_id and person_original_position_id columns
- Transition SQL from XTOSS14: MySQL 3 | SQL Server 2000 | PostgreSQL 7
Pre-release 14, 7/22/2007
- added column to person_phases: regular_position_depth
- added column to person_event_metadata: health
- added columns to baseball_pitching_stats: singles_allowed, doubles_allowed, triples_allowed
- RENAMED team_affiliation_phases to be just team_phases
- REMOVED teams.league_id (redundant with team_phases data)
- REMOVED key_aliases.alias_key -- redundant
- ALLOWED start_season_id and end_season_id in person_phases and team_phases to be NULL
- ALLOWED role_id and regular_position_id in person_phases to be NULL. Made membership_type field be NOT NULL.
Pre-release 13, 5/17/2007
- JPG (mirrored JPG -- may load faster)
- Generated Documentation (rather sparse for now, but some fields filled in)
- HF (Happy Fish format)
- What's New:
  - SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 7
  - Removed ice_hockey_event_states.publisher_key
  - Added columns to ice_hockey_defensive_stats: goals-shootout-allowed, shots-shootout-allowed, goals-empty-net-allowed, goals-short-handed-allowed
  - New table ice_hockey_player_stats with one column: plus_minus
  - New table basketball_team_stats with columns timeouts_left, largest_lead, fouls_total, turnover_margin
  - Changed core_person_stats.time_played to core_person_stats.time_played_event
  - Added columns to core_person_stats: time_played_total, time_played_event_average
  - Added columns to display_names: prefix, suffix
  - Changed baseball_action_plays.comment and american_football_action_plays.comment from VARCHAR 100 to VARCHAR 200
  - Added columns to person_event_metadata: lineup-slot and lineup-slot-sequence
  - Remove columns from affiliations: start_season_id, start_date_time, end_season_id, end_date_time
  - New table affiliation_phases with columns: id, affiliation_id, ancestor_affiliation_id, start_season_id, start_date_time, end_season_id, end_date_time
Pre-release Draft 12, 3/21/2007
- JPG (mirrored JPG -- may load faster)
- Generated Documentation (rather sparse for now, but some fields filled in)
- HF (Happy Fish format)
- What's New in Draft 12
  - SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 7
  - Added db_info table, to store XTOSS Schema Version Number (and other future "sysinfo" properties)
  - Changed affiliations.tier field to be to affilations.affiliation_type
  - Added (optional) team_id FK to person_event_metadata
  - Dropped current_injury_phase_id, current_team_phase_id, current_league_phase_id from persons table
  - Modified datatypes for three baseball_*_stats tables, to be more granular
  - Made *_event_states tables more consistent, with event_id, current_state, and sequence_number
Pre-release Draft 11, 3/12/2007
- JPG (mirrored JPG -- may load faster)
- Generated Documentation (rather sparse for now, but some fields filled in)
- HF (Happy Fish format)
- SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 7
- What's New in Draft 11
  - Included indexes on most all relevant fields
  - Included Foreign Key enforcement on most all relevant fields
  - Expanded baseball play-by-play
  - Made baseball and american-football event_state tables cleaner (will do same for other sports soon)
  - changed "stats.final" boolean to a stats.context varchar
  - Combined teams_phases and person_league_phases to person_phases; made it polymorphic
  - Added phase_status to teams_affiliations-phases -- active | inactive
  - Added publisher_id to affiliations
  - Allow teams.league_id to be NULL
  - Added events.last_update
  - Add role_id to team_affiliation_phases (e.g., identifies the relationship between a minor league team and parent MLB ballclub)
  - Added event_id and person_id FK fields to person_event_metadata table
  - Added rank to participant_events
  - Changed bookmaker_key to bookmaker_id FK
  - Added bookmakers table (id | bookmaker_key | publisher_id | location_id) # can also have display_names
  - Added Soccer Stats and Event State
  - Added Motor Racing Stats and Event State
  - other various improvements
Pre-release Draft 10, 6/26/2006
- JPG (mirrored JPG -- may load faster)
- Generated Documentation (rather sparse for now, but some fields filled in)
- HF (Happy Fish format)
- SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 7
Pre-release Draft 9, 6/12/2006
- JPG (mirrored JPG -- may load faster)
- Generated Documentation (rather sparse for now, but some fields filled in)
- HF (Happy Fish format)
- SQL (MySQL)
- What was new:
  - ```
  New table: baseball_defensive_players:
  		id
  		baseball_defensive_group_id
  		player_id
  		position_id
  
  		Resolved: Keep pitchers OUT of this new table.
  
  	New table: baseball_defensive_group:
  		id
  
  	New column for table baseball_action_plays
  		and baseball_action_pitches: 
  			baseball_defensive_group_id
  
  	New table: display_names (for multilingual person, team and 
  		conference names)
  			id
  			entity_type == person | team | affiliation
  					| site | position
  			entity_id == (either a person_id value or 
  					a team_id value or etc.)
  			language
  			full_name
  			first_name
  			middle_name
  			last_name
  			alias
  			abbreviation
  			short_name
  
  	Removed person_names table (subsumed by new "display_names" table)
  
  	Removed these columns from teams table:
  		first_name
  		last_name
  		full_name
  		alias
  		abbreviation
  		short_name
  
  	Removed these columns from affiliations table:
  		name
  		alias
  		abbreviation
  
  	Removed these columns from sites table:
  		name
  		alias
  
  	Removed this columns from events table:
  		name
  
  	Removed these columns from positions table:
  		abbreviation
  		name
  
  	Firebird DB Compatibility (per this list)
  		Shortened all table names to 32 characters or less
  			american_football_* changed to am_football_*
  			american_football_action_play_participants to  
  				am_football_action_participants
  
  		Changed from generic field names that are reserved Firebird terms
  			(type, role, size, value, set) as follows:
  
  		TABLE				OLD FIELD	NEW FIELD
  		-----------------------------	-------------	---------------------
  		media     			type     	media_type
  		sub-season     			type     	sub_season_type
  		standings     			type     	standings_type
  		person_league_phases		role     	person_role
  		am_football_action_participants	role     	participant_role
  		media_captions     		size     	caption_size
  		media_contents     		size     	file_size
  		periods     			value  		period_value
  		sub_periods     		value  		sub_period_value
  		wagering_runlines     		value		line_value
  		wagering_straight_spread_lines	value     	line_value
  		wagering_straight_spread_lines	value_opening   line_value_opening
  	
```
- Pre-release Draft 8, 3/22/2006
  - JPG (mirrored JPG -- may load faster)
  - Generated Documentation (rather sparse for now, but some fields filled in)
  - HF (Happy Fish format)
  - SQL (MySQL)
  - What was new:
    - Standings tables
    - Document Package tables
    - Redid many of the Events tables
      - Unifies tables
      - Allows for IDs to identify periods and sub_periods
    - Added media tables
- Pre-release Draft 7, 3/2/2006
  - JPG (mirrored JPG -- may load faster)
  - PNG (smaller; loads faster; less crisp to read)
  - HF (Happy Fish format)
  - SQL (MySQL)
  - What Was New
    - Moved entire site to Sourceforge & now saving drafts within Subversion (version control)
    - Various table and field renamings
      - Named affiliation_phases table back to affiliations.
        
        When it's named @affiliation_phases@ it suggests that there's another table called @affiliations@ which it references, which is not the case.
        Change type field to tier in affiliations table, to be more descriptive
      - Renamed doc_classes table over to document_classes, to reflect the sportsml naming.
      - Renamed fixtures table to document_fixtures
        
        Despite the sportsml naming, because "fixtures" is too general, and this jives better with "document_classes". Also, "fixtures" is a loaded Ruby / database term.
      - Changed name of events_teams_subscores table to teams_events_subscores, to match teams_events.
    - Added sub_season_key to sub_seasons table. Maps to @season-type@ sports content code.
    - Add recurring_events table, and added recurring_event_id to events (for recurring event names like "Daytona 500" and "Wimbledon")
    - Added document_contents table
      - To hold the actual sportsml, and an option abstract
    - Combined player_stats, team_stats, and affiliation_stats tables into one fancy "stats" table
      - Thrice polymorphic!
      - Per Ruby and polymorphism conventions, stat_type now holds a table name, and stat_id holds an id value
      - Renamed the old stat_type to be stat_stage (for distinguishing between intermediate results and post-game results, for example)
      - See "How Stats are Tied In" section for more details
    - Normalized all location values
      - For multilingualness's sake, locations table contains only language-independent information
        
        id
        timezone
        latitude
        longitude
        country_code
      - Added birth_location_id, hometown_location_id, residence_location_id, death_location_id to persons table
        
        Removed birth_city, birth_state, birth_country
      - Added location_id to sites table
        
        Replaces country, state_province, city, postal_code, postal_code_extension, timezone, address_1, address_2
      - A location can have zero or more address records:
        
        id
        language
        suite (for apartment number, suite, Post Office Box)
        floor
        building
        street_number
        street_prefix
        street
        street_suffix
        neighborhood
        district
        locality
        county
        region
        postal_code
        country
        address1
        address2
- Pre-release Draft 6, 2/2/2006
- Pre-release Draft 5, 11/7/2005: JPG | HF | SQL (MySQL)
- Pre-release Draft 4, 5/27/2005: PNG | HF | SQL
- Pre-release Draft 3, 4/26/2005: JPG | HF | SQL
- Pre-release Draft 2, 4/18/2005: JPG
- Pre-release Draft 1, 4/14/2005: JPG | HF | SQL

SportsDB Home

Documentation

Specification

Samples

Query Library

SportsCaster Web Service

Specifications for Previous Releases