June 21, 2010 at 8:42 pm
Comments posted to this topic are about the item The Schema Debate
June 21, 2010 at 11:37 pm
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
June 22, 2010 at 1:36 am
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)
June 22, 2010 at 2:02 am
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) :/ !
June 22, 2010 at 2:55 am
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.
June 22, 2010 at 3:09 am
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.
June 22, 2010 at 4:08 am
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.
June 22, 2010 at 5:59 am
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...
June 22, 2010 at 6:39 am
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/
June 22, 2010 at 7:04 am
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?
June 22, 2010 at 7:07 am
Curious why software would be designed specifically to have multiple apps run from one (or less than the number of apps at least) databases? Isn't that constraining you from ever splitting that one app out and selling it off separate from the others?
Seems like a design flaw to me, but I don't know nuthin', so would welcome an explanation from those of you who do?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 22, 2010 at 7:20 am
james.burnell (6/22/2010)
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?
How to store data depends on what you need to do with it. XML schemas are meant to define the possible structure and content of an XML document. Unless you have a very good reason to "unpack" the XML to a rational format, don't to it! The schemas themself have a fixed structure, so you could if you really need to make a single model for that. If you need access to some more standard data in the XML that always remains the same or similar enough, you could write adapters that extract this data from the XML and store it in a format easily accessible by SQL next to the raw XML input. One model and schema would suffice.
Here we have also used schemas to assist in partitioning of a set of very large tables across a date dimension. Once a year we split of a year of data into its own schema in a history database and modify the views that define the partitioning for each conceptual table (all automated of course). We end up having a schema for year2000, year2001, etc, up to 2008. The final years are in an activeYears schema with might be a bit more indexed then the other schemas. The split of years are in a database that is backed up after the move (once a year) and never changes. We did not make the database readonly however.
Addition:
You might not even need to write adapters to make certain data from an XML document accessible, you can use Xpath and store the relevant paths into the documents as meta data to the stored schemas.
June 22, 2010 at 7:26 am
I have long been a Schema fan, as a developer am faced with entering new work environments, and it makes development work much easier to not require knowledge of what are the "Account Mgt." tables/views, what are the "Reporting" tables etc. They simpy save time and make communication with Business Units easier.
June 22, 2010 at 7:32 am
I wonder if schemas may provide some value just in terms of organizing the tables into logically grouped Entity Relationship Diagrams. For databases with many tables, creating a comprehensible ERD is difficult. If a tool can do it in smaller chunks by importing the metadata for one schema at a time and creating a diagram for each one separately, that may help avoid spending huge amounts of time rearranging the tables on one huge diagram to make the connections look less like spaghetti.
June 22, 2010 at 7:34 am
Most replies so far seem to be in favour of schemas.
Any more from those with a contrary view? 🙂
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply