41

Space is cheap these days, so I'd advise to use one database per application. Sharing one database for amongst multiple applications has some serious disadvantages: The more applications use the same database, the more likely it is that you hit performance bottlenecks and that you can't easily scale the load as desired. SQL Databases don't really scale. ...


35

According to Microsoft, the term has 3 potential meanings (one database for all tenants, or one databaser per tenant). To use your example, each customer would be it's own tenant. A database per tenant (customer) Each tenant is isolated from the others (no accidental access to other tenants' data) The isolation also makes it easier to manage restoring of ...


29

Which of these paradigms constitutes a traditional "multi-tenant" DB Both concepts are called multi-tenancy, since it is just a logical concept "in which a single instance of software runs on a server and serves multiple tenants" (from Wikipedia). But how you implement this concept "physically" is up to you. Of course, the application needs a database ...


23

I had a similar situation once. My problem was to build 3 applications, one for inventory management, one for procurement management, and one for managing users, i.e. employees. My recommendation is not to break databases physically per application, or join them physically per application. Rather, IMHO logical separation works better. For example, all 3 ...


12

Besides siloing of data, you may run into problems with Availability - with a single tenant, they can only DoS themselves, but even when data is properly siloed, a tenant can still exhaust resources. Logging - all log messages assumed a single tenant. Unless you silo logs per tenant, your log messages may become less useful. Concurrency - single tenant ...


10

If you are using SQL Server, use one database but use schemas. Use dbo for stuff that is general to all clients and create a schema for each client and make that the default schema for users from that client. Now you can have a general object (say a getBudget proc) in the dbo schema and a customized one for the client in their schema with the same name.


9

If these applications are meant to work from the same data - for example, the same list of products and customers - then keep the database together. You dont gain anything by seperating the databases. Thats purely a 'human' issue - to the server its just bytes on a disk. It doesnt care if its 1 or 100 databases. But if you do split it, you then have to ...


9

I think your confusion is between the technical term "client" (as in client-server) and the business term "client" (as in, a paying customer). Multi-tenancy usually implies a server serving multiple business clients, each with their own separated environment, using shared hardware and software. For instance, I have several Wordpress blogs running on a ...


8

Yes, that's it. But wikipedia's definition is not general enough. It does not address multi-tier architectures or newer forms of architecture like SOA or microservices. Multi-tenancy is about software systems and data isolation. Some examples: a multi-tier system with a unique database can be multi-tenant. Example: a SAP system is composed of a ...


7

In the multi tennent systems I have worked on as a DBA and Developer we used 1 database per client. That database was completely self contained and did not rely on any shared database, not even for things like the states in the US. To make standing up a new client easy a model database was created that had all the things like States prepopulated. The ...


7

I am asking myself the exact same question at the moment. I am leaning towards the multi-instance single tenancy solution but have not taken a definitive decision yet. Let me share some of my thoughts : The main historical advantage of the multi-tenant architecture is a better use of infrastructure resources, by mutualisation (single OS, single Database, ...


6

"I think it would add a level of complexity to the app" ... opposed to what - using just one schema for all tenants? Then typically the opposite is true. Implementing multi-tenancy in one schema adds the complexity to each and every table where the data might belong to different tenants, which means this kind of complexity will go also into your app. ...


6

Yes, you certainly should store all client modifiable content into the database (or accessed via the database) so that the clients can maintain this for themselves. You will need to spend some time writing the admin functions but you will reap the rewards in the long run. You are right you cannot have the situation where all changes need to go through the ...


6

There are some trade-offs that you'll have to do: 1.Dedicated instances Advantages: No additional development needed nor conceptual work (less time and cost to market the product) Every customer is fully independent, which means technical flexibility (you could choose hosting facility depending on location, volume pricing, etc...), customizability (a ...


5

The primary concerns of this design are security and size. But before I get there, I want to clear up a misunderstanding: As wrong as it seems, would it be better to sacrifice normalization and include the franchise ID directly in some or all of these child tables? I see why you might think this: if you consider franchise ID as an attribute then ...


5

While I'm not really an experienced architect, but as the question stands, you might not want to change much at all. You just add a Blogs table, and a "blog_id" column to posts (just as your "comment" probably already has a "post_id" column). Well, depending on which entities you want to separate, you might make other changes, for example, you say you want ...


4

Since the clients databases and functionality are diverging, then it means that at one point they will end up being different systems, so in this case I would recommend separate systems since the costs of maintaining the customizations for each client will outweigh the benefits of a single database system. Single database systems are best for when the ...


4

Don't put all the authentication data in the shared database. Instead of storing tenant identity and authentication details in a separate database, you can store identity (username) and redirection details (server/instance.databasename). Then you can handle authentication at the tenant's database. I'm assuming admins are going to send invitations to ...


4

Supporting both options is possible also (a pool of tenants across multiple instances). I favor multi-instance cause of the natural isolation. Each customer's instance runs in it's own processes and it's data is isolated in it's own database. You can upgrade instances to new versions on a per customer/instance basis when desired. Tenant based systems come ...


4

If all code fetching data from the database is written correctly, then there is no need to have tenant id on each table. However, in practice bugs are inevitable, so there will at some point be improper data access in such a schema design. By including tenant id on every table, you can set up row-level security at the database level so that improper ...


3

A multitenant architecture has the benefit of scalability as well as security. When you have all the client data in one table, all customers has access to all customer data, not calculating the code you write to restrict this access. In a multitenant architecture you can set up different users in the db and have a much simpler design of the authentication ...


3

There is no one "correct" way to do anything in programming, even with a cloud-computing specific architecture such as Windows Azure. A cursory search of the MSDN documentation reveals that Azure does in fact have some useful functionality to help you separate your client's data. It appears as if you could decide to give each client their own SQL server ...


3

It may or may not be worth the trade off in your situation, but maintaining data integrity is easier with a single database. In MS SQL Server at least, you cannot foreign key from one database into a different database. You can simulate foreign key behavior with triggers, but it's not particularly elegant. In addition, creating local copies of the data can ...


3

You're missing some concerns. Problems will come with growth. If you can assume that someday you'll grow bigger than one DB server - one complex database will definitely cause you a headache. Unless you'll invest in architecture in advance. But it is also expensive step ) So, just do not forget, that it is both many times cheaper and many times easier to ...


3

If you build the app to support multiple tenancy, then your future tech reps will have a choice about how to deploy it. If you find a customer who insists on a single-tenant instance, you will be able to support them. Some customers prefer that approach for infosec reasons. At the same time, you'll be able to run multitenant operations. That will help you ...


3

You nailed the two obvious choices. But neither is quite as hard as you say, and which makes more sense depends on how much you expect to do in integrating content of one blog in another. Separate Database approach: This is the simplest to implement approach. You already have a connection string for connecting to your database. Just wrap it behind a ...


3

Ok heres my vague architectual advice. I think you are correct in equating the folders to indexes on a single table rather than schemas. But accessing a single table through views can occasionally be problematic. I would always advise just modifying the SQL statement rather than running your SQL on a view. Secondly, the shear volume of data could also be ...


3

After further research I am satisfied with an answer so I will answer my own post. I see that Microsoft recommends using a prepended-tenant (client ID in this case) in this particular article: https://docs.microsoft.com/en-us/azure/architecture/multitenant-identity/token-cache Furthermore, I see that the framework I'm using has specific options & code ...


Only top voted, non community-wiki answers of a minimum length are eligible