What is the best way to query data from multilpe tables and databases?

Question

I have 5 databases which represent different regions of the country. In each database, there are a few hundred tables, each with 10,000-2,000,000 transaction records. Each table is a representation of a customer in the respective region. Each of these tables has the same schema.

I want to query all tables as if they were one table. The only way I can think of doing it is creating a view that unions all tables, and then just running my queries against that. However, the customer tables will change all the time (as we gain and lose customers), so I'd have to change the query for my view to include new tables (or remove ones that are no longer used).

Is there a better way?

EDIT

In response to the comments, (I also posted this as a response to an answer):

In most cases, I won't be removing any tables, they will remain for historic purposes. As I posted in comment to one response, the idea was to reduce the time it takes a smaller customers (one with only 10,000 records) to query their own history. There are about 1000 customers with an average of 1,000,000 rows (and growing) a piece. If I were to add all records to one table, I'd have nearly a billion records in that table. I also thought I was planning for the future, in that when we get say 5000 customers, we don't have one giant table holding all transaction records (this may be an error in my thinking). So then, is it better not to divide the records as I have done? Should I mash it all into one table? Will indexing on customer Id's prevent delays in querying data for smaller customers?

Answer 1

I think your design may be broken. Why not use one single table with a region and a customer column?

If I were you, I would consider refactoring to one single table, and if necessary (for reverse compatibility for example), I would use views to provide the same info as in the previous tables.

---

Edit to answer OP comments to this post :

One table with 10 000 000 000 rows in it will do just fine, provided you use proper indexing. Database servers are built to cope with this kind of volume.

Performance is definitely not a valid reason to split one such table into thousands of smaller ones !

Answer 2

Agree with Brann,

That's an insane DB Schema Design. Why didn't you go with (or is an option to change to) a single normalised structure with columns to filter by region and whatever condition separates each table within a region database.

In that structure you're stuck with some horribly large (~500 tables) unioned view that you would have to dynamically regenerate as regularly as new tables appear in the system.

Answer 3

The architecture of this system smells like it needs a vastly different approach if there are a few hundred tables and each has the same schema

Why are you adding or removing tables at all? This should not be happening under any normal circumstances.

Answer 4

2 solutions 1. write a stored procedure who build the view for you by parsing all table names in the 5 databases and build the view with union as you would do it by hand.

1. create a new database with one table and import each night per example all the records of all the tables in this one.

Answer 5

Sounds like your stuck somewhere between a multi and single tenant database shema. Specifically your storing it as "light"multi-tenant (separate tables vs separate databases) but querying it as single-tenant, one query to rule them all.

In the short term have your data access layer dynamically pick the table to query and not union everything together for one uber query.

In the long term pick one approach and stick too it. One database and one table or many databases.

Here are some posts on the subject.

http://stackoverflow.com/questions/13348/what-are-the-advantages-of-using-a-single-database-for-each-client

http://msdn.microsoft.com/en-us/library/aa479086.aspx