We are building a large application (a financial system) that, after
normalization, would approximately need 1500 or so tables. The question is
should we build this as one database or should we separate it out into
multiple databases.
There is a case for separating out into 5 or so databases based on
functional modules within the financial system (GL, AP, AR etc.) . Problem
though is they are related to one another in some way.
In a single database case, some standard tables (like U.S. States, Notes
etc.) can be a single table. Also it avoids cross database talks so ease of
development is more.
What are the performance advantages/disadvantages of one way or the other?
Does SQL Server performance get affected if the number of tables is large, as
in my case? Any inputs, suggestions?

Thanks
Naveen

Naveen wrote:
Show quoteHide quoteKeep them in one database, there is nothing to be gained by splitting
them apart.  That said, you can create multiple filegroups within a
single database, and specific tables can be stored in specific
filegroups.  A common optimization tactic is to put high-traffic tables
into seperate filegroups that are isolated on seperate disk spindles,
which spreads the I/O across multiple hardware channels.

Naveen (Nav***@discussions.microsoft.com) writes:
The number of tables has little do to with it. But if the database gets
huge, you may want to consider filegroups and that sort of thing.

Having separate databases only makes sense if one module has to be available
while another is down for maintenance.

What you could consider, though, if you are on SQL 2005 (and which you
should be if you are in the development stage now) is to use different
schemas for the various modules. This gives no performance benefits,
but can be an ease for development, as different components gets separate
name spaces.

Naveen wrote:
Show quoteHide quoteUse multiple databases only if they will have different requirements
for availability, security or other admin-related reasons. There is no
performance advantage to be had from creating additional databases
(unless you put them on different servers).

I'm curious as to why you would bother creating your own GL, AP, AR
functionality if the task is as complex as you seem to suggest. Why
wouldn't you buy rather than build?

We have to take your word for it, but I have found that a bit larger
than most Fortune 500 systems.  But who cares, if the data model is
right.  And if they are part of the same data model, they should be in
one DB -- would put male employees in one schema and female employess
in a second schema?

With various products, you can partition the tables, do some indexing
tricks, etc. but that is implementation and not design.

multiple databases. <<

Unh?  Is it one data model or many different universes of discourse?

"functional modules within the financial system"  -- Like a 1950's
COBOL system!  Nothing like an RDBMS at all!  No concept of a universe
of discourse at all.

NO, THEY CANNOT!!  A table a set of entities or relationships of the
same kind.  Were the kid on dope in the back of my database class?
Look up the OTLT or MUCK design flaw.

Ease of developement is not the important thing.  Data quality,
maintainabilty and those other things they told you about in the
Software Engineering course you should have taken are ORDERS OF
MAGNITUDE more important (literally -- did you ever read Boehm's
research, etc.?).

Spreadsheet totals
Foreign Key may cause multilple cycles
Bulk Insert from Excel
Sort Numbers in Nvarchar As Numbers
How do I filter out unwanted rows by only one different column?