Multiple databases with same login (.net core 3 API)

Question

As I am working on a new platform (.net core 3 API) where every company has a separate database. I'm finding myself with an issue I can't find a solution to on the internet.

Companies have different users, but users should be able to login on different companies if they have the right to. Having a master database with all companies/users would work but then I would lose the foreign keys to the users? How is this issue solved by best practices?

I'm not talking about SQL users but users that are created on our application.

You can work with linked servers to keep your foreign keys intact. — Steven Lemmens
– Steven Lemmens, Commented Nov 28, 2019 at 11:01
@StevenLemmens that doesn't work with Entity Framework Core though. — Jesse
– Jesse, Commented Nov 28, 2019 at 11:04
@Jesse EF isn't the problem, it can work with three-part names or synonyms. The real problem is that cross-database foreign keys aren't allowed. You'll have to use triggers to handle validation. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 28, 2019 at 11:10
Having a central authentication service isn't a bad idea - in fact, it's already available in the form of Identity Server. Even ASP.NET Identity uses (should use) a different model for users from the rest of the application. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 28, 2019 at 11:14

David Browne - Microsoft · Accepted Answer · 2019-11-28 14:10:29Z

1

Companies have different users, but users should be able to login on different companies if they have the right to. Having a master database with all companies/users would work but then I would lose the foreign keys to the users? How is this issue solved by best practices?

The best practice in a database-per-tenant solution is to maintain a central database with global configuration data (like this), and also replicate the relevant data to the tenant databases, as needed. So you should have a central database where you administer the relationship between users and companies, but then copy all of the users for each company into their company database.

Typically this reference data is slow moving, and so you can manage the copying by either writing to both databases directly from the application, or by running a batch job.

answered Nov 28, 2019 at 14:10

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple databases with same login (.net core 3 API)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related