Last post Jun 18, 2019 08:10 PM by JohnLocke
Jun 18, 2019 06:53 PM|JohnLocke|LINK
I am in the midst of re-writing a handful of applications in asp core mvc. The apps in their current state all have their own separate database, and for the most part this is OK. However, one commonality between all the databases is that they each contain
an identical "Users" table with basic properties such as Name, Email, Phone, etc. The problem with this is that if I want to update any property of the Users table in one database, the other databases require updating as well to stay in sync. Ideally it
would be great if all the applications could utilize the same "Users" table so regardless of which app I choose to update User properties, it will be reflected in the other apps.
Where I see a problem is that each app has other tables in their respective database with relationships to the Users table. So I don't know how I can have one table shared by multiple apps, while maintaining relationships with other tables. If anyone has
some advice for how I can handle this, I would greatly appreciate it.
Jun 18, 2019 07:17 PM|mgebhard|LINK
If the databases are on the same server then you can use fully qualified names.
If the database or on separate servers, you can use a linked server.
ADO.NET can work with both schemes above but EF can not.
These days I uses authentication services (IdentityServer4). Not sure if that's a solution for you.
Jun 18, 2019 07:37 PM|JohnLocke|LINK
The databases are on the same server. I'll do some research on your suggestion. Can foreign keys exist across tables of different databases? The apps currently utilize complex data models via Entity Framework, so if I could keep that intact somehow, that
would be great.
Unfortunately our authentication service is managed by a larger entity using active directory. We are a child node of their organization. While we can authenticate against their AD, we cannot request special claims, properties or policies for our apps
(which is why we have a Users table to hold extra properties).
Jun 18, 2019 07:52 PM|mgebhard|LINK
Can foreign keys exist across tables of different databases?
Unfortunately no but you can hack together a trigger.
FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE
The apps currently utilize complex data models via Entity Framework, so if I could keep that intact somehow, that would be great.
As far as I know, that's another no. EF reads the table schema of the current DB. But, you could take advantage of stored procedures or raw SQL. I use this approach in an app I support. I have the same issue where I need to join tables is two different
Jun 18, 2019 08:10 PM|JohnLocke|LINK
Thanks for the information, that all makes sense!