36

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 ...


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

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

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 ...


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

"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. ...


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

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

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

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

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

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

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

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

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

I need to use some common database which then prevents me from joining tables in the natural way, or using e.g. EntityFramework out of the box. You can use EntityFramework.DynamicFilters for this. It allows you to put a dynamic filter on your model that will be applied to all queries (both direct queries and loading related entities). For example, I use ...


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 ...


2

Just looking for suggestions, experiences ... Design for the new functionality. Without the big picture how can you - much less I - assess a microscopic issue like a Customer interface. Once you see what you need you can better assess how the existing code base might change. Once you see what needs to change you can contemplate how. Design is everything. ...


2

A schema in Postgresql is akin to a "database" in, say, MySQL; it is a namespace containing a distinct and separate collection of database objects (tables, views, stored procedures, triggers, etc). Creating a schema for each tenant will allow you to Hold a database connection open and switch between schemas, Query across multiple tenants using UNION, and ...


2

It seems like you're viewing the problem as one of re-configuring your application based on the subdomain it is accessed from. Given that your university specific data is basically user data - i.e. it will change as users sign up for and use your application, I think a better approach is to store all of that stuff in your database and then create a service ...


2

Adding a "tenant" column (ClientId in this case), and using that to filter by tenant, is logically identical to having a separate database for each tenant. The only difference is, you have to remember to use that everywhere. This is true for everything in the database, not simply your nested sets. You are safe, assuming you are careful in writing the stored ...


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