0

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?

What have you tried
  • 11,018
  • 4
  • 31
  • 45
  • I ended up modifying my design from a multi-tenant design where each tenant has its own DB. Took a long time but well worth the work. Thanks for the answers. – What have you tried Sep 12 '14 at 14:27

1 Answers1

0

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:

  • User joins siteA
  • earns 5 points
  • dbA gets updated

  • 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)

  • profile query pulls/joins dbGlobalPoints for display
  • earns 10 points.
  • dbB gets updated
  • dbGlobalPoints gets upsert'ed (if exists (it will), update points+10, else insert userID, 10)

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.

BReal14
  • 1,603
  • 1
  • 12
  • 35
  • A pretty solid explanation on what you should do. Just one extra thing to complement the answer: Don't recalculate on each page load. Save the points in session on login, and update the session just on a page where the points are earned. As he suggests when you update `dbGlobalPoints` update the Session. – Eduardo Romero Sep 11 '14 at 14:59