When should I create a new schema?

[tpeters1]

I am a developer. When I create new applications I like to place all the tables for the application in one schema in order to organize the database and make it easier to understand. Our DBA is contantly pushing us to not create new schemas.

Is our DBA full of "you know what"?

Is one giant schema really the best way to organize a database with multiple applications?

Is there any kind of industry standard set of criteria for making this kind of decision?

By what criteria do you all decide whether to create a new schema or to throw the new tables and such into one monolithic schema with a bunch of other unrelated objects?

 

[SantaMufasa]

T,

I believe that I can say with confidence that the typical practice is to create schemas where there is a logical "business"/application/functional boundary.

For example, I create a new schema for a new computing application for which I want all of the database objects to be gathered, yet kept separate from objects of some other application.

I also create new schemas for (within) a single application where I want to separate "release statuses". For example, for application "XYZ", I would create separate schemas for:[ul]
[li]Development: "XYZ_DEV"[/li]
[li]Testing: "XYZ_TEST"[/li]
[li]Demo: "XYZ_DEMO"[/li]
[li]Release: "XYZ_REL"[/li]
[li]Production: "XYZ_PROD"[/li]
[/ul]If I use this methodology, I can promote from one completed area to the next, with very little administrative complexity, all on the same instance if I prefer.


Having the above functionality would be virtually impossible in an environment where the DBA was against creating new schemas.

As an overriding Rule of Thumb, an organization (read: "IT Department", "DBA", et cetera) should create policies and procedures for which there is a direct, positive relationship to the corporate "bottom line". That is, our policies and procedures should cause either a traceable increase to revenues or decrease to expenses.

In your case, I believe that my example, above, shows that having intelligent proliferation of schemas reduces IT expenses, moreso than having a default institutional resistance to creating schemas.

Let us know how your "battle" turns out.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[tpeters1]

Thanks for responding.

It seems that there there is consensus in the industry for creating new schemas according to logical, fuctional divisions. I have not heard the full force of the DBAs arguments at this point. But from his prior comments in passing, he seems to think that the maintenance effort incurred by creating new schemas is the reason to not create more.

I dunno, is there alot work required of the DBA in order to maintain an individual schema? Backups? Statistics gathering? Security?

Here is a funny one... Another develper has analyzed the number of schemas on the database instance and found the the DBAs have more schemas (sys, sysman, system, etc.) than we do! lol

 

[SantaMufasa]

IMHO, if the level of effort to maintain individual schemas exceeds the patience level of the DBA, then s/he needs to learn how to use automated tools/scripts to do their work for them. Using automation brings the level of effort to maintain multiple schemas down to roughly the same effort to maintain a single schema.

Again, implementation of policies and procedures should based upon the overall effect upon the organisation's bottom line, not necessarily the "comfort level" of the DBA. If the DBA can show that s/he spends an inordinate amount of time on maintaining individual schemas (despite automation), then s/he might be able to build a "bottom-line" case for fewer schemas. But I, personally, don't have any problem with creating a new schema when business needs dictate.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]