Dilemma - have multiple dbs or not?
-
Hi,
We are developing an application that will run for multiple countries but users need to see only their country-specific data. Data will sit on one server. The question is, should we split up the data for each country into separate databases or tie access for users based on say, a key like country id. I am trying to figure out which will be better (maintenance, performance, isolation, etc.) in the long run.
And there will be super admin(s) setting up the users. If we go the multiple databases way, won't we need something like a master db (for the super-users).
I am tempted to go the single database way for now (since the server is one anyway). Can someone guide me through this?
Thx for all the help
Vis.
-
We faced a similar situation sometime back when our company went or a spree to purchase a few other companies. We were assigned a task to come up with a database system for all the companies where there was a chance that some "super users" would need to see data across companies but for the most part employees of a particular company saw only their set of data. All the companies have similar type of data and similar processes. After much deliberation we went with one common database for the following reasons:
1: Ease of storing data
2: Ease of management
3: Ease of creating web GUIs
4: Ease of creating reports across the entire portfolio
5: Need only one set of data access strings and one set of processes (we have a total of 250 automated processes that run on the data).
And we have not seen any real degradation of service or response times in applications.
To give you an idea of the amount of data we deal with in multiple databases:
- We have quite a few tables with 20-30 million records in them
- Some tables with 50+ records in each table
- Rest of the tables have less than 100,000 records in them.
Our setup:
- 11 SQL 2000 database Servers - Xeon processors - mid grade servers - not really high end.
HTH,
Vishal
-
I would advice you , to store the data in single database.
Regards,
Sudheer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply