Different databases or qualify by owner?

I have an application where I have different department using some
tables which are shared between departments (customer, vendor file etc)
but some other tables are for each department only. They also use the
same table name (Orderheader, OrderDetails etc).
I have two options. One is to put the shared data in one database and
every department's separate data tables in their own database. For
example:
Databases:
Shared
Dept1
Dept2
In this case tables would be qualified by names like
Shared.dbo.Customer, Dept1.dbo.OrderHeader, Dept2.dbo.Orderheader etc.
The other option is to use create tables using different owners and use
one database. For example have one database owner "shared", "dept1",
"dept2". In this case the table names would be referenced by: (leaving
the database name out) Shared.Customer (or create it as dbo.Customer and
the shared tables can be referenced without the schema/owner),
Dept1.OrderHeader, Dept2.Orderheader
Is one solution prefered over another? From the application's (or when
accessing data outside of application for that matter) point of view
both are just as bad since in both cases you need to put in an exrtra
qualifier than table name alone. This make SQL code less portable (even
if it is not that hard to solve).
Another solution which however only works for 2005 or later is to use
the first model but use synonyms to reference the shared tables.
One aspect (I'm sure other will add more aspects) is backup/restore. Is it OK if, when you have the
separate databases model, the two databases are from different point in time? I'm thinking about
data integrity, relations etc here. This is what will happen if you need to restore a backup from
either of the databases. It is possible to sync backup in time over several databases, but backup
and restore is more complex involving transaction log backups and marked transactions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Magnus Bergh" <magnusb@.sbbs.se> wrote in message
news:MPG.20e4aef9230e5fdc98969b@.msnews.microsoft.c om...
>I have an application where I have different department using some
> tables which are shared between departments (customer, vendor file etc)
> but some other tables are for each department only. They also use the
> same table name (Orderheader, OrderDetails etc).
> I have two options. One is to put the shared data in one database and
> every department's separate data tables in their own database. For
> example:
> Databases:
> Shared
> Dept1
> Dept2
> In this case tables would be qualified by names like
> Shared.dbo.Customer, Dept1.dbo.OrderHeader, Dept2.dbo.Orderheader etc.
> The other option is to use create tables using different owners and use
> one database. For example have one database owner "shared", "dept1",
> "dept2". In this case the table names would be referenced by: (leaving
> the database name out) Shared.Customer (or create it as dbo.Customer and
> the shared tables can be referenced without the schema/owner),
> Dept1.OrderHeader, Dept2.Orderheader
> Is one solution prefered over another? From the application's (or when
> accessing data outside of application for that matter) point of view
> both are just as bad since in both cases you need to put in an exrtra
> qualifier than table name alone. This make SQL code less portable (even
> if it is not that hard to solve).
> Another solution which however only works for 2005 or later is to use
> the first model but use synonyms to reference the shared tables.
>
>
|||Separate databases might sound like good idea because tables are separate and
easier to view and such. But I agree with Tibor, backup and restore becomes
an issue.
Another issue to consider is future upgrades, I have a system where we have
two databases that interlink some stored procedures to each other. So if I
upgrade one I have to upgrade other, even though I am just looking at the
info in the one (shared database). But I can't upgrade both at same time
because amount of work involved can't get clients to approve the cost.
So I would recommend you create them in one table under different schema
this way you are able to have FK constraints and such to shared table from
department 1 or department 2, and keep all the data in sync.
Just my two cents ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
|||Mohit K. Gupta wrote:
> Separate databases might sound like good idea because tables are separate and
> easier to view and such. But I agree with Tibor, backup and restore becomes
> an issue.
> Another issue to consider is future upgrades, I have a system where we have
> two databases that interlink some stored procedures to each other. So if I
> upgrade one I have to upgrade other, even though I am just looking at the
> info in the one (shared database). But I can't upgrade both at same time
> because amount of work involved can't get clients to approve the cost.
> So I would recommend you create them in one table under different schema
> this way you are able to have FK constraints and such to shared table from
> department 1 or department 2, and keep all the data in sync.
> Just my two cents ;-).
>
You did not state what version of SQL you are using. If you are on SQL
2005 I would recommend looking at schemas. Using schemas you can
separate tables and privileges based upon those schemas.
|||Heh sorry Jeffrey, Yes, I was referring to SQL 2005. I have most experience
in 2005, although recently I been doing lots of trouble shooting for older
version of SQL systems, so hopefully I will be able to understand better when
saying anything here ;-). Thanks for the pointer.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jeffrey Williams" wrote:

> Mohit K. Gupta wrote:
> You did not state what version of SQL you are using. If you are on SQL
> 2005 I would recommend looking at schemas. Using schemas you can
> separate tables and privileges based upon those schemas.
>
|||In article <E0397338-B468-484B-A891-BD000C1E7310@.microsoft.com>,
tibor_please.no.email_karaszi@.hotmail.nomail.com says...
> One aspect (I'm sure other will add more aspects) is backup/restore. Is it OK if, when you have the
> separate databases model, the two databases are from different point in time?
Thank you.
That is exactly the kind of feedback I was looking for.
In this case (but it might change) it should not be a big problem if the
respective "department" databases are not from the same time since the
shared tables are mostly lookup tables. Actually being able to restore
one of the databases but not another might even be prefered.
However this raise another question. Let say you are inserting rows in
two different databases in same transaction. Will there be any problems
with rolling back the transaction in the case of any errors? I don't
think it should if I understand things correctly. But I guess there
might be a problem with restoring a backup if you have bad luck and you
do a tranaction log backup at the same time as the transaction is saving
(for example backup is made to one database and the second is done after
the transaction has finished).
|||In article <ui$IpMItHHA.1184@.TK2MSFTNGP04.phx.gbl>, jeff.williams3188
@.verizon.net says...

> You did not state what version of SQL you are using. If you are on SQL
> 2005 I would recommend looking at schemas. Using schemas you can
> separate tables and privileges based upon those schemas.
At the moment my client is using 2000. I have not looked that close how
schemas are implemented but are schemas (conceptually) that different
than creating tables under different owners in 2000 and earlier?
|||Yes,.
In 2000, a user owned the tables or objects. Where as with Schema's the one
user can be owner of more then one schema, yet each schema have different
objects and you can assign different permissions to people for each schema.
That is Schema and user are not bound and more like in 2000. In 2000 schema
exist (kinda) because you did have dbo.objectname, but dbo was user and all
objects belong to the user. In 2005, they belong to schema, and schema in
turn belongs to a user.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Magnus Bergh" wrote:

> In article <ui$IpMItHHA.1184@.TK2MSFTNGP04.phx.gbl>, jeff.williams3188
> @.verizon.net says...
>
> At the moment my client is using 2000. I have not looked that close how
> schemas are implemented but are schemas (conceptually) that different
> than creating tables under different owners in 2000 and earlier?
>