Is the Schema Still Needed?
-
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.
-
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
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
-
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?)
-
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
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
-
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.
-
I'm a simple, caveman database administrator. Terms like "Cross-database Ownership Chaining" and "Meta Data Services" frighten and confuse me. But what I understand is that a "schema" is a place to hold the information about the database. Meta-data is our friend, helping us move databases to other machines and platforms.
Do we blame the schema for the difficulty we have in moving MS databases to servers with different names? Do we blame the schema for assigning ownership of all objects to "dbo" by default? While the Empire may have implemented a version designed to impose their omnipotence upon us, and while we may find this cumbersome or "useless," a schema is a necessary part of a database. The schema objects are exposed to us to see what indexes are created, what constraints, what columns, not merely who owns these objects. Enterprise Manager reads the information in the schema to help us understand the database(s). Schemas contain the metadata of our flawed view of reality we humbly attempt to capture as data in our database(s), so help us Codd. Schemas enable the "FOR XML" clause in SQL to execute. Hide the schema if you wish. Make fun of it, mock it even, but to eliminate it brings us back to the days of B-trieve. The days we used rocks to chip data from our 5 Megabyte hard drives. The days when the term "normalization" was a '60s dirty word. I implore you to campaign for an easier to use schema with built-in ERD diagram capabilities, easier to use and understand security, define-able defaults (not just "dbo?"). My defense rests.I think the link below, if it displays ok, will be of interest to all regardless of their stance:
[http://www.well.com/~doctorow/metacrap.htm#2][/http://www.well.com/~doctorow/metacrap.htm#2]
-
Is support for multiple instances of same-named object a technical requirement for a contemporary DB - Yes
Do most implementations need it - No.
To borrow from a different debate on security and authority:
"I'd hate to be in a situation where I needed a handgun but I'd hate it even worse if I was in a situation where I needed a handgun and DIDNT HAVE ONE."
-
Why is the same named object required in modern dbs?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
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 -
The use of schemas is common in Oracle because small "databases" in Oracle tend to be implemented within a single instance, each owned by a separate schema. This, along with public synonyms hide most of the complexity of this from the user but still make maintenance for the DBA more straightforward and helps with understandability (is that even a word?) for developers.
What I see with Access apps that have been "upsized" to SQL Server is a similar siloed approach, but that pigeonhole the data into a bunch of small databases (absent active intervention from the DBA) instead of several schemas within a database. An even worse approach for the administrator (in this case, me
).I think once SQL Server "Yukon" gets synonyms and a proper schema implementation people will warm up to them. As an example, think of the [sysobjects] table as being owned by [sys] with a name of [objects]: [sys].[objects] and you'll see that the good folks at MS have been working around this issue for some time, and so have most of us. We have a huge reporting database on a reporting server that is a subscriber to dozens of publications from several SQL Servers. Most of the tables have prefixed names in a sourcesystemname_tablename format, so why not use schemas instead? They would make the identification of related datasets a lot easier, and the security model on top of schemas is also much more straightforward that what we do in SQL Server.
My two cents...
---
Resident DBMS fence sitter. -
Interesting idea. I hadn't considered that.
Thanks for the info.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
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 -
let me share my experiance. I came across a situation a couple of years ago where 2 applications use "identitical set of tables". In that system we had the middle layer which connects to the database.
Our solution was this: We created 2 users. user1 will be used by application1 and user2 for application2. both users have DBO rights in the database, (But not sa) and the same code is used to do all the database stuff. Only difference was on the connection string.
We were able to maintain code reusability. It is in a way "horizontal partitioning" but no additional triggers, etc.
I don't know whether this is a valid reason to justify the existance of schema, but may be a simple use of schema.
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
Huh? Steve writes "The notion of a schema ... " and "... having a schema ...", as if the schema is optional.
Am I missing something? The schema is the core of any database. All the tools built around it are of great assistance, but if you have to get back to what is really going on, the schema defines that. Take away the schema, and you take away the database.
Are the questions really, "How many, and how often do, db developers directly view, edit, create, modify, transfer, etc the schema, as opposed to simply using the tools built around the db?" The answer to those question are likely "very few" and "infrequently".
Personally, on any decent size project, I normally end up at least generating and viewing the schema. Often I backup the db structure by saving the schema. I sometimes find it quickest to get an overall picture of a midsize db by looking at a few pages of the schema definition. Often I find it faster and more consistent to copy & edit schemas by hand, rather than going thru the click, click, click of a UI.
-
A schema is just the structure of the database or database object; that is, the T-SQL. It's been discussed here as if it's some ethereal notion or pie in the sky concept. Yes, Microsoft has the concept of the object owner, so that you can have multiple customer tables, for example. This is merely a violation of relational database theory.
I'm still laughing at the headline though; Is the schema still needed? Is the motherboard still needed? Is the floor still needed? What choo talkin' 'bout Steve?
John Scarborough
MCDBA, MCSA
Do we blame the schema for the difficulty we have in moving MS databases to servers with different names? Do we blame the schema for assigning ownership of all objects to "dbo" by default? While the Empire may have implemented a version designed to impose their omnipotence upon us, and while we may find this cumbersome or "useless," a schema is a necessary part of a database. The schema objects are exposed to us to see what indexes are created, what constraints, what columns, not merely who owns these objects. Enterprise Manager reads the information in the schema to help us understand the database(s). Schemas contain the metadata of our flawed view of reality we humbly attempt to capture as data in our database(s), so help us Codd. Schemas enable the "FOR XML" clause in SQL to execute. Hide the schema if you wish. Make fun of it, mock it even, but to eliminate it brings us back to the days of B-trieve. The days we used rocks to chip data from our 5 Megabyte hard drives. The days when the term "normalization" was a '60s dirty word. I implore you to campaign for an easier to use schema with built-in ERD diagram capabilities, easier to use and understand security, define-able defaults (not just "dbo?"). My defense rests.
).Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply