Database structure

Question

I've been searching for a while how to solve this problem, and finally I've decided that maybe it's better to ask for some help.

We have two different application (app1 and app2), each of them with a different database (db1 and db2). Then we have another project to manage those applications data using both databases. This would be app3 with db1 and db2.

In db1 we have a clients table to set which client have active which application. Exemple: client 1 has active app1, client 2 have active app1 and app2, and so on... this is managed from app3.

The problem is that in app2 we need to access to this table in db1 to know if the user has that application active to log in.

We were thinking to separete this table in another database, but as we are not database experts we are wondering if there is any other better solution to solve this.

It's possible that in the future will exists a forth application with same clients and different database so we'll have the same problem. And duplicate clients it's not an option.

We're using SQL Server and .NET Core.

Thanks

Answer 1

It is possible to query across multiple databases in SQL Server if all the databases are on the same instance, or there are linked servers set up between them. The login would need access to all the databases.

For your design question, I think having a third database for the separate concern of the client connections makes sense. This would allow the apps to focus on their specific data requirements in their databases and the client connections requirement focus on just that without crossing domains too much.

Answer 2

As others have pointed out, you're allowed to access Tables, Views, Stored Procedures, Functions, and most other objects across databases with cross-database querying. So from that standpoint you shouldn't run into any issues regardless of where you put the table.

It moreso comes down to the business logic and management reasons of whether you want to put that table into its own database. I've done it both ways and personally (at least for non-big data scaling / high workload apps) I like to keep all my business application tables under the same database but use a different schema for each application.

So in your context there would be a BusinessApplicationsDatabase, and a schema for App1, a separate schema for App2, and the default schema dbo (or you can make your own schema too) would be for universally used tables. Doing it this way organizes the data objects better, in my opinion. It also allows them access to objects that are not available cross-database, such as User Defined Types (whether those are good practice to use is for another discussion, but as an example). It also makes the database more portable and manageable in regards to backups and syncing or moving it to other servers. Additionally, security management within the database server becomes a little bit better in a sense that you have the granularity to GRANT and DENY permissions for a schema, but you only have to map your Users to one database instead of multiple.

The use cases where I wouldn't do that kind of design necessarily is if I know one applications data is going to grow at a much larger scale much more quickly than the other applications, and you want to separate the management of the growth of the database files. More so, if you need to replicate the data of one application across multiple servers with AlwaysOn Availability Groups, then having separate databases is better (again unless the data isn't large) since AlwaysOn Availability Groups synchronize whole databases. But these are more edge cases in my opinion, and less common the previously mentioned benefits of keeping things within the same database but leveraging the power of schemas.