KartRider Analytics is a full-stack database management and analytics web platform for a kart racing game. The project includes a relational database in MySQL and an interactive web application built with PHP, supporting player management, dynamic SQL queries, and game performance analytics.
It demonstrates best practices in:
- Database design (3NF/BCNF normalization)
- CRUD operations (Create, Read, Update, Delete with cascade)
- Analytical query building
- Dashboard data visualization
kartrider-analytics-database-main/
├── CodeBase.pdf # Project codebase documentation
├── README.md # Project overview and setup instructions
├── kartrider_data.sql # SQL file for sample data population
├── kartrider_ddl.sql # SQL file for database schema (DDL)
├── htdocs/ # Web application root directory
│ ├── README.md # Application structure documentation
│ ├── config.php # Main configuration file (database connection, paths)
│ ├── config_environment.php # Environment-specific settings
│ ├── dashboard.php # Dashboard entry point (analytics interface)
│ ├── index.php # Main landing page (homepage + navigation)
│ ├── profile.php # Profile management (CRUD operations)
│ ├── queries.php # Dynamic query interface
│ ├── table_viewer.php # Database table viewer
│ ├── assets/ # Static assets (CSS, JavaScript, SQL)
│ │ ├── README.md # Assets folder documentation
│ │ ├── dashboard.js # Dashboard interactive features (JS)
│ │ ├── style.css # Global CSS styling
│ │ └── tabs.js # Tab navigation interactivity
│ ├── controllers/ # MVC controllers handling backend logic
│ │ ├── DashboardController.php # Main controller for dashboard routing
│ │ ├── PlayerStatsController.php # Handles player statistics page backend logic
│ │ ├── ProfileController.php # CRUD operations for player profiles
│ │ ├── QueriesController.php # Dynamic SQL query execution backend
│ │ ├── TableViewerController.php # Backend for table viewer interface
│ │ ├── README.md # Controllers module documentation
│ │ ├── dashboard/ # Sub-controllers for dashboard modules
│ │ │ ├── AchievementDashboardController.php # Achievement analytics controller
│ │ │ ├── PlayerStatsDashboardController.php # Player statistics analytics controller
│ │ │ ├── SessionAnalyticsController.php # Session analytics controller
│ │ │ └── README.md # Dashboard controllers documentation
│ ├── includes/ # Core backend services and utilities
│ │ ├── AssetHelper.php # Helper for static asset management
│ │ ├── BaseController.php # Base class for all controllers
│ │ └── README.md # Includes folder documentation
│ ├── models/ # Database services and data models
│ │ ├── DatabaseService.php # Database connection service
│ │ └── README.md # Models folder documentation
Player (1:1) ↔ PlayerCredentials
Player (1:1) ↔ RegisteredPlayer/GuestPlayer
Player (1:M) → Participation (M:1) ← Race
Participation (1:M) → LapRecord
Kart (1:1) ↔ KartDetails
Kart (1:1) ↔ SpeedKart/ItemKart
Track (1:M) → Race
Achievement (M:M) ↔ Player (via PlayerAchievement)
| Player Management | Game Activities | Game Assets |
|---|---|---|
| Player | Race | Kart |
| PlayerCredentials | Participation | KartDetails |
| RegisteredPlayer/GuestPlayer | LapRecord | SpeedKart/ItemKart |
| PlayerAchievement | Track, Achievement |
- Fully normalized (3NF/BCNF)
- Foreign keys with ON DELETE CASCADE
- Check constraints for data validity (e.g.,
FinishingRank BETWEEN 1 AND 8) - Strategic indexes for performance
- Register new player
- Update player profile (username, email, profile picture)
- Delete player with cascade (removes participation, achievements)
- Join Query – Top players with achievements
- Aggregation Query – Average playtime & total achievements
- Nested Group-By – Weekly playtime analysis
- Filtering & Ranking – Top 5 players by win count
- Custom Query – Free SQL SELECT with safety constraints
- Player Stats: player counts, active rates, type distributions
- Session Stats: race participation, kart usage, track difficulty
- Achievement Stats: progress, rare achievements, completion rates
- MySQL 8.0+ or MariaDB 10.4+
- Apache + PHP (XAMPP, MAMP, or PHP server)
- phpMyAdmin (optional)
- Import database schema:
mysql -u username -p < kartrider_ddl.sql mysql -u username -p < kartrider_data.sql
- Configure
db_connection.php:$conn = new mysqli('localhost', 'root', 'password', 'KartRiderAnalytics');
- Deploy
htdocs/to your PHP server root. - Open browser:
http://localhost/dashboard.php
bash# Clone the repository git clone https://github.com/tyrahappy/kartrider-analytics-database.git cd kartrider-analytics-database
SELECT pc.UserName, COUNT(*) AS TotalWins
FROM Participation p
JOIN PlayerCredentials pc ON p.PlayerID = pc.PlayerID
WHERE p.FinishingRank = 1
GROUP BY pc.UserName
ORDER BY TotalWins DESC
LIMIT 5;SELECT k.KartName, COUNT(*) AS UsageCount
FROM Participation p
JOIN Kart k ON p.KartID = k.KartID
GROUP BY k.KartName
ORDER BY UsageCount DESC
LIMIT 5;SELECT t.TrackDifficulty, COUNT(*) AS RaceCount
FROM Race r
JOIN Track t ON r.TrackName = t.TrackName
GROUP BY t.TrackDifficulty;Table Browser: Complete database exploration with search/sort Interactive Filters: Time period and demographic filtering Real-time Updates: Designed live‑update dashboard architecture, with plans to integrate intelligent caching for performance Security: Implemented secure user registration using prepared statements and parameter binding to prevent SQL injection.
| Module | Key Metrics |
|---|---|
| Player Stats | Total players, active players, win rates |
| Session Stats | Race counts, kart usage, track popularity |
| Achievement | Total achievements, rarest, completion % |
With filters by time range.
Tables: 14 fully normalized tables Records: 377+ comprehensive test records Queries: 25+ optimized analytical queries Performance: Sub-second response times with indexing Relationships: Complex multi-table joins with referential integrity
Controllers: 8 specialized MVC controllers Features: Complete CRUD + 5 analytics modules Visualizations: 15+ interactive charts and tables Code Quality: 2,500+ lines of documented, production-ready code Security: Zero SQL injection vulnerabilities
This project fulfills all core requirements of a database-driven web application with CRUD, dynamic querying, and interactive dashboards. It deepens understanding of relational databases, SQL optimization, and data visualization in a full-stack environment.
Live Application: https://kartrider.kesug.com Source Repository: https://github.com/tyrahappy/kartrider-analytics-database.git Documentation: Comprehensive README files in each directory Academic Context: CS 5200 Database Management Systems