Question :
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?
Answer :
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.
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.
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).
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.
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.