multi-tenant Application,

[PaulAmmoun]

Hi,
I have a multi-tenant application (one database with a company id field).
It's getting complex to manage. I am thinking of switching to having a different database per company.
is there any easy way?
What do you think? is it better to have a different database for each company or keep it in 1 database?

Thank you for any input
Paul

[Steve Workings]

I have a couple of these types. I can't imagine doing it with separate databases to maintain -- I'd rather do it with one database. Tell us why you think multiple databases would simplify?

[PaulAmmoun]

I have few issues, such as logging in, I can't have the same user name twice.
The app has a mobile application (offline), so users need to enter a "company code", so I know which company to load the data.
Checking for duplicates is tricky, I always have to include the company code.
But the main issue is with users logging in, they need to have different username across companies.
of course there are solutions but I was thinking about different databases...

[lvasic]

I have done both. Each has its pluses and minuses. I have one component where the user logs in and then after you log in you can choose which company you want to see. Are you using the extended Users table in security?

[madtowng]

I might have put too much effort into the project, but I used to use a landing page (www.comp1.com, www.comp2.com, www.comp3.com), and use that
to set the various variables (database, logos, phone numbers) from that.

[Russell Craycraft]

Separate databases can become unruly. If you fix/add something to your project it'll be hard to ensure it works across all, adds testing time, and more break points.
I use email addresses for logins. It also makes password resets and 2fa convenient.

I'd make a copy of the project and the database. Then hopefully you can run a sql query to update all of the original usernames from their email field. Give them the test site to ensure they can log in, and after you speak with your clients, give them 90 days before you switch.

This is of course a 50,000 ft. comment, and Lee and Steve may have some great input to fixes as well.
(Also, Lee is on it with using the Extended Users Table).

[peteconway]

I did this a long time ago I was working on a multi-tenanted site that may have used unlimited entities. I made the Security ID a generated code (whatever way GUID etc.) the extended user table with a readable login ID PeteConway and the tenant ID is in the extended user table, Once security is summited, I used the Extended user table as the login: for a given entity - at this point the AA security system has no knowledge of what's happening. So I'm on the ACME landing page and I enter PeteConway then use a SQL view to see if I exist for the Entity ID of the landing page, If I do then ask for the password then call one of the web security functions using GUID as the user ID and see if the password is ok - if so yes - no exit.

Hope that helps.

[mikeallenbrown]

In a couple different multitenant web apps I'm using multi databases. One database is my control or system admin DB. It contains Alpha's security tables and other tables/data that all tenants use. Then each tenant gets their own DB. I use Alpha's dynamic SQL connection method. It all works well together. I use stored procedures to sync-up the databases when ever I make a change to one of them.

As for why I chose multi DB: Admittedly part of the reason is the challenge of it. After working through some kinks and figuring out a solid/easy way to update multi DB's it's really no different than a single DB. The other part is that it feels more secure. Each DB has their own unique encryption. Another reason is the occasional subpoena of data. Easier to follow through with that when the effected user has their own DB.

[ChristopherG]

I note Mikes comment above with interest but I think it somewhat depends on how complex your DB will get.

We run a multi-tenanted platform deploying multiple functional modules (~10 complex modules and another 8 supporting modules) for multiple Groups, each with multiple Companies. Users are defined within and limited to a single Group of Companies. Users have company/module specific security roles and on login, their roles for a 'default' company are loaded. They can then switch to any other company they have access to within the group. All alpha components and lookup controls are filtered by groupid and companyid session variables. Although this sounds dramatic, we are primarily focused on servicing the needs of our own group of companies and so our active daily user list is relatively small (<50).

We run this on a single SQL Server database with over 4200 objects (tables, triggers, views, functions, stored procedures etc) and AA has about the same number of components. Given our rate of development and the granularity of the stored security data, I would not want to be synching changes across multiple databases. That being said, I am not sure how this approach will scale if our daily active user count increases substantially.

One advantage of the single database approach is that we have deployed modules in which, by design, companies in different Groups can share data across the normally fixed Group barrier e.g. companies in our industry often share R&D data and colloborate on research. This would be more complex to achieve in a multi-database solution.

But yes, multi-tenancy adds a layer of data and security filtering that is more complex and which you have to be super diligent about implementing on the AA side.