Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Having asked This question on Stackoverflow, I wondered where what I have done is correct/best practise.

Basically, every object that I create is going into a schema with the schema name reflecting a usage. For example, I have the schemas Audit and Admin (amongst others).

This in turn leaves no objects in dbo. Is this ok? Is there anything else that I need to do?

share|improve this question
    
possible duplicate of Schema design - best practices?. And related –  gbn Nov 29 '11 at 5:32
add comment

5 Answers

up vote 10 down vote accepted

Schemas are not only a great security tool (which is reason enough to use them), but they are also perfect for logical separation. And it seems as though this is what you are practicing.

Even if the current requirement doesn't need special security, say down the road all of the database objects that are related to Auditing should be secure to a database role. If these objects were scattered throughout the dbo schema, then you would have to explicitly deny permissions on the individual objects. But with the Audit schema, you do a single deny and you're set.

I personally practice the use of schemas. Like all things in databases, though, there is a happy medium. I wouldn't create a schema for each granular aspect of the data layer. There is such thing as too many schemas and separation. But I'm guessing you aren't anywhere close to that.

share|improve this answer
add comment

A typical pattern is schemas based on permissions, so you'd have WebGUI, Desktop etc for code so all objects have the same permissons from the schema.

If you have clear user groups then you can permission on that, but you'll end up with overlapping and messy permissions at some point. I tend to defer the user/group checks to some check inside code and not permissions objects: say you have Admin and HR Excel users: these all run Desktop code.

Data is usually shared so I'd have a Data schema, maybe a History or Archive schema.

Some code isn't public (like a UDF or internal proc) so I'd use a Helper schema for code that shouldn't be run by client code.

Finally, schemas like Staging or System or Maintenance are useful sometimes.

Although there are no user objects in the dbo schema, the user dbo owns all the schemas.

share|improve this answer
add comment

No objects in dbo schema is perfectly fine. From what I can see that's not overuse of schemas, either - although how many schemas is 'too many' is a fairly subjective question (it's comparable to "How many classes should my OO design have?").

The only other thing I'd mention would be to grant permissions on the schema rather than individual objects (your SO question doesn't specify anything about permissions).

share|improve this answer
    
I usually sort out permissions at the end of development as we have somewhat "fluid" specifications at times. Is this how you do it? Or do you assign permissions as you create the objects etc –  Stuart Blackler Nov 29 '11 at 0:23
    
Create object, assign it to a schema, assign permissions to the schema. If you need wide-open access for development, just grant full permissions on the schema(s) and narrow them down later. –  Simon Righarts Nov 29 '11 at 0:29
add comment

I would like to use schemas to separate database according to modules and usage patterns. I find that it makes it very easy to understand database tables therefore maintenance becomes easier. I had following schema types in my last sql server project. LT -- Lookup Tables

COMMON
LT_COMMON
MODULENAME1
LT_MODULENAME1
..

For a big modules, we also separated them to more schemas. For example personel module consists of more then 5 modules.

PERSONEL_COMMON
PERSONEL_FINANCE
PERSONEL_MODULE2
..
LT_PERSONEL_COMMON
LT_PERSONEL_FINANCE
LT_PERSONEL_MODULE2

We also included schemas like for other activities TEMP,MAINTANENCE. In the management studio, you can filter using schema name. A developer responsible for MODULE1 , filter according to name and works almost all of the time with only these tables. This makes it very easy for developers, dbas, new comers alike to understand database tables.

share|improve this answer
add comment

Best practices may be different for sql server than for oracle however my experience is that the less schemas, the better.
I like to have a schema for the dba/programmer that has special privileges and some code to do maintenance. All the business data should go in one schema so you know where it is. Naming conventions are enough to differentiate between the use of the table or stored code.
I can see a case where you have multiple business units owing different data with little overlap each having their own schema. Otherwise keep it simple.

share|improve this answer
2  
Schemas are different in SQL Server: there is full database-schema-user separation. I found Oracle schemas to be limited –  gbn Nov 29 '11 at 5:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.