Hiya!
I have some concerns about using an SQL for being the primary host of a game. I have an idea that’s a little courageous and breaks the barriers, but I need see if I’m not chewing more than I can swallow here. So that’s why I’m coming here first.
So here’s the concept:
tl;dr - Making singleplayer servers of a galaxy consisting of yours and other peoples’ solar systems with cross-server communication and wondering if this is practical, if not possible.
This will be a space exploration game separated into singleplayer servers. You are in a galaxy that will expand when more new people play the game. Upon a user joining the game for the first time, it will select randomized Vector coordinates of the user’s home solar system location. The more existing solar systems, the farther away these Vector coordinates will be from the origin (older players will be closer to the origin). Rogue systems (solar systems not owned by players) will be generated nearby the new player as well.
Here’s a visual representation of the galaxy.
These coordinates will be indexed in an SQL database. Each row will consist of the owner’s username, the planet names, types, etc. In order to reduce the web host’s bandwidth, the name/type values, such as planet appearance, colony data and whatnot, will just be index names to retrieve from the Roblox Datastore.
Of course, not all of these star system locations will be generated at once. While exploring the galaxy, it will take query data from the SQL in a continuous loop, returning the nearby star systems in the database and generate those in the workspace.
You can enter player’s star systems and attack if they’re not pacifists, or give gifts, alliance and so on, or colonize uncolonizes star systems. But this is getting off topic so I’ll end the concept explanation here.
So, here are my concerns,
1) Querying - Plenty of SQL’s are used to hold username and password data for websites and whatnot. Some people, typically with millions of rows in a database, have issues with SQL taken an incredibly long time to find indexes. What’s the most efficient thing I can do to prevent this? Make a new table for rows every ~1 million rows? Any good way to search for nearby vector coordinate indexes without using FIND?
2) Bandwidth - In order to update cross-server communication as often as possible, each one player server will have to do 300-400 requests p/m on average. I use Hostinger with a business plan, SSL certificate and unlimited bandwidth. But can it still handle this? They may think I’m doing something fishy, sending and receiving info back and forth with a third-party service so quickly. Like I’m trying to DDoS their service.
3) Unrelated to SQL stability, is this just too complicated for a Roblox game? Will this overwhelm new players? This may be too courageous of an idea and I should take a few steps back, but at the same time I really want to bring something new to the table. Is this concept practical?
I know this is a lot, sorry for stupid questions. Just need to get my worries about the technical problems out of the way if I ever choose to go forward with this. If anyone has other SQL-related stuff I should be aware of, please let me know!
Thank you!