Hello all. I have a question I wanted to pose to the community to get some feedback as I'm not sure what the best approach is. I have a client who currently has an application they have developed. They have one master db that stores some info that is central to their business, and other dbs that are client specific. That is to say, they deploy their application for each client, with each client having a separate db.

I am looking at creating a new application for them but am faced with the question of how/where to put the data. On the one hand, I could create it in the master db, centralizing all the data for the new application, but would need to come up with a creative solution about how to connect the data in the master with the data in the client specific db. On the other hand, I could create the data in the client specific db, but as part of the project, I need to provide a means for aggregating the data for this new endeavor from all client db's into a single interface for an admin user to work with without having to log into each environment separately.

I would love to get some feedback on this, which approach would be better and why, or perhaps an approach I haven't considered. I apologize if this is not clear enough. Still trying to wrap my head around it.

persoanlly I'd suggest a single central db with client data identified appropriately within.

alternatively export the client data into a single DB

another approach is to store the various db's on the same physical server and then with judicious use of queries connect to that data at anyone time. you can query data from another MySQL db from another MySQL db

importing data is potentially tricky as it can be difficult to spot duplicates, you have wasted effort bringin across duplicates, but you can get round that by a carefully designed interface

in the absence of the business needs and reasons why you have discrete db's I would say the single central db with internal client identifiers would be the 'smarter' solution

retrospectively the multiple db on one server may be the best route. especially of your db's are called say
Co1, Co2 and so on.

you coudl copy the existing data to the same server if required