Lacrosse Scheduler Database Layout - Interactive Visualization

Application Architecture Overview

The SLAMA Tools Scheduler is a comprehensive sports scheduling and management system with 6 major functional areas working together using a shared database of 60+ tables.

📋 Master Data Tables (Dimensions)

Core entities used across all functions:

tblschool tblsport tblgender tblseason tblconference tbllevel

📊 Transactional Tables (Facts)

Primary business data:

tblschedule tblteamrank tblroster tblplayerstats tblgoaliestats

⚙️ Working Tables (Temporary/Staging)

Used during schedule generation:

tblpossiblegamedate tblscheduledate tblschedulebuild tblmatrix tblgamelist

🚫 Constraint Tables

Scheduling rules and restrictions:

tblfieldconflict tblschoolconflict tblfirstgamedate tblschoolexclusion tblrivalry tblschedulingrule

📥 Import/Staging Tables

Imported data before processing:

ref_intake_conflicts ref_import_history 2025_lax_info_conflicts 2026_lax_info_conflicts

📝 Progress Notebook

Notebook UI (navbar): task checklist, notes, tags, and archives (public schema):

tbluserprofile tblschedulingprogresstask tblusertaskprogress tblusernote tblusernotetask

Key Statistics

60+

Database Tables

6

Major Functions

7

Workflow Phases

Dashboard Function

Primary Route: app/routers/dashboard.py

Provides high-level statistics, shows active announcements, and serves as the entry point to other functions.

graph LR Dashboard[Dashboard View] Dashboard --> tblschool[tblschool
School Count] Dashboard --> tblschoolsportconference[tblschoolsportconference
Team Count] Dashboard --> tblschedule[tblschedule
Game Count] Dashboard --> tblannouncement[tblannouncement
Announcements]

Database Tables Used

Table	Role	Access
`tblschool`	Count total schools	Read
`tblschoolsportconference`	Count total teams	Read
`tblschedule`	Count scheduled games	Read
`tblannouncement`	Display system announcements	Read

School Info Function

Primary Routes:

app/routers/schools.py - School CRUD operations
app/routers/teams.py - Team management
app/routers/conferences.py - Conference management

graph TD SchoolInfo[School Info Function] SchoolInfo --> tblschool[tblschool
School Master Data] SchoolInfo --> tblschoolalias[tblschoolalias
Name Matching] SchoolInfo --> tblschoolsportconference[tblschoolsportconference
Team Participation] tblschoolsportconference --> tblsport[tblsport
Sport Types] tblschoolsportconference --> tblgender[tblgender
Gender Categories] tblschoolsportconference --> tblseason[tblseason
Season Definitions] tblschoolsportconference --> tblconference[tblconference
League Structure] SchoolInfo --> tblschedule[tblschedule
Team Schedule View] SchoolInfo --> tblteamrank[tblteamrank
Team Rankings] SchoolInfo --> tblplayerstats[tblplayerstats
Current Stats] SchoolInfo --> archive_historical[archive.tblhistoricalplayerpoints
Historical Stats]

Purpose

Manage school master data
Configure team participation in sports/conferences
View team schedules and statistics
Support name matching during data imports

Scheduling Function

Primary Routes:

app/routers/scheduling.py - Conflict import and management
app/routers/scheduler.py - Schedule generation wizard
app/routers/lax_conflicts.py - Girls lacrosse conflict intake & import

graph TB subgraph "1. Import Phase" Import[CSV Import] --> ref_intake[ref_intake_conflicts
Staging] Import --> ref_history[ref_import_history
Batch Tracking] ref_intake --> field_conflict[tblfieldconflict
Field Unavailable] ref_intake --> school_conflict[tblschoolconflict
School Unavailable] end subgraph "2. Date Pool Generation" dates[tblpossiblegamedate
Available Dates] tblfirstgamedate[tblfirstgamedate
Start Constraints] dates -.constrained by.- tblfirstgamedate end subgraph "3. Matchup Generation" conference[Conference Matchups] --> scheddate[tblscheduledate
Matchups Without Dates] district[District Matchups] --> scheddate rivalry[tblrivalry
Required Games] --> scheddate rank[tblteamrank
Rank-Based] --> scheddate end subgraph "4. Date Assignment" scheddate --> schedbuild[tblschedulebuild
Matchups With Dates] field_conflict -.blocks.- schedbuild school_conflict -.blocks.- schedbuild end subgraph "5. Home/Away Balancer" schedbuild --> balance[Home/Away Assignment] balance --> final[tblschedule
Final Schedule] end subgraph "6. Version Control" final --> version[tblscheduleversion
Snapshot] final --> versiongames[tblscheduleversiongames
Game Details] end subgraph "7. Validation" final --> issues[tblscheduleissue
Detected Problems] end

Workflow Steps

1️⃣ Import Phase

Athletic directors submit conflict data via CSV. Data is staged in ref_intake_conflicts and then processed into tblfieldconflict and tblschoolconflict tables.

2️⃣ Date Pool Generation

System generates all possible game dates in tblpossiblegamedate, respecting constraints from tblfirstgamedate.

3️⃣ Matchup Generation

Algorithm creates team matchups based on conference requirements, rivalries, and ranking-based pairings. Stored in tblscheduledate without dates assigned.

4️⃣ Date Assignment

Engine assigns dates to matchups while avoiding all conflicts. Working data in tblschedulebuild.

5️⃣ Home/Away Balancer

Optimizes home and away game distribution for each team. Final schedule written to tblschedule.

6️⃣ Version Control

Snapshots of schedules saved in tblscheduleversion for rollback capability.

7️⃣ Validation

AI-powered issue detection logs problems in tblscheduleissue with suggested resolutions.

Rankings Function

Primary Route: app/routers/rankings.py

Calculates preseason team rankings using a multi-component algorithm based on historical performance, player experience, and tournament results.

graph TB subgraph "Input Data" schedule[tblschedule
Historical Games] roster[tblroster
Player Info] stats[tblplayerstats
Performance] awards[tblawards
Recognition] tournaments[tbltournamentstanding
Placement] end subgraph "Ranking Algorithm" config[tblpreseasonconfig
Component Weights] schedule --> algorithm[6-Component
Calculation] roster --> algorithm stats --> algorithm awards --> algorithm tournaments --> algorithm config --> algorithm end subgraph "Output" algorithm --> teamrank[tblteamrank
Final Rankings] algorithm --> job[tblrankingjob
Job Tracking] teamrank --> scheduler[Used by Scheduler] teamrank --> reports[Used by Reports] end

Ranking Components (6 Factors)

AGD - Average Goal Differential

WP - Winning Percentage

OWP - Opponent Winning Percentage

OOWP - Opponent's Opponent WP

Player Experience - Returning players

Tournament Results - Playoff performance

Reports Function

Primary Route: app/routers/reports.py

Generates analytical reports and data exports for schedules, rankings, statistics, and historical data.

graph LR Reports[Reports Function] subgraph "Current Data" Reports --> schedule[tblschedule
Games] Reports --> ranks[tblteamrank
Rankings] Reports --> stats[tblplayerstats
Statistics] end subgraph "Historical Data" Reports --> hist_ranks[archive.tblhistoricalteamrank] Reports --> hist_player[archive.tblhistoricalplayerpoints] Reports --> ref_max[ref_max_lacrosse_schedule] end subgraph "Output Formats" Reports --> pdf[PDF Reports] Reports --> excel[Excel Exports] Reports --> csv[CSV Downloads] end

Available Report Types

Schedule Reports: Team schedules, master calendars, conflict summaries
Ranking Reports: Current rankings, historical trends, component breakdowns
Statistics Reports: Player stats, team performance, goalie statistics
Historical Analysis: Multi-year comparisons, trend analysis
Administrative: School rosters, contact lists, participation reports

Forms Function

Primary Routes:

app/routers/scheduling.py - Conflict CSV import & merge
app/routers/lax_conflicts.py - Varsity/JV conflict form
app/routers/feedback.py - Coach notes, score reporting
app/routers/cteam.py - C-Team scheduling intake
app/routers/progress_notebook.py - Scheduling progress notebook

graph TD Forms[Forms Function] subgraph "Import Forms" Forms --> conflicts[Conflict Import
→ ref_intake_conflicts] Forms --> rosters[Roster Import
→ tblroster] Forms --> stats_import[Stats Import
→ tblplayerstats] end subgraph "Data Entry Forms" Forms --> manual_stats[Player Stats
→ tblplayerstats] Forms --> awards_entry[Awards
→ tblawards] Forms --> rivalry[Rivalries
→ tblrivalry] end subgraph "Configuration" Forms --> rules[Scheduling Rules
→ tblschedulingrule] Forms --> rank_config[Ranking Config
→ tblpreseasonconfig] Forms --> announce[Announcements
→ tblannouncement] end subgraph "System" Forms --> bugs[Bug Reports
→ tblbugreport] Forms --> assets[Facilities
→ tblasset] Forms --> reservations[Reservations
→ tblassetreservation] end

Form Categories

Import Forms - Bulk data uploads from CSV files

Data Entry - Manual entry for stats, awards, and special requirements

Configuration - System settings and algorithm parameters

System - Administrative tools and facility management

Table Usage Matrix

This matrix shows how each database table is used across the six major application functions.

Not Used

✓ Read Access

✓✓ Primary/Heavy Use

Table Name	Dashboard	School Info	Scheduling	Rankings	Reports	Forms
CORE ENTITIES
`tblschool`	✓	✓✓	✓	✓	✓
`tblschoolalias`		✓
`tblschoolsportconference`	✓	✓✓	✓		✓
`tblsport`		✓	✓	✓	✓
`tblgender`		✓	✓	✓	✓
`tblseason`		✓	✓	✓	✓
`tblconference`		✓	✓		✓
`tbllevel`		✓
SCHEDULING
`tblschedule`	✓	✓	✓✓	✓	✓✓
`tblpossiblegamedate`			✓✓
`tblscheduledate`			✓✓
`tblschedulebuild`			✓✓
`tblmatrix`			✓
`tblgamelist`			✓
CONFLICTS
`tblfieldconflict`			✓✓			✓
`tblschoolconflict`			✓✓			✓
`tblfirstgamedate`			✓
`ref_intake_conflicts`			✓✓			✓✓
`ref_import_history`			✓			✓
`2025_lax_info_conflicts`			✓			✓
`2026_lax_info_conflicts`			✓			✓
RANKINGS
`tblteamrank`		✓	✓	✓✓	✓✓
`tblpreseasonconfig`				✓✓		✓
`tblrankingjob`				✓✓
PLAYERS
`tblroster`				✓	✓	✓✓
`tblplayerstats`		✓		✓	✓✓	✓✓
`tblgoaliestats`		✓		✓		✓✓
`tblawards`				✓	✓	✓✓
PERFORMANCE
`tblteamperformancerating`				✓	✓
`tbltournamentstanding`				✓
CONFIGURATION
`tblrivalry`			✓			✓
`tblschedulingrule`			✓			✓
`tblschoolexclusion`			✓
`tblscheduleissue`			✓
VERSIONS
`tblscheduleversion`			✓
`tblscheduleversiongames`			✓
ASSETS
`tblasset`						✓
`tblassetreservation`						✓
SYSTEM
`tblannouncement`	✓					✓
`tblbugreport`						✓
ARCHIVE
`archive.tblhistoricalteamrank`					✓
`archive.tblhistoricalplayerpoints`		✓			✓
REFERENCE
`ref_max_lacrosse_schedule`					✓
`ref_lax_g_schedule_history`					✓