Database & Questing

[KittHaven]

Hello! I have a few questions that kinda come from my brainstorming lol. On my site I want to have quests. I have a solid idea for how to do quests, most will be set up through files with little to do with the database except for every step a user completes it will need to be stored in the database for that user. As well as if there is a timer for the quest... say the user needs to wait 2 hours before being able to complete the next step, it would also need to store what time the user started that step so I could only allow them through when the time is + 2 hours. As a brief example lol.

Anyway, that could mean the database gets huge eventually. Before release I'd like to have a decent amount set up. But if, say, I have 50 quests and each quest has like 5 steps (as ballpark figures) the adopt_quests table could have like 250 columns, plus however many rows as users. Maybe even more if certain quests require more info to be stored, like time/date started, etc. I don't know too much about databases beyond a few basics, so is that a good idea? Not even thinking about how big it could get if you release new quests after release.

If not, does anyone have a better one? How would you store the information? Would you maybe split the quest table up into sets?

For example I have plans for a set of tutorial quests that users can complete. These quests will give users rewards while teaching them the basics, etc. So I could set up a table called adopt_tutorial and have all the tutorial quest steps stored in there for users.

I also have daily quests planned. Such as finding a random lost item and needing to check with NPCs to see if it belongs to them. So any repeatable quests could get their own table.

But yeah.. any ideas for how best to handle this?

-As an aside, my current plan for quest step storage is to assign each quest an ID and store the steps like: 1_1, 1_2...3_3, 3_4, etc. Then the default value is set to 'no' for them not having completed it, and set to 'yes' when they have. I feel like IDs will be easier than trying to comes up with a word to identify each quest lol. I will probably also store quest info in a new table, which will store the ID, name of the quest, NPCs involved, a description, etc. so I can have an on-site quest log that displays the information to users.-

Also, how would you go about creating a new table for users when you already have an established userbase, if I needed to add a new feature? When you don't have them it's easy enough. You can create the table and make it so when a new user registers they get added to it. But if you already have an established userbase you'd need to add your existing users to it first. I've been curious about this lol. This kinda thing would definitely be done when the site is in maintenance mode so new users cannot sign up or login while doing it..

Thanks for help and sorry for the length lmao. When I started I just kept typing more.. xD

 

[Hall of Famer]

But if, say, I have 50 quests and each quest has like 5 steps (as ballpark figures) the adopt_quests table could have like 250 columns, plus however many rows as users.

I dont think you would need as many columns as quests x steps, it seems unnecessary. You can have separate tables for quest types and quests for users, just like how Mysidia does it with adoptables and owned_adoptables.

 

[KittHaven]

I dont think you would need as many columns as quests x steps, it seems unnecessary. You can have separate tables for quest types and quests for users, just like how Mysidia does it with adoptables and owned_adoptables.

Oh yeah that way makes a lot more sense lmao Thanks!