April 24, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/istheschemastillneeded.asp
May 18, 2003 at 5:45 pm
Outlived its usefulness? Heck, I'd be happy if somebody told me what its usefulness ever was. I have never understood the desire to have multiple objects with the same base name in the same database. What is or ever was the benefit?
May 18, 2003 at 6:59 pm
Only being able to speak from personal experience, that is what I will speak from. It is not a feature that I have ever used and the only reason to know about it is so to be sure that an oversight doesn't allow it to cause problems.
May 18, 2003 at 7:14 pm
Up until about two months ago, I thought it was totally silly and had very little use... But, one, I hope "clever" <g>, of them did spring to mind.
I've been writing a doc management system which is to have very tight security, etc, etc. The customers can write their own custom reports which can be based upon anything in the system. In order to ensure that someone cannot run and report and see something they should not normally see, all users of the system have their own SQL login (which my app will create/destroy/modify as required). Each user will have their own set of views (all with the same name, only distinguished by the schema) which map pretty much map directly to each normal table in the database, except for a discrimination on each view's where clause to exclude rows that view's owner cannot see.
Thus, when a report is written, just the ordinary view names are used (no schemas) - then when an ordinary pleb logs in, they cannot see the additional records that the super user who wrote the report can see. No changes required to the report at all!!
I've done a bit of testing on the above and it seems to work quite well.... Anyone else think of a "good" reason for them??? Particularly when MS bring forth row level security in Yukon (apparently?), the above use will diminish too (does anyone else do things this way or have I missed something)
My 2c.. Cheers,
Ian
May 18, 2003 at 8:32 pm
Hi Steve
Good point to bring up actually.
This sort of concept comes from the oracle world, but were see a clearer distinction between "user" and "owner" of objects. Here in oracle an instance = a single db with 1 or more "schemas", where schemas can be similar to a single sql server instance with a bunch of user databases. This is purely thinking aloud though and there are other issues to consider of course..
In SQL Server best practice stats "all objects owned by dbo"... so other "schema" users are irrelevant. I tend to disagree, esp during development where people can play around with their own objects and we cut them over to dbo and wrapper the security model around them to formalise the structure/framework of the user objects in the db.
There are some other cases where I like to break the dbo rule, and have a different schema owner for my audit tables.. eg:
dbo.course
dbaudit.course_audit
why? I can still wrapper up all my security privs etc, but at the same time its easily to single out my audit tables and keep them alphabetically listed. Of course, alphabetic listing is not the only reason... 🙂
The BIG problem with non-dbo schemas tends to be db import/export via DTS and its inability to match up obj names between schema owners.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 18, 2003 at 9:16 pm
if you go back to the fundamental justifications for having databases (right back to the days hierarchical Dbs for instance) the whole thrust was about data management approaches that would break down isolated "islands of information" that bedevilled the corporate landscapes and caused no end of inconsistencies and confusions.
The schema concept being challenged here came to the relational world in Oracle's products - it was a pretty silly idea then and frankly it still is - because it tends to reinforce the "islands" type thinking. But hey, I won't just bash Oracle for it - if there was a stupid idea lying around then for sure M$ would just have to adopt it too now wouldn't they?
May 19, 2003 at 12:36 am
I vote thumbs down to schemas, for me they're just a problem I could do without (ever seen what happens if someone is the owner of a DB but is not a sysadmin?).
They also add a minimum of 4 useless characters (including the dot) to names, and given that SQL is full of stupid windows that can't be resized (e.g. many wizards) this another nuisance aspect....
May 19, 2003 at 2:47 am
I liked the idea of developing and testing with your own "schema" before cutting across to a dbo implementation.
It is not a facility that I commonly use and I don't know many other people who do. The problem is that if you work in a shop where no-one else uses a facility then who do you learn what the correct use of that facility is?
There are two uses for this facility that spring to mind.
1. Holding personalised settings for an app a'la windows profiles.
2. A centrally managed app where the data and/or other objects have to be isolated according to the user.
These days the sort of isolation and security that is carried out at the application level rather than the database level.
In the case of point 2 its a case of "an" answer rather than "the" answer.
May 19, 2003 at 8:15 am
I'd vote a general thumbs-down as well--but they do have their uses.
The primary negative point is Confusion. Schemas are a pretty abstract concept (the closest analogy I can come up with is "parallel dimensions applied to database tables"), and how can you clearly and simply explain schemas to the people who may be required to use them--especially if they're not even clear on why a table is (you'll note I didn't say "what").
Managing them is also confusing. How many schemas can there be in a database? Can you readily and ably manage them and their contents as a coherent set? I don't know anything about this subject, as once I realized what they were I made sure they weren't going to be used.
Lastly, there's the (at least to me) very confusing and harshly underdocumented subject of optimization. In all code (stored procedure or otherwise), "best practice" is you're supposed to qualify all objects with their full ownership chain (i.e., "SELECT * from myDB.dbo.myTable", not "USE myDB" | "SELECT # from myTable"). If so, what's the point of setting context (USE mdDB)? What happens if you have multiple database instances with different names? Or is that just for stored procedures (and which is better, "EXECUTE myDB.dbo.myProc", "EXECUTE dbo.myProc", or "EXECUTE myProc"? I know this underlies optimizing procedure cache hits and utilization, but I've never gotten the long or short of it.)
I do like the idea of setting up "your own set of objects" (your own pocket universe) for development purposes... but again, how easy is it to explain this to the developers? How easy is it to set everything to .dbo when it's time to roll it to a QC environment? How many problems and glitches will arise solely from the fact that everyone was working in their own schema? Me, I just create and destroy a separate copy of the database whenever I need to do something like this... but I'm the DBA and can get away with it. Others are not so lucky.
Now, if it were possible to define or configure a distinct set of schemas (as opposed to one for everyone who can log on with no questions asked), and permit or deny individuals access to them, that might have some benefit. (It's only a thought--I can see this quickly spiraling out of control.)
This should be an interesting thread. Enough two centses add up to dollars...
Philip
May 19, 2003 at 9:49 am
I agree with the article.
Regarding the ownership issue, we provide a separate copy of a database for each developer who needs one to run and test their changes on their own machine. Changes to the production databases are controlled by the DBA group so that the only schema is dbo.
User preferences are done by attaching user identification columns to the appropriate tables where necessary. Users are not allowed to create objects in the database.
I agree with the KISS principle as well.
James Brake
DBA
Associate Electric Cooperative, Inc.
May 19, 2003 at 10:17 am
Wow, I was expecting more complaints against the article, but thanks for all the comments.
I see the "developers" argument, but with the Personal edition being available, I'm not sure it is needed.
For the "customers" problem, I understand what you're doing, but I still think you could get around it. Having all those views lying around isn't good practice either. If it's your app, you could also have views as MyCust_whatevername, prefixing views for each customer.
Again, thanks for the comments and I stand by my view the schema isn't needed.
Steve Jones
May 19, 2003 at 10:52 am
Schemas seem to have been part of the SQL standard for a very long time, possibly from the first version in the 1980s. (I'm referring to Date and Darwen's excellent book, "A Guide to the SQL Standard".) So it is to the standards bodies, not the vendors, we should turn first if we think this feature is unnecessary at best and confusing at worst.
And I tend to agree it is unnecessary and confusing. All the vendor implementations are now very good at cross-database querying. (In the early years of RDBMS this was probably not so.) So in nearly every case where separate schemas might seem useful, separate databases would now seem to be a viable option.
May 20, 2003 at 8:27 am
Schema is really confusing, even if we are removing schema nothing will happen.But I favor the idea of more than one sql users which is slightly diffrent from schema .Let the sysobjects uid column be there to assure the protection of the objects like one should not drop others objects. In this scenario if Microsoft is ready to include some pattern enforcing mechanism to nomenclature of user objects it will be better. Like a user ID ACR is created for doveloping Accounts Receivables System all the objects created by this user should start some thing like ‘ACR_%’
john cyriac
May 21, 2003 at 7:32 am
I agree I have not really understood why the schema concept is there. It has caused more problems for me that it has been useful. We have a research dept that I got tired of creating hundreds of views for so I finally gave them Enterprise Manager and only gave the rights to create views. That was great until someone quits and no one else has access to thier views. Now I have to go in and change ownership on each view to someone who is currently working here.
I think using the Personal edition is the best answer for developers. It allows developers to have thier own environment and I don't have to worry about them even possibly wrecking production environment. Also they might learn a thing or two about SQL Server in the process.
Ross
May 21, 2003 at 3:19 pm
I guess I get to be one of the few to see a value in schemas. While there are probably far more scenarios where schemas can be a hinderance, scenarios still do exist where they can be useful. Many have commented on the idea that a database is a shared repository of information, yet, there are times when it is helpful for various organizations to have a "view" or way of seeing a database that is distinct from other organizations that access that database.
For example, suppose that you provide a web-based application to various organizations. You host the databases at your site, and each database has the same database structure, but different data (data unique to each organization) is contained in each database. Oftentimes, in a situation such as this, you would create a different database for each organization that is using our application, but suppose there is some data that is relatively generic and could usefully be shared between all organizations that use the product; zip code information is one example, and there may be other examples unique to the particular class of business you are dealing with. You may want to enforce certain referential integrity constraints on this data to assure - for example - that address information that is entered into a database always has legitimate zip codes, or to protect yourself from various data anomalies that can arise without RI. You could continue to maintain a separate database for each of your customers, and simply keep a copy of the shared information in each database, but that creates more work. Or, you could store the shared information in its own separate database, but then RI becomes troublesome - since you can't have declared RI that spans databases.
Here is a place where the "schema" concept can be useful. An alternate - and workable - solution is to keep all of your customers in a single database, but provide a different schema for each customer. Any objects that are to be shared by all customers can be owned by DBO. Every customer can have their own login that matches the owner of their schema (remember that in this case, a customer is an organization, so this may be actually several users belonging to the same organization that share the login); hence, each customer can see only their objects and objects owned by DBO (to which they've been granted access). Thus, they have an isolated view of the data in the database. Your web-based application becomes simple to program and can be shared by all customers - the login they provide is the key that gives them access to their schema.
Ultimately, it boils down to this (and I've seen at least one post here on SQL Server Central where a database developer had a scenario like this); there are situations where it is helpful for different groups to access different database schemas (either different in structure or different only in data) while at the same time having access to certain common, shared data. There are several ways you can meet this need, but one way - one way that often goes overlooked - is to use the "schema" functionality of SQL Server. Certainly, it can be confusing at times, but the confusion is less a problem with the concept, and more a problem with the fact that we don't use it frequently enough to be familiar with it; and if done properly, the confusion can be largely minimized. In return, you get the ability to give different organization different schemas while still being able to share common data and even have declared referential integrity to that data. This reduces data storage space, database coding (that you might otherwise need to do to work around the inability to declare RI across databases), and application coding (since you can write the code once and not have to write any code to deal with multiple databases).
The need may not be common, but it is still there.
Matthew Burr
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply