Different databases or schemas?

I have a customer running several of our application that we are going
to convert to MS SQL. Each application has unique data and tables but
there are also some shared tables. For example Application 1 has a
uniquer Orderheader and Orderdetails tables but Csutomer table is shared
with application 2.
What is the best approach, to setup different databases or to use a
single database but with different schemas? I would like to know the
advantages of each?
Using a single database with several schemas seems like the best choice.
Then all our application is contained in a single database.
It would look liek something like this:
database.shared.Customer
database.app1.Orderheader
database.app2 Orderheader
If using different databases I would make one database with shared data
and one for each application for each applications' unique tables.
Magnus
What did you mean by "single database but with different schemas"?
Obviously ,it will be best to use a single database rather two or
three.....
1) Easy to maintain a single database.
2) Storage issue (Hardware,Disk space and....)
"Magnus Bergh" <magnus.bergh@.kaab.se> wrote in message
news:MPG.1c190da5fb7eb4ee989683@.news.microsoft.com ...
> I have a customer running several of our application that we are going
> to convert to MS SQL. Each application has unique data and tables but
> there are also some shared tables. For example Application 1 has a
> uniquer Orderheader and Orderdetails tables but Csutomer table is shared
> with application 2.
> What is the best approach, to setup different databases or to use a
> single database but with different schemas? I would like to know the
> advantages of each?
> Using a single database with several schemas seems like the best choice.
> Then all our application is contained in a single database.
> It would look liek something like this:
> database.shared.Customer
> database.app1.Orderheader
> database.app2 Orderheader
> If using different databases I would make one database with shared data
> and one for each application for each applications' unique tables.
|||SQL 2000 and prior do not use schemas in the proper ANSI way. Schemas are
attached to users. Yukon handles this better..If you have access to the
code, you'd have to change it to use the 2 part name.
If you put the tables in a single database, remember that a database is a
unit of backup and recovery... Will there be times when one of the apps
needs to be restored and the other does not?... That might be a problem
But the converse is also true. IF they share a single customer table , and
you use different databases, the customer table can get out of synce...ALso
you can not put FK constraints across databases.. so this is a tough issue
worthy of consideration..
My opinion on this varies, but you should know that there are good reasons
to do this EITHER way, and reasonable people will differ.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Magnus Bergh" <magnus.bergh@.kaab.se> wrote in message
news:MPG.1c190da5fb7eb4ee989683@.news.microsoft.com ...
> I have a customer running several of our application that we are going
> to convert to MS SQL. Each application has unique data and tables but
> there are also some shared tables. For example Application 1 has a
> uniquer Orderheader and Orderdetails tables but Csutomer table is shared
> with application 2.
> What is the best approach, to setup different databases or to use a
> single database but with different schemas? I would like to know the
> advantages of each?
> Using a single database with several schemas seems like the best choice.
> Then all our application is contained in a single database.
> It would look liek something like this:
> database.shared.Customer
> database.app1.Orderheader
> database.app2 Orderheader
> If using different databases I would make one database with shared data
> and one for each application for each applications' unique tables.
|||In article <eaYnLGH2EHA.3324@.tk2msftngp13.phx.gbl>,
wayne.nospam.snyder@.mariner-usa.com says...

> SQL 2000 and prior do not use schemas in the proper ANSI way. Schemas are
> attached to users. Yukon handles this better..If you have access to the
> code, you'd have to change it to use the 2 part name.
Oracle also have schemas attached to users (at least they had in
previous versiosn have not followed Oracle since ver 8). How do you mean
it is handled in Yukon?

> If you put the tables in a single database, remember that a database is a
> unit of backup and recovery... Will there be times when one of the apps
> needs to be restored and the other does not?... That might be a problem
Yes, been thinking of that. Usually they should be backup/restored at
same time. Only exception would be if the users made some big error and
want to rollback the changes. But that cause so many other troubles so
that should be avoided.
Thank you for your help
|||There is nothing wrong with using shared data. Those should be put in an
Enterprise Table, akin to a Warehouse or Data Mart.
Then, each application should have its own database because they each need
their own data.
Finally, either each application references the Enterprise Table data
through direct query or should each get a local copy of that data. I prefer
the local copy due to the ability to enforce FK RI on local tables only, not
across databases without triggers.
Synchronization is only a issue if you have multiple downstream
dependencies: C copies from B, which copies from A. All users should query
or refresh local copies from the primary source of the data as in a spoke
diagram as opposed to a linear sequence.
Sincerely,
Anthony Thomas

"Magnus Bergh" <magnus.bergh@.kaab.se> wrote in message
news:MPG.1c190da5fb7eb4ee989683@.news.microsoft.com ...
I have a customer running several of our application that we are going
to convert to MS SQL. Each application has unique data and tables but
there are also some shared tables. For example Application 1 has a
uniquer Orderheader and Orderdetails tables but Csutomer table is shared
with application 2.
What is the best approach, to setup different databases or to use a
single database but with different schemas? I would like to know the
advantages of each?
Using a single database with several schemas seems like the best choice.
Then all our application is contained in a single database.
It would look liek something like this:
database.shared.Customer
database.app1.Orderheader
database.app2 Orderheader
If using different databases I would make one database with shared data
and one for each application for each applications' unique tables.