Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Is the Schema Still Needed? Expand / Collapse
Author
Message
Posted Monday, May 19, 2003 10:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
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
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
www.dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #60500
Posted Monday, May 19, 2003 10:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 30, 2010 11:40 AM
Points: 2, Visits: 4
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.





Post #60501
Posted Tuesday, May 20, 2003 8:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, October 23, 2010 4:51 AM
Points: 80, Visits: 58
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





Post #60502
Posted Wednesday, May 21, 2003 7:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 15, 2007 9:22 AM
Points: 238, Visits: 1
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




Post #60503
Posted Wednesday, May 21, 2003 3:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 26, 2004 4:04 PM
Points: 161, Visits: 1
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




Post #60504
Posted Wednesday, May 21, 2003 4:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, July 07, 2012 4:08 PM
Points: 21, Visits: 55
Matthew, thank you. Your example makes total sense to me. I've seen a lot of threads asking the question "How should I... different databases or one database?" for essentially the same scenario you pointed out. And while in the past my preference has been separate databases, you do run into the redundancy of common data, or the separate DB for common data issue. And, as you briefly mentioned, any modification to sprocs have to be deployed across all the databases, where here you just have the one copy in production. (or I guess you could have schema specific sprocs if you needed them, but I like one common set of code most of the time). Of course, you still have the issue of any changes to table definitions have to be rolled out to all schemas.

So again, thanks for providing a reasonable use.




Post #60505
Posted Thursday, May 22, 2003 1:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:27 AM
Points: 6,997, Visits: 8,411
I guess problems with schema started when ms introduced a "everyone" schema : DBO.
Making a "transparant" option is a terrific way to mistify objects.
Backside of this implementation is (of course) the ownership-chaining-struggle.
Because they did implement authority at db-level (e.g. db-datareader) and did not implement
schema-based authority (grant to schema1) it's an all or struggle option.
This combined with the fact that in some cases only dbo-objects are supported with upgrades
(did they forget about schema (i.e.bug) or was this the intention ? I had a couple of non-dbo tables that were replicated and gave me a couple of headaches when
I upgraded that server from sql7 to sql2k.)
Maybe the time to rollback the dbo-concept is nolonger an option and we all are asking ourselves what the use of schema is becaus it gives us so much troubles.
Thank you transparacy.



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #60506
Posted Thursday, May 22, 2003 3:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 16, 2004 2:54 AM
Points: 6, Visits: 1
I would just be happier if they corrected the BUG that doesn't allow a dbowner who is not a sysadmin to automatically create tables under the "dbo" schema (I presume that "dbo" means "db owner", or am I an idiot?)




Post #60507
Posted Thursday, May 22, 2003 5:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:27 AM
Points: 6,997, Visits: 8,411
dbo = dust between objects ;)
It is annoying that members of the db_owner group don't create dbo-objects by default. They are not adapted to always mention the object-schema, but if it only is one user, make that user database owner (exec sp_changedbowner @loginame = 'Jobi' ,@map = 'true') and it will be ok.



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #60508
Posted Thursday, May 22, 2003 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 16, 2004 6:23 AM
Points: 4, Visits: 1
seems like a nice feature would be to let the database owner or sysadmin set an option that makes objects owned by dbo by default.

Still, it seems that most database nowadays are used for access from a frontend application - the user's rarely if ever directly access the database via Query Analyzer, and the people doing the ddl are database owners anyhow.

Schema is useful, but should be an option that can be defaulted to some value.




Post #60509
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse