DB design for “Gacha” – Same Probability

Gacha systems are commonly used in mobile games and online services to provide randomized rewards to users. A well-structured database design ensures efficient data management and smooth system operation. This article outlines a simple yet effective database design for a gacha system.

Basic Table Structure

To implement a gacha system, we need the following tables:

• users – Stores user information.
• gacha_pools – Defines different gacha pools.
• gacha_items – Stores the items available in gacha pulls.
• gacha_results – Records user gacha pull results.

Table Definitions

users Table

Stores basic user information.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

gacha_pools Table

Defines different gacha pools.

CREATE TABLE gacha_pools (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

gacha_items Table

Stores items available in the gacha system.

CREATE TABLE gacha_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pool_id INT,
    name VARCHAR(255) NOT NULL,
    rarity ENUM('Common', 'Rare', 'Epic', 'Legendary') NOT NULL,
    probability DECIMAL(5,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pool_id) REFERENCES gacha_pools(id)
);

gacha_results Table

Records user gacha pull results.

CREATE TABLE gacha_results (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    item_id INT,
    pulled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (item_id) REFERENCES gacha_items(id)
);

Gacha Pull Logic

When a user performs a gacha pull, the system should:

1. Select a gacha_pool.
2. Randomly choose an item based on the probability column in gacha_items.
3. Insert the result into gacha_results.

Sample Query for Pulling an Item

SELECT id, name FROM gacha_items
WHERE pool_id = ?
ORDER BY RAND()
LIMIT 1;

Conclusion

This database design provides a foundation for implementing a gacha system. It ensures proper data management and allows flexibility for future improvements, such as user-specific drop rates, event-based pools, and tracking user pull history.