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 1234»»»

The Schema Debate Expand / Collapse
Author
Message
Posted Monday, June 21, 2010 8:42 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 11:08 AM
Points: 31,371, Visits: 15,839
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
Post #940727
Posted Monday, June 21, 2010 11:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:28 PM
Points: 16, Visits: 102
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
Post #940757
Posted Tuesday, June 22, 2010 1:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 5, 2013 11:33 AM
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)
Post #940788
Posted Tuesday, June 22, 2010 2:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 5, 2014 6:18 AM
Points: 1,140, 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) :/ !





Post #940807
Posted Tuesday, June 22, 2010 2:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 22, 2014 10:35 AM
Points: 267, Visits: 691
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.
Post #940841
Posted Tuesday, June 22, 2010 3:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 22, 2014 3:42 AM
Points: 337, Visits: 2,283
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.
Post #940854
Posted Tuesday, June 22, 2010 4:08 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 19, 2013 2:03 PM
Points: 62, Visits: 379
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.
Post #940882
Posted Tuesday, June 22, 2010 5:59 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, 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...
Post #940955
Posted Tuesday, June 22, 2010 6:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 6:03 AM
Points: 94, Visits: 596
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/
Post #940980
Posted Tuesday, June 22, 2010 7:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 25, 2014 2:20 PM
Points: 20, Visits: 46
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?
Post #941004
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse