One Big Database vs Many Smaller Databases
-
Hi all,
I currently have a Database for an application used by many departments. In that Database, each table that specifically belongs to a department contains a DeptID field so that the application can retrieve & process data correctly. There are also some tables used together (shared) by all departments. All of these tables reside in one Database.
I just feel there's a way to tweak this design. I have a thought that each department should have its own Database and there's also a Database which stores the tables that shared by many departments. I'm not sure whether my thought is better or not, I'm afraid that this design will cause more overhead for my application.
Also, if I have to seperate the database for each dept, I become more confuse on how my application can connect to the right database. Currently, I use DeptID associated with the logon user so that the application can get the correct data.
So, for performance wise, should I seperate my one Big DB into the smaller ones?
I welcome any opinions and thanks in advance.
Hendry
Edited by - Henmi on 11/18/2002 02:46:05 AM
-
I think rather than looking at the semantics of the database design, you should consider how the business wants to use the data and what it's plans are going forward. For example, if you split the database on department, what would you do if two of those merged, or one was to split into sub-divisions? There could also be issues with the client code being changed - all of this is expecting to find the data in one place...
But then there are more practical issues to solve. One database can have one set of maintenance jobs, one backup/recovery routine and one disaster recovery plan. Splitting into many smaller db's exponentially increases the complexity for you as the DBA. I suggest also that if you're thinking of a federated database you look very carefully indeed as that's a big step.
-
There is no performance reason to split into may databases per se. If your tables are very big it might be usefull to split the tables (and put them in seperate databases to prevent too many changes in your code). I cannot tell you what very big is, but IMHO it is at least millions of rows.
Joachim.
-
I'd vote for one. The extra complexity of splitting it out can be managed of course, but I'd only do it when I hit a really compelling reason. One might be that I couldnt do backup or maint operations in the window I had (filegroups offer an alternative there). I've got an app at work that partitions client data by database, means I have 250 instead of one. I'd cheerfully take the one!
Andy
-
Thanks all for your great feedback
As suggested by all of you, it seems one database will make my life easier.
BTW, any other opinions from the opposite side?
Hendry
-
quote:
BTW, any other opinions from the opposite side?
Don't know if you're done with this topic, but Antares686 and I had a good discussion a week or two about it... :
-
jpipes, Thanks very much for the link. It's really an interesting discussion. Anyway, let me have a favor to ask you one question.
What's your primary goal for splitting up your database? Is it for performance wise?
Thanks
Hendry
-
quote:
What's your primary goal for splitting up your database? Is it for performance wise?
No, actually, it is not. There are a number of (valid) considerations on both sides of the coin here. The points made above regarding a single backup procedure and ease of maintenance for a single database are excellent points. I believe the most compelling reason to split databases into more than one database is when you want to clearly separate logical units and shared data.
It is my preference to have one database that houses "shared" or "common" data elements, and other databases that store "application-specific" or "subjective" data. There is, as has been previously pointed out, no significant performance improvements by doing this; it is simply a choice to separate logical units into separate dbases.
For example, in the "common" database, you could have tables storing employee information, for instance a company id, name, address, etc., and than in a Payroll-type "application-specific" database, store employee information that is, perhaps, more sensitive, for instance SSNs or pay information. Of course, to maintain referential integrity, you would need to have a foreign key relationship field in your application-specific database table that would, through a trigger or batch process, enforce a relationship with the primary key field in the shared database table.
I know this may seem like a lot of work to separate logical units, but once you get the methods down, it provides for a database architecture that more closely models that shared data environment that many companies face today. It incents application developers to utilize common data in a shared data source rather than a) store duplicate information in each database, and/or b) build complex processes to duplicate that data/ share that data.
Of course there are downsides, some of which have been pointed out above. It may be too much effort and overhead in a multi-server environment to pull something like this off, for instance...
-
Implementing data level security can be a ***** so using multiple databases can solve this, but then, as said, the issue of consolidating, or splitting of data becomes and issue.
You can or course use partitioned views so that one database is a representatin of all the databases, so you have the best of both worlds!!
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Got it! Thanks again Guys
Hendry
-
Splitting your data base into multiple data bases is usually driven by security reasons. If you have tables that are shared, splitting them into multiple data bases will only cause you headaches. If you don't need to absolutely guarantee that only 5 users can see certain data (e.g. personnel info), keep them all together.
If you end up splitting it out, you might consider a common data base where all common procs and tables reside and limit access to update that data base. (setting it to read only keeps it fairly stable 🙂
-
One additional thing that I think should be added in regards to maintaintence which I got from another thread is this.
Singles database all users interact within the same envrionment. This is great for do backups and other types of maintainence in that a single piece is all you have to worry with. However consider what happens if something causing a need to restore the database based on one applications issue. You end up losing all transactions for all tables of all applications after the point in time you restore to. So now you have adversly affected other applications and users that by having seperate databases would not have been an issue.
Just food for thought.
-
Another downside to splitting - if you're using transactions that span two or more db's and then need to recover one...what fun!
Andy
-
quote:
Another downside to splitting - if you're using transactions that span two or more db's and then need to recover one...what fun!
Good point. For instance, if you have a transaction which updates the inventory table (in an inventory database) in response to an update of a work order table (in a work order database), what issues will you be dealing with? If you put the transaction in a trigger (MS recommended approach?), do you need to worry about problems in restoring? If not implemented in a trigger, then what are the issues/options available to the developer/dba?
Good food. for thought... 🙂
-
That is a very good issue indeed. This is when you get into splitting some further where they are transactionally affected and mix only those that relate to each other and when a transaction occurrs. Unless the data that is linked on is static and once created will never change. But bay far you still affect fewer users if you are carefull and those transactions that cannot be planned simple enough you should be aware of the applications order of creation so you can correct issues.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply