5

I'm having trouble coming up with a name for this problem, but I feel like its been solved before. I think my naiveté is preventing me from typing the right words into Google to get the results I need. So I apologize in advance if this question has been asked before.

Anyway...

I'm designing a multi-tenant web app where the structure of the data is dependent on the tenant. A tenant in this case is a business that wants to use our services, and a user is an individual that works for one of those businesses. The system is all in one place, as in there is one website that all users log in to, depending on the email address they type in, the tenant they are assigned to is automatically decided at the time of registration (I'm not sure if this is even the right way to go about this).

Each tenant wants to store information about the people who work for them on our system. These people are not necessarily users, and most importantly, this people information is used on other parts of the website that may be more general.

Here is the problem: each tenant may want to store different information about their people. For example, say Tenant A wants to store Name, Email, Phone, and Address, but Tenant B wants to store Name, Email, a picture of the person, and their favorite food (I don't know).

We're using SQL Server to store our data. My instinct is to just create a different table for each tenant, but I foresee things getting messier as the number of tenants increases. If we had 5 tenants, sure individual tables would work... but what if we had 500 tenants? Would we need to have 500 tables just for all of the people we're storing information about? But it gets worse. The differing information isn't just with each tenant's people; tenants may want us to store different information for other services we offer to them.

Is it possible to generalize this varying data structure somehow? Is there anyone out there that has dealt with something like this before?

  • IMHO opinion storing in a relational DB stuff like Json or Xml or any other serialized string as data it's a huge workaround. I'd prefer the EAV model as described in the answer below. But, another option would be to use some non-relational DB for storing additional data that has no predefined structure and which might have its structured changed as well; then you could use some NoSql database in order to persist this "custom" info for each tenant. Your application would need to the relational DB and also the NoSql db and retrieve the data for the client. – Emerson Cardoso Dec 14 '17 at 18:35
  • "My instinct is to just create a different table for each tenant" - great Scott, my instinct tells me exactly the opposite ;-) – Doc Brown Dec 14 '17 at 19:55
  • Commingle multiple tenants in the same database is not good for performance or security. – paparazzo Dec 15 '17 at 15:35
2

You would need to separate the data that is standard across all tenants, and the data that will vary from tenant to tenant.

You could store the varying data in XML or JSON. Either as a single field in a larger table, or multiple fields, it will depend on the nature of the data. SQL Server has the ability to query into JSON structures, and probably (but I'm not 100% sure) can do this for XML as well.

Schemas can be defined for XML, so you could still validate data for each tenant for that tenant's custom schema, if you wanted to. I believe there are ways to define schemas for JSON too.

If you don't want to go that route, there's the good ol' Entity-Attribute-Value model which could handle this too (https://en.wikipedia.org/wiki/Entity–attribute–value_model)

Of course, you would also need to ensure that your system allows users to manipulate their custom attributes (add new ones, alter existing ones, remove, etc...) - whether it all gets dumped into JSON/XML or an EAV model.

  • 1
    I'm surprised that this isn't higher up. XML or JSON seems like the way to go, unless you want to start looking into Document databases. Although the non-relational aspect of that may not be a good fit for the rest of the application. MS SQL Server and Postgre, among others, have good support for JSON and XML data types. – neilsimp1 Dec 15 '17 at 13:47
2

I actually worked on a project that was migrated as a feature into a much larger software. Suddenly we had much more companies that wanted to access our application and each company gave us different user information, which resultet in a mess in the database.

Our solution was to add a basic user table. The table holded all the information that was the same for all companies: Names, password, email, phone and language. In the end this was just the information that our service needed in order to work properly. Additionally, we added tables for each company. We had Company-A-User and Company-B-User and so on. These tables contained the additional and different information and a foreign key to the basic user to reference the necessary information. In our application we could do almost everything with the data of the basic user and only in company-specific services it was necessary to access the other tables.

My advice: Check how much of the information your services actually need and put it into a basis table. If you need the additional information a company provides add a user table with a foreign key to the base table. If you do not share any information at all, you need to do individual tables anyway.

2

The problem is not new, and there is no "one size fits all" solution for this. If you want to keep your system maintainable, you need to analyse the different requirements for every single attribute and make a decision case-by-case how to design it:

  • either as a standard attribute which is available for every tenant (and so best modeled as a standard column in your database)

  • or an attribute which is available for lots of tenants (so it could be modeled as as a standard column in your database as well, but only shown in the UI for those tenants who require it).

  • or a custom attribute which is best modeled using an EAV approach, as suggested by @FrustratedWithFormsDesigner, or an "XML or JSON" approach (this is typically the case when the tenant wants to add new attributes freely by himself, maybe on a "per record" base)

  • or, if the attribute fits best into a "child" table, as suggested by @pfuetz (maybe a tenant-specific child table, but I recommend to have such child tables associated with a named feature of your application, and then decide which tenant gets access to this feature)

Note that standard columns are often much easier to manage than EAV approaches: it will be easier to implement business logic for them, standard SQL queries will be simpler, user interface design can be made more specific to the column, database migrations will often be easier, they are much more self-documenting and so on. So I recommend to use EAV rarely, with caution, only where you really need it.

You will definitely run into maintenance problems when you start to duplicate tables for each tenant and modify each copy a little bit. Do this 10 times, and you will have 10 times the maintenance effort for every copied attribute. So I would heavily recommend against that.

0

You are going to offer tenants the ability to "model" the data structure... One way or another.

I'd advice you to take some sample requests for multiple tenant's data and workflows, and find out the generic structure common across all. There would be a few common concerns like:

  1. User management
  2. Access control
  3. Authentication
  4. Some common functionality like user hierarchy

So, you might start with base tables for the common stuff. Then allow tenants to add fields to these entities in a dynamic fashion (formatting, validation and all) in a separate, EAV extensions tables, one for each base table. In essence similar to what some popular Content Management Systems refer to as "Custom Fields".

-2

A few ideas:

Individual tables

I don't think using one SQL table for every individual tenant (or object structure) is necessarily bad.

I've personally worked with Oracle databases containing around 1000 tables (with millions of rows per table). They needed some parameter settings to overcome a few resource limitations, but worked quite well.

Advantage is, all database operations stay straightforward. The tables stay compact. And it's easy to implement customer isolation by granting privileges on tables.

Depending on the database access layer you are using, implementation might get clumsy (esp. Object-Relational Mappers), but you write "My instinct is to just create a different table for each tenant...", so I think you analyzed that aspect.

Grand Unifying Table

Another option is to create one monster table with all columns you know right now, and as soon as a new customer needs another column, alter the table adding that column (don't know how efficient that operation is on MS SQL Server).

You still have straightforward database operations, but one big growing table, slowing down one customer because of some other customer's mass-data. And customer isolation isn't as straightforward.

So, I'd go for the individual-table approach.

  • 1
    Both ideas will probably lead the OP directly into hell, at least when he picks one and tries to solve all his problems with that ;-) – Doc Brown Dec 14 '17 at 19:58
  • @DocBrown Could you please elaborate on your concerns? – Ralf Kleberhoff Dec 15 '17 at 9:39
  • Most of it you find in my answer. Individual tables (with duplicated attributes) have a high risk to cause maintenance problems. Putting all potential attributes in one monster table is seldom a good approach, too - if you think for a moment of one tenant who wants all available attributes in his application, you would probably not put them in one monster table either. Instead, you would model them according to normalization rules in different child and link tables. And such a split model is still useful even one when each tenants sees only a subset of the whole model. – Doc Brown Dec 15 '17 at 14:11
  • ... so both of your suggestions heavily violate normalization rules, just in different ways. And I don't think a recommendation for just one single solution, without individual considerations on a per-attribute basis, will serve the OP well. – Doc Brown Dec 15 '17 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.