MS SQL Server - question about using various DBs

Author

question about using various DBs

sklett

2006-02-03, 8:23 pm

Hi,

I'm designing our suite of new applications (sigh) and we're going to be
storing all the data in SqlServer 2005. I'm hardly a "DBA", I have used
access in the past, but never designed a full system. The issue I'm hung up
on is how to determine what tables should be in their own separate database.
For example, we will need the following tables:
- users
- inventory
- product bill of materials
- production runs
- test results
- etc

One of the apps will handle "production run" management. This includes
pulling the parts for the run, creating bar code labels, test results and
finally "checkin" of the finished items. This application will need access
to all the tables listed above. Another application will only need access
to "test results", "users" and "production runs" and finally, another
application that will only need access to "inventory"

So, do I place all of these tables into the same database, even though they
aren't all logically connected? Some of the applications will use all of
them, but they aren't necessarily all part of the same "logical group"
Another example would be if we had an "Employee Management" application, it
would need access to the "users" table as well, but has NO business with the
other stuff.

Would it be smarter to create a "Users" database, "Inventory" database, etc
then just reference the database.table in my queries?

What concerns should I have? This won't ever be a large application, so I
don't need to worry about different servers, etc.

I hope you see what I'm trying to figure out, hopefully someone has some
suggestions.

Thank You,
Steve Klett

David Browne

2006-02-04, 3:23 am

"sklett" <asd2@fkd.com> wrote in message
news:eSeL3mRKGHA.3272@tk2msftngp13.phx.gbl...
> Hi,
>
> I'm designing our suite of new applications (sigh) and we're going to be
> storing all the data in SqlServer 2005. I'm hardly a "DBA", I have used
> access in the past, but never designed a full system. The issue I'm hung
> up on is how to determine what tables should be in their own separate
> database. For example, we will need the following tables:
> - users
> - inventory
> - product bill of materials
> - production runs
> - test results
> - etc
>
> One of the apps will handle "production run" management. This includes
> pulling the parts for the run, creating bar code labels, test results and
> finally "checkin" of the finished items. This application will need
> access to all the tables listed above. Another application will only need
> access to "test results", "users" and "production runs" and finally,
> another application that will only need access to "inventory"
>
> So, do I place all of these tables into the same database, even though
> they aren't all logically connected? Some of the applications will use
> all of them, but they aren't necessarily all part of the same "logical
> group" Another example would be if we had an "Employee Management"
> application, it would need access to the "users" table as well, but has NO
> business with the other stuff.
>
> Would it be smarter to create a "Users" database, "Inventory" database,
> etc then just reference the database.table in my queries?
>
> What concerns should I have? This won't ever be a large application, so I
> don't need to worry about different servers, etc.
>

This is easy. Use one database.

A database is a physical container. It can contain multiple schemas, which
are just logical containers, so you might want to seperate the objects by
schema. But if you never plan to put the objects on seperate servers, don't
use seperate databases.

David

sklett

2006-02-04, 3:23 am

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:OGagdZTKGHA.3836@TK2MSFTNGP10.phx.gbl...
>
> "sklett" <asd2@fkd.com> wrote in message
> news:eSeL3mRKGHA.3272@tk2msftngp13.phx.gbl...
>
> This is easy. Use one database.
>
> A database is a physical container. It can contain multiple schemas,
> which are just logical containers, so you might want to seperate the
> objects by schema. But if you never plan to put the objects on seperate
> servers, don't use seperate databases.
>
> David
>

HI David,

I confused my terminology, I'm pretty sure my I meant to ask if I should use
separate schemas. If I have multiple schemas, I would still use the same
connection string to access the different schemas, right?

Are there any performance issues with a stored procedure accessing multiple
schemas?

Thank you for the response!
-Steve

sklett

2006-02-04, 3:23 am

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:OGagdZTKGHA.3836@TK2MSFTNGP10.phx.gbl...
>
> "sklett" <asd2@fkd.com> wrote in message
> news:eSeL3mRKGHA.3272@tk2msftngp13.phx.gbl...
>
> This is easy. Use one database.
>
> A database is a physical container. It can contain multiple schemas,
> which are just logical containers, so you might want to seperate the
> objects by schema. But if you never plan to put the objects on seperate
> servers, don't use seperate databases.
>
> David
>

1 more question, just trying to make sure we are talking about the same
thing.
Given this example:
SELECT * FROM [PMD_MANF].[dbo].[ttt]

would 'PMD_MANF' be the schema? I'm confused, Management Studio is leading
me to believe this is a "database", not a schema.

Tibor Karaszi

2006-02-04, 7:23 am

> SELECT * FROM [PMD_MANF].[dbo].& #91;ttt]
>
> would 'PMD_MANF' be the schema? I'm confused, Management Studio is leading me to believe this is
> a "database", not a schema.

No, that is the database name. In your example, "dbo" is the schema. The object owner is no longer
part of an object name in SQL Server 2005. Where we used to have object owner, we now have schema.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"sklett" <asd2@fkd.com> wrote in message news:enX7SYVKGHA.648@TK2MSFTNGP14.phx.gbl...
>
> "David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in message
> news:OGagdZTKGHA.3836@TK2MSFTNGP10.phx.gbl...
>
> 1 more question, just trying to make sure we are talking about the same thing.
> Given this example:
> SELECT * FROM [PMD_MANF].[dbo].[ttt]
>
> would 'PMD_MANF' be the schema? I'm confused, Management Studio is leading me to believe this is
> a "database", not a schema.
>

Sponsored Links