Insax's avatar
Insax
OP
4 years ago
Level 3

Use same Model for multiple database connections

I'm working on a project to mange multiple Gameservers at the same time.

Basically each gameserver has its own database and the user can switch between dropdown menu between the servers. The amount of gameservers is unknown, so it can be 1 or 20

Now the "real" issue - the database connections since all of them use the same schemas, but ofcourse every db has different content.

For requests I would be able to check the requested Server (which is part of the url for shareability) either in a Service Provider or Middleware, but for backend jobs (using the queue system) i can't really do that because every model is meant to be bound to one connection.

Also a deciding factor for me is that the database servers are kind of slow - they lack performance so i want to keep the amount of Database interactions as low as possible

Possible Methods (which I'm not happy with alltogether)

a) Save all possible gameservers in one main database - then on every quest check the values from session, url and database which connection to use and run \Config::set()

That solution works somehow, but does not really offer a way to deal with Background Jobs/Schedules

b) Simply make an application for each gameserver! and the applications database gets shared

This would work perfectly fine, but extremly hinders User experience - especially because I want to be able to show some statistics on the dashboard which would become more and more of a hassle, especially because ACID becomes more and more of a problem the more applications are running.

c) Add the connection to each Database interaction in the code

Again: How to figure out in schedules and jobs - also the code somewhat would look ugly with that.

d) Simply create a model for each Server for each Table

No, just no - thats an insane amount of duplicated code which becomes nearly impossible to maintain at some point

e) Run a SPA and let some smart ass api handling do the rest by implementing a api for each server

This also would be a solution - but unfortunatly I'm not that good with vue/react and I don't have the time to learn it that in-depth for that project

Has anyone ever encountered this issue and has come up with a practical solution to fix this? For me it looks like the most practical solution would be the last solution using an spa with multiple apis - but is that really the only way?

0 likes
3 replies
Tray2's avatar

First of all this

Also a deciding factor for me is that the database servers are kind of slow - they lack performance so i want to keep the amount of Database interactions as low as possible

Is completely wrong, a properly set up database with a good design and with well written queries is fast, even very fast.

I would probably go with one application behind a load balancer with x amount of servers (web servers) and dynamically to the correct database. However, I don't really think you need more than one properly setup database until you reach a very high number of users.

You are not telling what RDBMS you are planning on using but there is always Oracle if MySQL/MariaDB or Postgre isn't fast enough.

Insax's avatar
Insax
OP
4 years ago
Level 3

@Tray2 I do completly agree with you - but the issue here is that the DB Structure of the Gameserver itself is completly inefficient - something that I can not change unfortunatly because its "dictated" by the game itself.

(e.g) instead of doing a 1:n relation between clans at moderators (of the clan), its just a serialized array in the field with the ids of the moderators.

Also when we did a first test and we selected a sample dataset using the primary key as where condition, the query took 2 seconds to come back (the table had 200 entries)

The database itself is a mysql database

Please or to participate in this conversation.