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

Best license program

I have a small company and i make online ERP software in ASP.NET for small
companies. Companies with one server en a maximum of 10 concurrent users.
What SQL server license should i advice for these companies? And how much
does it costs?
I've tried to understand the documents on the microsoft site but i did not
find an satisfying answer.
Thanks in advance.
RonaldIf your database won't be bigger than 2 GB, you can use MSDE which is free.
Otherwise the best is to use Standard Edition with per seat licensing if
your client don't have more than 25 users (not concurrent users, you need a
license for everyone who uses the server), or Standard Edition with a
processor license if there are more than 25 users.
--
Jacco Schalkwijk
SQL Server MVP
"Sandeman" <ilighters@.zeelandnet.nl> wrote in message
news:OAiA2bYlEHA.3452@.TK2MSFTNGP15.phx.gbl...
>I have a small company and i make online ERP software in ASP.NET for small
> companies. Companies with one server en a maximum of 10 concurrent users.
> What SQL server license should i advice for these companies? And how much
> does it costs?
> I've tried to understand the documents on the microsoft site but i did not
> find an satisfying answer.
> Thanks in advance.
> Ronald
>

Best license program

I have a small company and i make online ERP software in ASP.NET for small
companies. Companies with one server en a maximum of 10 concurrent users.
What SQL server license should i advice for these companies? And how much
does it costs?
I've tried to understand the documents on the microsoft site but i did not
find an satisfying answer.
Thanks in advance.
Ronald
If your database won't be bigger than 2 GB, you can use MSDE which is free.
Otherwise the best is to use Standard Edition with per seat licensing if
your client don't have more than 25 users (not concurrent users, you need a
license for everyone who uses the server), or Standard Edition with a
processor license if there are more than 25 users.
Jacco Schalkwijk
SQL Server MVP
"Sandeman" <ilighters@.zeelandnet.nl> wrote in message
news:OAiA2bYlEHA.3452@.TK2MSFTNGP15.phx.gbl...
>I have a small company and i make online ERP software in ASP.NET for small
> companies. Companies with one server en a maximum of 10 concurrent users.
> What SQL server license should i advice for these companies? And how much
> does it costs?
> I've tried to understand the documents on the microsoft site but i did not
> find an satisfying answer.
> Thanks in advance.
> Ronald
>