Best Practices Question: Create many common databases or many tables in one data

General Best Practices Question: Create many common data databases or many tables in one database? I am not a DBA!

In developing a web portal, ERP, or any large enterprise wide application made up of smaller task specific applications under the parent environment.

Should the design be to create a common data database and a specific database for each app or just put create one database and use best practices naming conventions? RI may or may not be a concern.

Areas of concern are: Maintenence, Security/Access, Performance, backup/restore, and file system and DB fragmentation benefits/hits.It depends on a whole host of questions. Will you need to do reporting across these applications? How will security need to be handled? What is the estimated size and growth of these seperate applicaitons?|||Originally posted by rhigdon
It depends on a whole host of questions. Will you need to do reporting across these applications? How will security need to be handled? What is the estimated size and growth of these seperate applicaitons?

I know my question is a broad one and it does bring up a whole host of questions. I can see many pros and cons for both approaches.

Reporting would be needed across all dbs or tables.
Security would be ADSI/NT Auth Mode.
Much of the apps data would grow very large (heavy I/O) and independently, while others would grow at a slower rate.|||What do you mean by "many common databases"? Do you mean duplicate schemas? I'd advise against that, unless you want rolling out upgrades, synchronizing lookup tables, and consolidating data for reporting to be a full-time job.

"Put all your eggs in one basket - and then WATCH THAT BASKET!"
-Mark Twain

blindman|||Originally posted by blindman
What do you mean by "many common databases"? Do you mean duplicate schemas? I'd advise against that, unless you want rolling out upgrades, synchronizing lookup tables, and consolidating data for reporting to be a full-time job.

"Put all your eggs in one basket - and then WATCH THAT BASKET!"
-Mark Twain

blindman

Databases that contain the data that is common to a specific application only and each then of those databases containing links to a database that contains data that is shared or common to all other apps/databases.|||If they are truly separate applications, then they should be separate databases.

Roll their data into a central data warehouse for analytical processing.

Maintain common data in central database and publish it to the clients to keep them synchronized. Each application should be stand-alone, and not rely on links to other databases if it can be helped.

This is going to be an ambitious project.

blindman