[INFO] - Wish simulator implementation

[AimarIbarra]

Sup, @ROLFAN here!

In the end I will try to implement the wish simulator, so I need some information so I can know how to design the database.

Taking into account the following schema:

-- Wish Simulator Schema
CREATE TABLE IF NOT EXISTS wish_simulator (
    user_id BIGINT NOT NULL,
    entertwined_wishes INTEGER NOT NULL DEFAULT 0,
    limited_wishes_placeholder INTEGER NOT NULL DEFAULT 0, -- I forgor the name
    primos INTEGER NOT NULL DEFAULT 3200,
    standard_wishes_count INTEGER NOT NULL DEFAULT 0,
    limited_wishes_count INTEGER NOT NULL DEFAULT 0,
    character_list TEXT NOT NULL DEFAULT '[]',
    character_count INTEGER NOT NULL DEFAULT 0,
    weapon_list TEXT NOT NULL DEFAULT '[]',
    weapon_count INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id)
);

I guess that for each user it's desired to keep track of the amount of gacha roles, both in the standard wishes and the limeted ones (which I suppose are the event wishes), the characters they have got, and the weapons. However, I don't know what the purpose of the entertwined_wishes and primos entries are, it would be nice if someone could tell me.

In addition telling me which features should be implemented would be cool.

Thank you!

[paninizer]

Apologies, I misspelled entertwined_wishes.
It should be Intertwined Fate (intertwined_fate) for entertwined_wishes and Acquaint Fate (acquaint_fate) for the placeholder column.
The former is used in event wishing banners, Acquaint Fate for standard banner.
Then primogems is a currency that can convert to either of those wishes (160 primogems to one wish).
I may also implement more columns: stardust and starglitter, with both of them being currencies obtained through wishes. More information can be found on Genshin Wiki: Starglitter Wiki, Stardust Wiki with more specific details in Wishes.

Hope that helps.

[AimarIbarra]

Apologies, I misspelled entertwined_wishes.
It should be Intertwined Fate (intertwined_fate) for entertwined_wishes and Acquaint Fate (acquaint_fate) for the placeholder column.
The former is used in event wishing banners, Acquaint Fate for standard banner.
Then primogems is a currency that can convert to either of those wishes (160 primogems to one wish).
I may also implement more columns: stardust and starglitter, with both of them being currencies obtained through wishes. More information can be found on Genshin Wiki: Starglitter Wiki, Stardust Wiki with more specific details in Wishes.

Hope that helps.

Yep, guessed that much about the misspelling, but I wasn't 100% sure.
I will make an E-R diagram before making the schema (reduced to BCNF) and post it here so it can be decided if it is an appropriate design.
It shouldn't take me more than 20mins considering that I already have read what seemed relevant from the wiki.

[paninizer]

Alright thanks.

[AimarIbarra]

If the database type needs to be a SQL database then I believe the following E-R represents a good design.
I will later create the SQL code that in charge of implementing it and qeurying the relevant data.

[paninizer]

Second step is to write out helper functions that can query the DB.
Depends on the memory needed, it may be also be advisable to implement a map of all users and their data into the bot's global data framework (whether that is needed does depend on the scale of the bot)

[paninizer]

Depending on feature needs we may need to rewrite the wish_simulator schemas further, so making it editable in the future without erasing all of the data may prove to be beneficial.
I may plan on doing event banners from past versions and newcomer banner as a paid feature. Ideally, to incentivize users, a multiplayer feature may also be added where players can create decks and challenge dungeons and the top on the global leaderboard for each lunar month gets something special like spiral abyss but turn-based (will add after simulator finishes development).

[paninizer]

For now this is a good roadmap to start out with!

[AimarIbarra]

Should I design the whole schema for the bot?
Having a look at the other schemas I noticed many opportunities to normalize and therefore improve the DB.

At least redundancy would be reduced, and there should be performance gains.

And a normalized DB is easier to maintain, so it may be a good idea.

[paninizer]

That would be nice! Since I'm new to SQL I don't have an exact idea how it works so that would help a lot!

[AimarIbarra]

The first thing I need is a written description of the model, which I will write right now so you can give your thoughts on it as soon as possible.

I will post it here. Shouldn't take long.

[paninizer]

The most important ones are guild settings and modlogs

Guild settings will have the guild's prefix, whether certain features are enabled, etc

Modlogs will contain the moderation history of a guild.

The other one is bot_stat which counts the total command invocation of a guild.

There's not much else right now and later features may be implemented on an indefinite basis.