June 22, 2010 at 8:22 am
I’ll pile on to the “favor schemas” with:
• Organization - I have multiple departments (marketing, accounting, hr) / applications (crm, rpt.) that I support. Being able to group them in a schema helps as opposed to using that in the object name (i.e. mkt.Promotion vs dbo.mkt_Promotion)
• Function - I have some objects that support specific functions (www, soa, rpt, etc.) and, as you may imagine, the www schema is fairly well locked down with limited permission.
• Security - I agree with using roles for security but that doesn’t mean I can’t apply those permissions at the schema level. Also, any SQL facing a web site can make sql injection attempts more difficult as they must also provide the schema in the hack.
--Paul Hunter
June 22, 2010 at 8:42 am
Thanks for standing up for the "hard way". Everyone wants the easy method that they can plug and look good.
Even Project Management has been "methodized". A great project manager works hard to bring his/her project on time, in scope and under or on cost promised.
Keep up the good work. Pretty reports and charts will not get the job done. Hard work and trying not to make the same mistake twice is the only way America can fight back against the "outsourcing" that promise the world and deliver chaos.
June 22, 2010 at 9:02 am
I haven't read all the posts but I'm in favor of schemas for similar reasons already stated. When possible I still prefer to use separate databases, but since this is not always possible or practical, then using schemas is preferred over prefixing tables.
Using schemas simply depends on your requirements, desired results and comfort level with them. I don't believe in blindly using schemas or using them excessively... but they are another valuable option a DBA and developer can use.
David
June 22, 2010 at 9:19 am
I don't think it should be considered a technical best practice to either use or not use schemas; I think it mostly just an extension of one's overall naming convention, partitioning, or security strategy. For the past several years, I've been developing and maintaining several large databases with complex data models that are shared by multiple applications. Sometimes they also have client specific functionality or sub-systems. The more legacy databases originally developed under SQL Server 2000 logically seperate objects across multiple databases. However, for those developed more recently under 2005/2008, we have procedures and tables contained in schemas specific to an application or client, and I like this approach better. For one thing, we can create foreign key relationships and indexed views across schemas, but can't do this across databases.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 22, 2010 at 9:26 am
I find schemas extremely useful. We have a couple of user groups that need access to manually run some stored procedures. We created these in a separate schema. That way we know exactly what procedures they can run and we know there is no applications calling this code. It also makes security much easier to manage.
June 22, 2010 at 9:44 am
I'll chime in on the against. I just haven't seen the separation of tables/entities into groups as much. Perhaps I haven't worked in as large a set of organizations, but most times we have data, it is related to one entity. Order entry stuff gets spread out and I woundn't say it's all "Sales" related. Sometimes it becomes a part of inventory, CRM stuff, etc. So I just have Ordersxxx tables under the same schema. Reporting, web services, etc., it's all usually mixed up with other data.
I think that AdventureWorks separation is a little silly. IMHO, it's overdone and doesn't necessarily make it clearer to me. I struggle to sometimes understand why a particular schema exists. Perhaps it's just that I would have Person and PersonAddress and not think anything of it.
In terms of security, it seems that there are always exceptions to a group's rights. Granting something on a schema, to me, seems akin to granting to public in a single schema database. I can't build exceptions. So I would still need roles to help me manage rights.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 22, 2010 at 9:59 am
Many of the replies in favor of schemas make sense to me. However:
In my environment, schemas are simply not necessary or helpful. When I want tables to be "grouped", I simply start their name with the same text. For example, there might be a LogSlot, LogAppointment, LogAssign, etc. for all the tables that relate specifically to a function in my agency that is about "logs". (Tables and "log" made up to protect the innocent database.) I do not want tables to have the same name, so having the tables all be in the same "name space" is great error checking/naming quality control. In my practice, for general tables that span multiple uses, such as Employee, the table name has no prefix.
This system works very, very, very well--at least for the needs at my agency. It just requires planning. And since schemas require planning too, there would not be much benefit for me to use schemas over good naming practices.
Like I said, I can understand the arguments in favor of schemas, but I think schemas should be used deliberately with a lot of thought for specific need(s) instead of as some kind of standard best practice. It is very possible that a large percentage of databases out there need no more than one lovely schema, everyone's best friend, the dear old "dbo".
June 22, 2010 at 10:20 am
Some of the against views (both in this discussion and in blogs referenced in the original editorial) indicated that data / entities used originally for one purpose, eventually get hijacked and used for others. Furthermore, where multiple schemas were part of the equation, then this just added to confusion and manageability. In addition, that schemas are only useful on 'large' databases (by which I assume the database contains many objects).
To my mind, use of schemas can help clarify greatly what things are intended to be used for, and to tightly couple blocks of functionality with a clear & simple permission set.
The situation described by those more 'against' multiple schemas strikes me as accepting the consequences of a break-down in development processes. Multiple-schemas are of course not a magic bullet against this, but a useful tool in the armoury.
As for the idea that they should only be used on large databases ... what if some large databases started at as small ones? 'Good' development practice and design ideally should be built in early, and retrofitting this into a database once it is 'large' may be an unhappy experience.
The Principle of Least Privilege is more easily applied by grouping objects into schemas (surely?). Also, can one not argue it is easier to verify permissions granted and withheld in a database through associating permissions with schema, rather than have to work on an object-by-object basis?
I feel that multiple-schemas generally help, rather than hinder ( or rather I think that they have generally helped me, which is of course not the same thing 🙂 )
June 22, 2010 at 10:28 am
I'd argue against Craig saying that often the business doesn't know what the best use of this data is. Or how it will be used with an extremely high degree of certainty at development time. It's not necessarily a failing of the development process (though it can be), but often a business failing.
In terms of permissions, if I grant permissions to a schema, and I need a sub group of permissions in that schema, how is that easy to handle? For example, HR schema. I have people in base roles, and more privileged roles for things like salary.
Principle of least privilege, I have two roles. One has rights to some of the objects, some have rights all objects. To me, this is easy to handle with roles, with rights granted to the objects. Fundamentally simple to me to set security. It seems to me if I have schema security, I can't do this, or that I have some people set with schema level security, some with object level security, which seems fundamentally to be an exception.
But that's me. I am more than willing to accept that perhaps I haven't given schemas a chance.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 22, 2010 at 10:49 am
I have never used schemas for security (I agree that security is easy enough to set up without schemas, and I've never had a need). Instead, I use it for the logical grouping of database objects.
This started with a Kimball BI recommendation to use schemas instead of prefixes for naming conventions of data warehouse table. Instead of dbo.factCustomerOrder, I have fact.CustomerOrder; instead of dbo.dimTime, I have dim.Time. The "dbo" prefix is totally unneccesary, and I think the shorter names is just cleaner. It's purely asthetic, but I like it.
Since that, I've branched off and found many uses for schemas in grouping objects, primarily to avoid using prefixes, using schemas instead. I have several functions and stored procedures attached to my master database. Most are for date/time or string manipulation. A couple are database administration SPs. Previously, they all had prefixes, and in the case of functions (which most of them are), I had to type "dbo" anyways. master.str.ufnSplit is a lot cleaner than master.dbo.str_ufnSplit.
I've also used schemas to group stored procedures that run against our linked server. Again, this was previously done through the use of prefixes, but now I use schemas to differentiate which ERP-based SPs report against the local server, the linked server, or both.
I have a log database, and I've started using schemas to group logs by process. Since each process has a different lifetime for it's logs, I've created an SP that can purge old logs on a per-schema basis. Since all log tables have a "LogDate" field, I can loop through the objects and purge logs older than a @daysold parameter. I've found it quite useful. I suppose the same could be done with table prefixes, but it would be quite a bit less elegant.
Stored procedures, functions, and tables used to test processes or programs are typically placed in a "test" schema. This seperates them logically from application objects which may very well reside in the same database.
Schemas are definitely not for everyone, but I've found them quite useful. I'll admit that it is occasionally not obvious which schema a certain object should belong. Sometimes an object may want to belong to more than one schema, or it's true "home" may change over time. These things are fairly manageable, and no database design will ever truly be perfect. I just try to apply some logic, and keep the objects as organized as possible. A couple of times I've had to move an object, usually creating a synonym unless I know exactly all of the places where the object is referenced.
Although they are not for everyone, I've found that putting everything in the "dbo" schema isn't particularly meaningful (at all). Schemas are available, so I use them.
--J
June 22, 2010 at 11:12 am
I have never found schemas usefull or necessary. They always seemed like a solution in search of a problem to me. I also never like the term "schema" since it seemed like a hijacked term.
June 22, 2010 at 12:46 pm
JJ B (6/22/2010)
Many of the replies in favor of schemas make sense to me. However:In my environment, schemas are simply not necessary or helpful. When I want tables to be "grouped", I simply start their name with the same text. For example, there might be a LogSlot, LogAppointment, LogAssign, etc. for all the tables that relate specifically to a function in my agency that is about "logs".
Well you do use Schema just semantically instead of declaratively. It must take planning so that all developers use the same set of prefixes. But you have given up any possible benefits of using actual schema.
June 22, 2010 at 1:49 pm
Schema are very useful but not required. Here are a few cases where they have come in handy:
1) Util schema for UDFs that are generic and not specific to any domain. (example: LastDayOfMonth, RegExMatch, RegExReplace, BuildName_MFL, BuildName_LFM)
2) 3rd party Application modification. Add a new Shema for your code and objects to avoid any collisions or confusion
4) Clarity & Discipline. If used correctly the objects should be easier to intuitively understand, find, and enable good design. If schema are getting in your way then you may have chosen your schema poorly or modeled your data ineffectively.
5) ORM Scripting. Schema clarity helps with data access object modeling and defining your classes in meaningful and clear to use classes. While this is not a requirement a high correlation rate between object classes and data structures usually simplifies the SDLC.
6) Name spaces for identifying relations of Superclass data models. If you use this type of relationship it may be useful to inherit your identifying keys from a super class of one schema into multiple schema meaningful to the subclass tables. This would permit the use of the same table names for subclass objects. This is one of those things that is best worked out between data architects and application architects. It's not an absolute but a good tool to have in the arsenal that you know how to use.
Recently there was a meme asking people to list the features that should be removed from SQL server. I don't recall seeing any listing this feature. Even if you don't use them would you list schema as a feature that should be removed. If it was removed I could adapt but there would be a lot of useful cases that would have to be implemented in less elegant ways.
Finally, to suck up to Redgate a bit, schema make the intellisense features of SQLPrompt even easier and more useful to use. For those that don't use SQL Promt try filtering the lists of tables, vies, sprocs and udfs by schema in SSMS.
June 22, 2010 at 1:52 pm
Steve Cullen (6/22/2010)
I have never found schemas usefull or necessary. They always seemed like a solution in search of a problem to me. I also never like the term "schema" since it seemed like a hijacked term.
The concept of database schemas has been around for decades and is similar to namespaces in dotnet, where we have the sys schema containing SQL Server's system views and tables and then we have the default schema dbo and possibly user defined schemas. The way that SQL Server handles schemas in 2005/2008 is more ANSI standard than Oracle, which still associates a schema for each login account and an account for each schema.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 22, 2010 at 2:03 pm
Well you do use Schema just semantically instead of declaratively. It must take planning so that all developers use the same set of prefixes.
Either way, all the developers need to be on the same page - whether using prefixes or schemas.
But you have given up any possible benefits of using actual schema.
Well, that's my point. What benefit? For my situation, there does not appear to be a benefit to using multiple "schemas". (I totally agree with previous poster that this is a terrible name.) Security is the most persuasive argument I've seen for using schemas and yet our situation does not lend itself to applying permissions along schema groups. No other arguments I've seen are applicable to my situation.
On the other hand, adding multiple schemas adds a layer of complexity and lack of name checking that would simply be a cost without accompanying benefit.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply