Building a MapleStory Boss Drop Management Discord Bot: Database Designing

Being a gamer, nerd and programmer, the urge to solve problems always come naturally. Sometimes, it's not even a problem - it's just about me trying to find a better way to do something that I had been doing all this time. Converting a current procedure to something else that should be more pleasant to work with... at the cost of... I don't know?

_{xkcd knows best...}

Anyways, here's the situation...

---

In the game MapleStory, many players including me hunt a bunch of weekly bosses every week. While it is a totally optional piece of content, weekly bosses give a lot of useful items that are highly sought after by players, so that provides us with sufficient motivation to do them as much as we can. Naturally, just like in every other game, harder bosses gives better drops, and they get drastically harder to solo without funding your character a lot. Hence, we often form fixed parties to play these content together every week. If you're lucky with the party forming part and got a bunch of cool people by your side, they can make the experience really enjoyable, and I believe that it is one of the main reasons why the game is still surviving after one and a half decades.

But, of course, there is always the less-enjoyable part... and it happens to be the management part of it. After all, if you have a party, you need to manage it, right?

In my party, there's not much to manage - we just suggest a time for the week, everyone meet up, have fun, and we move on with our own stuff. The issue comes after we have the fun, where we have to sell the loot we get and split the gains. It isn't difficult, but recording what we sold and how much we got is an annoying task.

So far, we were using an online Excel spreadsheet stored on my OneDrive. It looks something like this, with just a few columns and some highlighting to mark those rows that have been claimed by party members. There's a column for optional remarks and another column to mark if anyone haven't claimed their portion, but they are somewhat rarely used. To be frank, it is also not that great to manually write down who claimed and who didn't either. Especially when everything needs to be done manually.

_{The Excel spreadsheet we are currently using for the job. The rows highlighted in green means that the split is already passed to everyone involved.}

The current system works, but with a few issues...

1. It's manual, and normally I need to spend significant amount of time processing this after we clear the bosses. It's just not that fun, considering that I want to play games, not doing Excel work.
2. Excel online doesn't seem to have functional autocomplete, so we type item names in manually. At this point, I have already memorized most of the names of the drops due to manually typing them for about half a year or more.
3. It requires me to open the file up just to update rows once I see them getting sold in the game. We don't normally run an Excel spreadsheet in the background while gaming, right?

So, I had been thinking for another solution... maybe, I can make a Discord bot for it. Since we always have Discord running while gaming, we already make records in the Discord after every boss run (so that I can update them into the Excel later), and it sounds much much better to just type a command and have the bot record it down for me! Alright, let's do it!.

---

In short, the primary function of the bot for now is to help us record our boss drops and selling history, so that I can update them naturally through Discord after every boss run and everyone can check how much we made easily without going into Office Online. In some ways, it feels like a database UI. Command-controlled Excel, or like how one of my friend describes SQL, "Excel on Crack".

Hence, naturally, we will need the bot to record the data in some place. We can use Excel, and that should work fine, as there are libraries out there that can help us to edit Excel files through code. However, it also feels wrong, because Excel is a format that feels really fragile if you ask me. It's like if you let something that is not from Microsoft take a glance at it it'll catch fire and deconstruct itself magically. Hence, I'm more oriented towards using a file format that is less difficult to work with. Perhaps, a simple text file, or something that makes more sense - as SQLite database? The SQLite database feels like the right thing to use, as it is easy (SQL just works without questions if you design it right), intuitive (we have organized data that can reasonably live in a database), and it is almost guaranteed to work everywhere. Even if in the future for some unknown reason I need to rewrite the bot in another language, the database will still work. I might have trouble with library options for other data storage options, but SQLite, it really won't go terribly wrong.

So, let's design the database, because after all, the bot is just a "database UI"... if the database is right everything will be easy after that! At least that is what I think. Heh.

I'm using DBeaver to create the database. So first, making an SQLite database...

Why Lutie? Well, it's named after this NPC in the game.

Anyways, moving on...

So now, we have this totally empty and clean database to work with. What tables do we need to add? Recalling the Excel spreadsheet...

_{It's so clean... just like the start of every project. It's not gonna last, so maybe look at it for a little longer before we push it into the abyss of chaos.}

1. A table to store each drop for the bosses and their selling records, just like what the current Excel spreadsheet does.
2. A table to store the item names, because we don't want to store the item names over and over again, that's not so nice.
3. A table to store the boss names and their difficulties.
4. A table to store the split and claiming records.
5. A table to store party members, so that I can do some access control if I need to. And in the future, maybe some other parties in the same Discord server wants to use this system, they can use it as well.

So... let's start making!

---

Making tables in DBeaver is pretty simple, just right click on the "Tables" list under the database on left panel, click create, then fill in the stuff on the right side, and that's quite it. Because I want to ensure foreign keys, I will start from the table that has the least dependencies first. That has to be the item table, because they are just item names and nothing more. It's a really simple table, with only two columns, with the id being the primary key. It seems that at least in DBeaver, if you let a column be "not null" and "auto increment", it automatically makes it a primary key. Not exactly what I wanted, but okay.

DBeaver generates the SQL for me when pressing save, and I'm pretty happy that I don't need to manually write them. I'm okay with writing SQL for selecting and inserting, but for creating and updating tables and other table design tasks, I trust the software more!

Oops, the table name should be "DropItem"... but okay, we can just rename it.

After that, the easier table to make should be the boss table, since it also depends on nothing. After making the boss table, we can then make the boss difficulty table, since a boss has multiple difficulties, and it would be nice to have these in a separate table as well. The table can also have abbreviations for each boss, as we usually call the bosses a way like "Easy Lucid" as "ecid". It would make it easier for us to use the bot later if we can use abbreviations instead of their full names!

_{Table for the Boss Difficulties.}

I also added a foreign key restriction to this table to prevent the unlikely situation of me deleting a boss that is linked to a difficulty. Since it's a restriction that makes sense either way, so it's here...

Adding two more tables for the parties, with a similar foreign key restriction.

At this point, since we already have the tables for bosses and items, we can make the table for the drop records. Unlike the Excel file, I chose to put the selling record in another table. So, it's going to be a simple table recording which party got it, what is the item, and what boss dropped it. As you probably imagined, I added three foreign key constraints here. I'm not sure if I'm having OCD or something, but I sure love adding constraints, lol.

Now, it is going to be the split table. I thought a little on this, and decided that I should have the bot adding "pending claims" for everyone in the party for every item we get. This way, the case of certain members not being in the run (or deciding to let the others share their portion) and the case of having occasional guest members in the party can be easily be handled as well, since it is just about adding more or lesser rows referencing to the item drop into this table. When calculating how much each member should get when they claim their "payout", the bot just needs to query this table for the member, sum up their portions, and tada, we got it. The only issue seems to be that I cannot have a restriction of an item drop must be available in the split table, due to how foreign keys work. That should not be too big of an issue, but if you know a design that can have this restriction in, let me know!

I also decided that since all I want to know is how much each member should get, it should also be optional to store the original selling price of each item, and we can just let the bot do the calculation of taxes and split amount at the moment of me sending the command to record instead. The column to record if a row is claimed by the member or not should have been a boolean column, but it so happens that SQLite doesn't have a boolean data type... so a text field with 'T' and 'F' will do. Or, 'Y' and 'N', depending on how I code the bot later. So, finally, here's the last table, after so long!

As usual, I added the foreign keys after that. That's an unexpectedly long amount of time spent designing the database. Hopefully it'll pay off later when I start coding the bot and save me tons of trouble! Or maybe I'll come back again to fix some design issues... that's a problem for future me when I finally start coding. It should happen really soon, but, no promises, plans are made to be broken...

Before I proceed to code the bot, I'll also need to fill in the database with some data, so that I'll have something to try out later while testing during coding! That's for another time as well, I guess. I should also move this database to a cloud storage or somewhere so that I can edit it on some other place that is not this PC. Like, in the car when I arrive at the office too early. That's the beauty of SQLite, a database that you can throw anywhere and it works.

That's all for now! Future me has a lot to do on this project, but that's a problem for future me. See you next time when future me finally decides to write code to do things with this database!

:)