DB design for “Gotcha” – Grade Probability

This article describes a suggested DB design.
This design is just an example.
Please use this as a template and adapt it to the system you wish to build.

Summary
Design Details
1. ER図
Usage
1. Grade Determination
2. Obtain items of specified grade

Summary

This is the "gacha" system often seen in mobile phone games.
The contents of the gacha are registered separately for each grade, and only the grade has a probability.
Probably the most practical.

For example, I would like the grade and probability to be 3% legend, 17% rare, and 80% common.
And each grade can have multiple items and the same probability within the grade.
And so on.

Design Details

ER図

erDiagram Gachas ||--o{ GachaGrades: "has many" GachaGrades ||--o{ GachaItems : "has many" Gachas { int id PK string name } GachaGrades { int id PK int gacha_id FK string name int rate } GachaItems { int id PK int gacha_grade_id FK string item_name }

Usage

Let me describe an example of acquisition in PHP Laravel.

Grade Determination

First, determine the grade of the gacha.
Obtain a grade ID.

public function getGrade($gachaId)
{
    $grades = GachaGrade::where('gacha_id', $gachaId)->get(); // from GachaGrades
    
    // 確率の合計値を求める
    $max = 0;
    foreach ($grades as $record) {
        $max += $record['rate'];
    }
    // If $grades is an array, the following is also acceptable.
    // $max = array_sum(array_column('rate'));
    
    // Get random numbers up to the total value minus 1, starting from 0.
    $rate = mt_rand(0, $max-1);
    
    // Find out where the random numbers hit.
    foreach ($data as $record) {
        $max -= $record['rate'];
        if ($max <= $rate) {
            return $record['id'];
        }
    }
    throw new Exception('Should not be here!');
}

PHP

Obtain items of specified grade

Next, the item is randomly obtained from the determined gacha grade items.

public function getItem($gradeId)
{
    // Get all items of the specified grade
    return GachaItem::where('gacha_grade_id', $gradeId)
      ->inRandomOrder() // ORDER BY RANDOM()
      ->first(); // LIMIT 1
}

PHP

There are various methods of random acquisition, so try to implement them while considering where to place the load on the DB, web server, etc.