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