Mar Java Mit Java

Database Design for Cricket Scoring App with Efficient Ball Information and Flexible Extra Handling

by · February 27, 2024

Database Design for Cricket Scoring App This design uses a relational database model with several tables to represent various aspects of a cricket match:




Tables:

Matches:

match_id (PK): Unique identifier for the match
series_id (FK): Foreign key referencing the series table (optional)
match_type_id (FK): Foreign key referencing the match_type table (e.g., Test, ODI, T20)
start_date: Date and time the match started
venue: Name of the venue
team_1_id (FK): Foreign key referencing the team table for the first team
team_2_id (FK): Foreign key referencing the team table for the second team
winner_id (FK): Foreign key referencing the team table for the winning team (optional)


Series (optional):

series_id (PK): Unique identifier for the series
series_name: Name of the series

Match_Types:

match_type_id (PK): Unique identifier for the match type
match_type_name: Name of the match type (e.g., Test, ODI, T20)



Teams:

team_id (PK): Unique identifier for the team
team_name: Name of the team



Players:

player_id (PK): Unique identifier for the player
player_name: Name of the player
team_id (FK): Foreign key referencing the team table



Innings:

innings_id (PK): Unique identifier for the innings
match_id (FK): Foreign key referencing the matches table
team_id (FK): Foreign key referencing the teams table for the batting team
batting_order: Order in which the players batted (optional)

Overs:

over_id (PK): Unique identifier for the over
innings_id (FK): Foreign key referencing the innings table
over_number: Number of the over


Balls:

ball_id (PK): Unique identifier for the ball
over_id (FK): Foreign key referencing the overs table
bowler_id (FK): Foreign key referencing the players table for the bowler
batsman_id (FK): Foreign key referencing the players table for the batsman
runs_scored: Number of runs scored on the ball
is_wicket: Boolean flag indicating if the ball resulted in a wicket
is_extra: Boolean flag indicating if the ball resulted in any extra runs

Wickets :

wicket_id (PK): Unique identifier for the wicket
ball_id (FK): Foreign key referencing the balls table
out_batsman_id (FK): Foreign key referencing the players table for the dismissed batsman
fielder_id (FK): Foreign key referencing the players table for the fielder credited with the wicket (optional)
wicket_type_id (FK): Foreign key referencing the wicket_types table (optional)
partnership_id (FK): Foreign key referencing the partnerships table (explained later)

Wicket_Types (optional):

wicket_type_id (PK): Unique identifier for the wicket type
wicket_type_name: Name of the wicket type (e.g., bowled, LBW, etc.)

Partnerships :

partnership_id (PK): Unique identifier for the partnership
innings_id (FK): Foreign key referencing the innings table
batsman_1_id (FK): Foreign key referencing the player table for the first batsman
batsman_2_id (FK): Foreign key referencing the player table for the second batsman
runs_scored: Total runs scored during the partnership

Extras:

ball_id (FK): Foreign key referencing the balls table
extra_type_id (FK): Foreign key referencing the extra_types table
extra_runs: Number of extra runs awarded on the ball

Extra_Types:

extra_type_id (PK): Unique identifier for the extra type
extra_type_name: Name of the extra type (e.g., bye, leg bye, wide, etc.)

If ball is isextra…how will we find its value in extras table…Do we need extrasid in balls table?

You don’t need an extras_id in the Balls table to efficiently find the extra value in the Extras table when is_extra is true. Here’s how it works:

  1. Querying the Balls table:
    • Use a query to filter the Balls table based on the desired criteria, such as specific innings or specific bowlers.
    • This query should already include the is_extra flag.
  2. Joining with Extras table:
    • Use an inner join between the filtered Balls table and the Extras table on the ball_id.
    • This join will link each ball marked as is_extra in the Balls table with its corresponding entry in the Extras table.
  3. Retrieving Extra Value:
    • Once the tables are joined, you can access the extra_runs and extra_type_id (or extra_type_name if preferred) from the Extras table.

Advantages:

  • This approach avoids redundancy by not adding an extras_id to the Balls table, which could potentially lead to data inconsistencies.
  • It simplifies data updates if new extra types are added, as you only need to modify the Extra_Types table without changing the Balls table structure.
SQL
SELECT b.ball_id, b.runs_scored, e.extra_runs, e.extra_type_name
FROM Balls AS b
INNER JOIN Extras AS e ON b.ball_id = e.ball_id
WHERE b.is_extra = TRUE
AND b.innings_id = 123;  -- Replace with your desired innings ID

You may also like...