CODEWITHSUNDEEP
c#sqllinqarchitecture
Tono Nam
Tono Nam

Reputation: 36048

Query multiple databases with one query

I have an architecture where I have one primary database MyMainDatabase. And then I create a separate database for each new customer CustDb###. On my MainDatabase I have a table to keep track of all the customers databases CustomerDbTable.

The customers database has a table called Invoices. The invoice id is a GUID therefore the probability of one repeating is very small.

So here is my question I have to look often for an Invoice given its id (GUID). I do not what approach to take:

  1. Create a table in MyMainDatabase called AllInvoices that will have the columns IdOfInvoice, CustDatabaseName. Every time I create a invoice on the customer Database I will also create a record in MyMainDatabase

  2. Create a query that will enable to search an invoice in multiple databases. I have the name of the databases. I will prefer this apprach because on the first one every time I create a new invoice I have to remember to create it also on the MainDatabase

Upvotes: 1

Views: 679

Answers (3)

stead
stead

Reputation: 167

Would it be possible to look up the appropriate connection information based on login? You could map your users to a particular customer database within the central database(or allow the selection of said customer database within the app) Then all queries could be written directly against the customers set of invoices.

If you truly do need to maintain separate databases and search across all said databases as described, Id go with your 2nd approach. However, if key invoice information should be indexed centrally for quick global searches, you should pull this key data up into the central database.

Upvotes: 0

Robbie Chiha
Robbie Chiha

Reputation: 409

In Sql Server you can access using dbName.Schema.TableName

    SELECT Invoice FROM [MyCustomerDb1].[dbo].[InvoiceTable]
    WHERE <Condition...>

You could then union all to get from multiple.

    SELECT Invoice FROM [MyCustomerDb1].[dbo].[InvoiceTable]
    WHERE <Condition...>
    UNION ALL 
    SELECT Invoice FROM [MyCustomerDb2].[dbo].[InvoiceTable]
    WHERE <Condition...>

That being said I think this is a pretty poor Architecture to have a separate db if it is one solution and is going to be deployed on one server.

If it is going to be deployed on multiple servers then an AllInvoices table will be required.

A better solution would be to have one invoices table in your main database with a ForeignKey CustomerId.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Sometimes, for security reasons (primarily) you need to create different databases for different clients. If you can avoid this architecture, then that will generally make your life simpler. I assume that you have good reasons for this architecture.

Given the situation, you can take either approach.

If you are going to store the invoices only in the original source databases, then you will need dynamic SQL in order to access them. In other words, you should have a stored procedure that will create the dynamic SQL and then return the set of invoices, using the list of databases in MyMainDatabase. You will need to manually check for appropriate security.

I actually prefer the first method (a central table), although it does have some additional complications. If an invoice changes, then you need to propagate the updates as well to the main database. This becomes a data propagation problem. But, it is easy enough to implement using triggers. One challenge would occur when the databases are actually on separate servers. Then you have to take into account that some databases (including the main database) might be down.

If the databases are all on the same server, you could use a hybrid approach. In this approach, you would have a view in the main database that brings together the invoices from the different databases. You would have a stored procedure that would regenerate the view as new databases are added or removed.

I don't think there is a right answer. There are just practical approaches that fit better or worse with your requirements.

Upvotes: 1

Related Questions