Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Schema Debate


The Schema Debate

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36145 Visits: 18748
Comments posted to this topic are about the item The Schema Debate

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
eric.fung
eric.fung
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 139
I think : for developers used to work on SQL Server, they will more likely see "Database" as one components of an application. So if there are many applications running in your organization, there will be multiple "Database(S)".

In that case, I think schema may have little value. As in that case the boundary of every applications will naturally base on database rather than schema.

However, if "Database" resume its original meaning as it first appear, where it should be a consistent information source for different applications, each provides a unique information usage of its kind in an organization, then schema can help to make such boundary easy to be administer.

I know that as nowadays, most organization may run applications built by different third party. Each application vendor will like to have absolute control on the database behavior. So a single Database to support all information requirement of an organization seems far from the truth. Neither will such centralize approach helps to scale the database to meet the needs of the organization. It may even cause recovery and availability issues to exaggerate to another order of magnitude of difficulty.

However, the rise of database is also the cure at the time as it appear to overcome the problems from isolated data source, excessive replication of data, and lack of consistency among data contents. When we see there is all kinds of challenge in integration, such decentralize approach to database may also reintroduce the problems as the cure originally aim to solve.

For me, schema is more likely a tools used in managing diverse data in a centralize database environment. Perhaps its importance may also gone as the database nowadays is no longer the database when it originally proposed.

Rgds,
Eric
rick.fitch
rick.fitch
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 23
Cost is another reason.
In our environment, when our databases were on our own servers there was no reason not to have a different database for every application and there were 80 or 90.
Then, the servers were moved offsite to be managed by a 3rd party. They charged by the database.
When mandates to reduce costs started coming down, a logical solution was to reorganize our apps into a handful of databases and seperate them logically by schema.

It would, of course, be much more useful at times to be able to switch the hierarchy of how object types (tables, views, stored procs, etc) and schemas are displayed in the tree (so all the objects that belong to a given schema fall together)
Craig@Work
Craig@Work
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1140 Visits: 326
Simple and stupid sometimes works.

There are multiple scenarios ( I think ) where use of multiple schema seem to make sense (to me).

For example, consider partial projects A and B. Database X is comprised of A only, database Y from A and B. It 'makes sense' to have the schema a. and b. (and everyone kind of undestands what a. and b. are 'for').

Alternatively, I have a set of procedures in schema priveleged. and another in report. . Everyone can tell just by looking at the schema roughly what its function is and likely set of privileges. Giving write permissions to roles/users etc. with only access to objects in report. would probably be silly.

I think schema help clarify what things are for, helps organise the code-base (something pure-DBAs can sometimes appear to forget?), and makes managing things a little easier.

Its just my view, and it works for me (so far) :/ !
r5d4
r5d4
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 812
I love schemas for 2 reasons

1) Organisation
Grouping tables as belonging together in a logical group.
For example from the AdventureWorks db > HumanResources.Employee , Person.Address , Sales.CreditCard.

2) Security
Permissions assigned at schema level make it easier to control relevant access to developers etc by subject area. Teams working on separate parts of a project can have different permissions within a db.
Application logins from different software components can have only the permissions they need.
Yes this can be done on an object level, but how much easier is it to do via schema from the start.
It will be one command versus many when granting access to a set of objects when grouping them by schema.
peter-757102
peter-757102
SSC-Addicted
SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)

Group: General Forum Members
Points: 451 Visits: 2549
A schema can best be seen as a namespace, separating one functional group of entities from another. This allows modular design and reuse of these functional modules without naming collisions. Entity and constraint names only have to be unique in the schema itself, so you can use meaningful, yet common table and constraint names (say: user or status). If you put everything into one schema you cannot use common table names if you want to work modular!!!

For example, you can have a mail system schema containing everything related to user mails (scheduling, log, blacklists, etc.). A security schema that deals with specific application user accounts and their roles/rights within your application. Another thing we use it for is imports that may or may not result in actual active records in the main schema.

Other obvious uses are a forum schema or a CMS schema, or as we have here a dedicated database with schemas containing reusable table valued functions and constants. One schema for db maintentance related procedures, one for string manipulation, one for math functions, one for constands (numbers table), etc.

Another use might be entities for zipcode validation and or IP country location information. This one is arguably one that you might want to use a separate database for, instead of a schema. But even then when you design the models, use a schema with a logical name so you can simply move it into another database when you need to without any changes.
John Magnabosco
John Magnabosco
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 385
A few posts prior to mine make the points that I will make regarding database object schemas; but I would like to add that I am actively utilizing these in a rather complex database design and they are extremely helpful. I am a huge fan of database object schemas.

My original intent to utilize database object schemas was to organize the database objects. For example: all of the tables, views and stored procedures that relate to customer information is in a "Customer" schema. All of the tables, views and stored procedures that relate to account information is in an "Account" schema. This has proved to be tremendously helpful when identifying the items that are functionally related.

In the above schemas, I did not apply any special permission combinations. I did however create a couple of schemas where permissions did come into play. I have a schema specifically for sensitive data. With this data being segregated into its own schema, I can better control who can see the sensitive data and who cannot. (I wrote about this in my book "Protecting SQL Server Data")

I also created a schema for all of the objects that are involved with an a batching process. General users cannot see these objects or interact with them. When the time comes and we need to eliminate the batch process, I know exactly what objects were involved. If removal of these objects is the desire it can be with ease and confidence.
SuperDBA-207096
SuperDBA-207096
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1477 Visits: 711
I like the comparison of schemas to namespaces. a shared database containing groups of tables for different things (addresses, customers, etc) could use schemas depending on what logical grouping of objects you want to use...
WI-DBA
WI-DBA
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 605
As reflecting in many comments already, they enable those of us who managed a series of databases that cross many applications to manage security extremely easily. No longer does each source file(proc, view) have to have grants in it, or any creative work to set security. Schemas(namespaces seems to be easier for them to understand) to simplify those pieces.

ordersystem.pr_Get_Order is just easier to manage than dbo.pr_os_Get_Order (or whatever) from the standpoint that schemas make permission management so simple.

One app per database? Overhead - 10 apps sharing 7 databases, big gains in efficiency (and self documentation)

Cheers
http://twitter.com/widba
http://widba.blogspot.com/
Jim Burnell
Jim Burnell
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 51
We don't actually do this yet, but I'm trying to convince our group to do it:

Our project is subordinate to another SOA project that conglomerates data from multiple sources and shares it via web services.

About twice a year, they update (sometimes dramatically) their XML schemas (XSDs).

Since we don't really control the structure of the web service data, it seems to me like a logical design would be to have a SOA master schema where we store information about the web service calls (raw XML requests and responses, timestamps, etc), with a "selector" field that indicates which SQL Server schema contains the "exploded" relational data. Since we don't really control the format of this data, and since it changes dramatically over time, it seems logical to me to use multiple SQL Server schemas as independent namespaces so that the data can reside in its "native" relational format without transformation.

Does this seem like a good design and/or a good use of SQL Server schemas?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search