Re: Different databases or schemas?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 12/02/04 

Date: Thu, 2 Dec 2004 08:18:42 -0500

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.

Relevant Pages

  • Re: Mumps: the IT worlds best kept secret?
    ... No matter if that is enforced by the database software or by convention in ... Generally they do not use SQL as its not an appropriate or ... The reasons for Mumps databases to exist, are exactly what you pointed out ... Intuitively you would expect RDBMSs with strong and rich schemas would ...
    (comp.lang.mumps)
  • Re: Logins, Users, Roles, Schemas
    ... How do you delete these from a database? ... the logins from the database? ... Passwords Between SQL Servers ... Thus, I believe making Users, Roles, Schemas ...
    (microsoft.public.sqlserver.security)
  • Re: Mumps: the IT worlds best kept secret?
    ... thinking that's going on with respect to SQL versus REST and JSON as ... hierarchical database?" ... The reasons for Mumps databases to exist, are exactly what you pointed out ... Intuitively you would expect RDBMSs with strong and rich schemas would ...
    (comp.lang.mumps)
  • Removing a user in my database
    ... I have a SQL Server 2005 running on my PC, and a database installed on it. ... I have a user nammed Maindatabase, and I need to delete this user. ... When I look the properties of the user, I find that in the "Owned Schemas" properties, there is a checkbox selected for this user and for the Maindatabase database. ...
    (microsoft.public.sqlserver)
  • Re: Multi-Database Transactions
    ... Distributed transactions are always a second choice when single database ... using Sql 2k5 ... > I've been looking into adding transaction capabilities into my object ...
    (microsoft.public.dotnet.framework.adonet)