I run a site where companies create accounts and have their users sign up for their accounts. A couple of the companies I work with have sensitive data and for that reason the decision was made a while back that we would maintain separate databases for each company that registers with our site.
To be clear, the DB's look similar to the below for companies A, B and C.
db_main /* Stores site info for each company */
db_a
db_b
db_c
Now, I'm finding that sometimes a user creates an account with both company A and company B, so it would be nice if I could combine their progress from the two sites (A and B). For example, if the user earns 5 points on site A, and 5 points on site B, I would like for their total site points to read "10" (their combined total from 5 + 5).
There are hundreds of these databases, though, and I'm worried that it will be rough on the server to be constantly running queries across all databases. The user's score, for instance, is calculated on each page load.
Is there an efficient way to run queries in this manner?
Joining to 100 DB's should never be an option, and to your question, it won't be efficient.
What I would suggest instead is to create a global table that stores a cache of the points you are after globally. Points should not be 'sensitive' in any way from the sounds of it. I assume a userID is not either. Given that a customer should never have direct query access to this table, it should be a non-issue.
Scenario:
dbGlobalPoints gets upsert'ed (if exists (it won't), update points+5, else insert userID, 5)
User then joins siteB with same username (this may be your biggest issue if you don't have unique id's across systems)
On initial run, a 'rebuild' process of sorts will need to be run which steps through each company table and populates the global table. This will also be useful later for a 'recount' process (say, you drop dbA and don't want those points to count anymore) you could also make this a subroutine that fires per user just once (in the background) if they don't have a record in the global points database.