?
Solved

Usage of "Schema" names

Posted on 2011-09-29
4
Medium Priority
?
253 Views
Last Modified: 2012-05-12
Hi,
I am somewhat a newbie to SQL Server administration as I am now doing everything for a very small company.

I have a database the has the need for 2 "Organization" tables.  One is a "Customer Organization" table (What organization the customer belongs to).  The other is a "Global Organization" table.  Let's accept that they have to be different tables.

Within a database, what are the ramifications between the 2 sets of names:
Database: myCompany
table:  dbo.Cust_organization
table:  dbo_Global_Organization
OR
Database: myCompany
table:  Cust.Organization
table:  Global.Organization

And what is the ramification of making a separate database (when would I do that)?
Database : myCompany
table: dbo.Organization

Database : Global
table: dbo.Organization

Any advise would be appreciated,
hefterr
0
Comment
Question by:hefterr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36817075
The difference is largely personal choice.  It depends on how your security is setup.  To give you another option, you could even have one table with a column that differentiates between customer and global, if the columns are the same.  

Using different schemas allows a logical grouping of objects and you can assign permission to the schema to only allow users to use that schema.  

With separate tables, you can still assign permissions to only the appropriate table, but you would have to assign permissions individually to each object instead of one like the schema method.

With separate databases, you can complete separate the data.  This might mean that you have duplicate tables in each to accommodate the design.  Also, need separate backups, etc...

Greg

0
 
LVL 1

Author Comment

by:hefterr
ID: 36817398
Hi JestersGrind,
Thanks for the quick response.

On the topic of security, as a web application all public users have the same security.  So you are talking about developers, DBA I assume?

...Using different schemas allows a logical grouping of objects and you can assign permission to the schema to only allow users to use that schema.  
Q:  Is this a common procedure for just "grouping" as this is what I would be using it for.

Q:  If a table was shared by different sites/applications, would it be more of a candidate for a separate DB?  Or is that not significant?

Thanks again,
hefterr

0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 2000 total points
ID: 36817934
I was referring to user security.  In most environments that I have worked in, DBAs have full access to production and developers have none, but every environment is different.  

Q:  Is this a common procedure for just "grouping" as this is what I would be using it for.
The behavior changed in SQL 2005.  Before that schemas were tied to a specific user.   Now they are independent and are really used more for organization than anything else.  Although, they can also simplify security too.  This is a pretty good article about it.

http://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/

Q:  If a table was shared by different sites/applications, would it be more of a candidate for a separate DB?  Or is that not significant?
It really depends.  Typically, I like to use one database per application, but I have seen implementations where customers were separated out to separate databases.  I've never thought that was a good practice.  As long as your customers can only see their data and not any other customer's data, it doesn't really matter how you store it.

Greg




0
 
LVL 1

Author Closing Comment

by:hefterr
ID: 36818192
Thanks!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question