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 Practices Analyzer Tool for MSSQL 2000

Hi!
The tool is ver useful, but I need the document in which are explained the
Best Practices for developing in SQL Server and how you coul implementate
them in data bases...
Thanks,
Sandra
http://www.extremeexperts.com/SQL/Ar...Practices.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Sandra" <special_sand@.hotmail.com-do not spam-> wrote in message
news:1069B09E-B4BE-4E80-B083-D0E0199E28AA@.microsoft.com...
> Hi!
> The tool is ver useful, but I need the document in which are explained the
> Best Practices for developing in SQL Server and how you coul implementate
> them in data bases...
> --
> Thanks,
> Sandra

Best Practice question

Our company has several large 3rd-party applications, each with its' own
dedicated SQL Server database. I am developing our own add-ons and utilities
that use data from these databases.
My question is, should my Views, Functions & Stored Procedures reside in the
3rd-party database (they have a standard naming convention to identify our
code), or should they be in a separate database referencing the tables, views
etc. in the 3rd-party database?
Is there any loss in efficiency by keeping our code in a separate datebase?
We are using SQL 2000 Standard.
Thank you.I don't see a problem with keeping the objects in the third-party database
as long as you have a naming convention to identify the objects and are
prepared to re-apply scripts to re-create the objects, if needed. If you
keep objects in a separate database, you'll need to create users in both
databases and can't use ownership chaining unless you enable the
'db-chaining' database option in both databases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in message
news:745D6B85-63D3-4194-9213-8721D7B3713D@.microsoft.com...
> Our company has several large 3rd-party applications, each with its' own
> dedicated SQL Server database. I am developing our own add-ons and
> utilities
> that use data from these databases.
> My question is, should my Views, Functions & Stored Procedures reside in
> the
> 3rd-party database (they have a standard naming convention to identify our
> code), or should they be in a separate database referencing the tables,
> views
> etc. in the 3rd-party database?
> Is there any loss in efficiency by keeping our code in a separate
> datebase?
> We are using SQL 2000 Standard.
> Thank you.|||Dan,
Thank you for the quick response. One thing I did not address was storing
data in custom tables in the 3rd party db which I am also doing. Because the
data in the custom tables changes more frequently than the 3rd party, I use
scheduled DTS packages to copy data from the custom tables to a separate db.
Any thoughts on that?
Thank you again.
--
"Dan Guzman" wrote:
> I don't see a problem with keeping the objects in the third-party database
> as long as you have a naming convention to identify the objects and are
> prepared to re-apply scripts to re-create the objects, if needed. If you
> keep objects in a separate database, you'll need to create users in both
> databases and can't use ownership chaining unless you enable the
> 'db-chaining' database option in both databases.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in message
> news:745D6B85-63D3-4194-9213-8721D7B3713D@.microsoft.com...
> > Our company has several large 3rd-party applications, each with its' own
> > dedicated SQL Server database. I am developing our own add-ons and
> > utilities
> > that use data from these databases.
> >
> > My question is, should my Views, Functions & Stored Procedures reside in
> > the
> > 3rd-party database (they have a standard naming convention to identify our
> > code), or should they be in a separate database referencing the tables,
> > views
> > etc. in the 3rd-party database?
> >
> > Is there any loss in efficiency by keeping our code in a separate
> > datebase?
> >
> > We are using SQL 2000 Standard.
> >
> > Thank you.
>|||> Any thoughts on that?
The main reason would might want to store the tables in a different database
is if you have a lot of data and the recovery/availability requirements are
different. Separation will give you more flexibility.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in message
news:A89EB44E-5E8C-4239-9E01-A92644E4EE3A@.microsoft.com...
> Dan,
> Thank you for the quick response. One thing I did not address was storing
> data in custom tables in the 3rd party db which I am also doing. Because
> the
> data in the custom tables changes more frequently than the 3rd party, I
> use
> scheduled DTS packages to copy data from the custom tables to a separate
> db.
> Any thoughts on that?
> Thank you again.
> --
> "Dan Guzman" wrote:
>> I don't see a problem with keeping the objects in the third-party
>> database
>> as long as you have a naming convention to identify the objects and are
>> prepared to re-apply scripts to re-create the objects, if needed. If you
>> keep objects in a separate database, you'll need to create users in both
>> databases and can't use ownership chaining unless you enable the
>> 'db-chaining' database option in both databases.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in
>> message
>> news:745D6B85-63D3-4194-9213-8721D7B3713D@.microsoft.com...
>> > Our company has several large 3rd-party applications, each with its'
>> > own
>> > dedicated SQL Server database. I am developing our own add-ons and
>> > utilities
>> > that use data from these databases.
>> >
>> > My question is, should my Views, Functions & Stored Procedures reside
>> > in
>> > the
>> > 3rd-party database (they have a standard naming convention to identify
>> > our
>> > code), or should they be in a separate database referencing the tables,
>> > views
>> > etc. in the 3rd-party database?
>> >
>> > Is there any loss in efficiency by keeping our code in a separate
>> > datebase?
>> >
>> > We are using SQL 2000 Standard.
>> >
>> > Thank you.|||Thank you.
--
"Dan Guzman" wrote:
> > Any thoughts on that?
> The main reason would might want to store the tables in a different database
> is if you have a lot of data and the recovery/availability requirements are
> different. Separation will give you more flexibility.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in message
> news:A89EB44E-5E8C-4239-9E01-A92644E4EE3A@.microsoft.com...
> > Dan,
> >
> > Thank you for the quick response. One thing I did not address was storing
> > data in custom tables in the 3rd party db which I am also doing. Because
> > the
> > data in the custom tables changes more frequently than the 3rd party, I
> > use
> > scheduled DTS packages to copy data from the custom tables to a separate
> > db.
> > Any thoughts on that?
> >
> > Thank you again.
> > --
> >
> > "Dan Guzman" wrote:
> >
> >> I don't see a problem with keeping the objects in the third-party
> >> database
> >> as long as you have a naming convention to identify the objects and are
> >> prepared to re-apply scripts to re-create the objects, if needed. If you
> >> keep objects in a separate database, you'll need to create users in both
> >> databases and can't use ownership chaining unless you enable the
> >> 'db-chaining' database option in both databases.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in
> >> message
> >> news:745D6B85-63D3-4194-9213-8721D7B3713D@.microsoft.com...
> >> > Our company has several large 3rd-party applications, each with its'
> >> > own
> >> > dedicated SQL Server database. I am developing our own add-ons and
> >> > utilities
> >> > that use data from these databases.
> >> >
> >> > My question is, should my Views, Functions & Stored Procedures reside
> >> > in
> >> > the
> >> > 3rd-party database (they have a standard naming convention to identify
> >> > our
> >> > code), or should they be in a separate database referencing the tables,
> >> > views
> >> > etc. in the 3rd-party database?
> >> >
> >> > Is there any loss in efficiency by keeping our code in a separate
> >> > datebase?
> >> >
> >> > We are using SQL 2000 Standard.
> >> >
> >> > Thank you.
> >>
>