Within the situation of the web application that organizations can subscribe to and employ to handle data, do you know the pros and/or cons of these two following options?
One database per organization in which the data for every organization is entirely separated into individual databases, with one (really small) centralized database that looks after a fundamental report on the organizations as well as their database identifiers.
One database for the whole application where all of the organizations are saved in a single large database and separated utilizing an organization identifier column on each table.
Another aspects to think about:
- Data should never be shared between organizations within the database, nor will login qualifications.
- Some organizations allows everyone to join up a free account using the application to submit data, others won't.
- We intend on subjecting a public API for organizations to integrate their current processes with this application. Organizations will have the ability to generate API secrets to permit use of their data, but there will not be considered a public API that spans organizations.
- Companies is going to be storing potentially sensitive data within the application.
Out of your experience and/or understanding, what's the proper way to build a storage shed design decision (or it is possible to "right" way whatsoever?)
Here is definitely an in-depth discussion on MSDN (Multi-Tenant Data Architecture).
I'd include that there's no right/wrong way. Everything is dependent on needs, existing expertise and price.
You will probably find Joel Spolsky's comments about this subject interesting. Here is a transcript from the podcast by which Joel talked about the multi-tenant database architecture of Fogbugz:
(search the transcript for that question beginning at [50:45])
One consideration that could be interesting:
for those who have just one database for those orgs, and individuals orgs are dispersed all over the 24 timezones, than the takes a 24/24 (and possibly 7/7) DBMS. Not every items obtain that, In my opinion.