February 18, 2011 at 2:21 pm
Prior to my current job, I worked solely on SQL Server 2000 so schemas weren't something I ever really cared about. Several months ago, though, I came up the a great idea...I wanted to start using schemas in the project I'm currently on.
I'll start with a bit of information on the project. I am working on a large-scale system for a state government entity. This system is broken into 5 distinct modules with a subset of data central to all 5 modules. Under our current design plan, each of these modules will be broken into its own schema. In addition, we plan to have a separate schema for the shared data and a schema for document management.
Here's my question...in addition to everything above, we also have an admin module for our application. The admin module incorporates everything from application security to data driven features such as dashboards to end-user maintainable fields and much more. My first though was to create an admin schema, but have been thinking more recently that each function could be broken into its own schema. Is there such thing as too many schemas? Are there any best practices out there for a situation like this?
February 18, 2011 at 3:27 pm
Jeff Kring (2/18/2011)
Prior to my current job, I worked solely on SQL Server 2000 so schemas weren't something I ever really cared about. Several months ago, though, I came up the a great idea...I wanted to start using schemas in the project I'm currently on.I'll start with a bit of information on the project. I am working on a large-scale system for a state government entity. This system is broken into 5 distinct modules with a subset of data central to all 5 modules. Under our current design plan, each of these modules will be broken into its own schema. In addition, we plan to have a separate schema for the shared data and a schema for document management.
Here's my question...in addition to everything above, we also have an admin module for our application. The admin module incorporates everything from application security to data driven features such as dashboards to end-user maintainable fields and much more. My first though was to create an admin schema, but have been thinking more recently that each function could be broken into its own schema. Is there such thing as too many schemas? Are there any best practices out there for a situation like this?
As it happens with most everything in life an overdose of anything may kill you - even water.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 20, 2011 at 5:43 am
I suspect everyone has their own best practice. I only break down schemas into either discrete security areas, where I'm going to treat some tables differently than others for security, or, very discrete functional areas that also coincide with differences in storage, say tables that are higher read or write percentages that I can put into a different storage area, I might create a different schema. Other than that, I've found that they just lead to confusion.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2011 at 10:27 am
IMO schema can be used in at least two ways:
- to outline data collections cfr Adventureworks db
(not coupled to an application !)
- to ease application security.
As long as you don't use dbo schema, there shouldn't be a problem.
dbo still hold a bunch of systems objects and functions, so granting that schema might be opening to much doors.
If you turn your data analysis to 3-NF, IMO the schema question prity much anwers itself to outline data collections.
It's certainly not an easy exercise to write out strict guidelines.
http://msdn.microsoft.com/en-us/library/dd283095%28v=sql.100%29.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 21, 2011 at 6:02 pm
I've used schemas to segregate what function as temp tables. No joins so they are easily truncated. I've also got a handful of very specific client related tables that have their own schema.
I'd call it more an organizational preference rather than a best practice.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply