Geek Answers Handbook

Multiple databases compared to a single database with identifiers used to separate organizations

In a web application situation that organizations can register and use to manage data, what are the pros and / or cons of the following two options?

  • One database per organization , where the data for each organization is completely divided into separate databases, with one (very small) centralized database that contains the main list of organizations and their database identifiers.

  • One database for the entire application , where all objects are stored in one large database and shared using the organization identifier column in each table.

Some other aspects to consider:

  • The data will never be shared with organizations in the database, and credentials will not be entered.
  • Some organizations will allow the general public to register an account in an application to send data, others will not.
  • We plan to expose an open API for organizations to integrate their current processes with our application. Organizations will be able to create API keys to access their data, but there will be no public API that spans organizations.
  • Companies will store potentially sensitive data in the app.

From your experience and / or knowledge, what is the right way to solve this design decision (or is there a “right” way at all?)

+4
source share
3 answers

Here is a detailed discussion of MSDN (Multi-Tenant Data Architecture).

I would add that there is no right / wrong way. It all depends on the requirements, experience and cost.

+8
source

You could find interesting comments by Joel Spolsky on this subject. Here's a transcript of the podcast in which Joel discussed the database architecture with several Fogbugz tenants:

https://stackoverflow.fogbugz.com/default.asp?W24218

(find the transcript for the question starting at [50:45])

+5
source

One of the considerations that might be interesting:

if you have one database for all organizations, and these organizations are distributed across all 24 time zones, then this requires a 24/24 DBMS (and possibly 7/7). I believe that with all products this is not so.

0
source

More articles:


All Articles