Re: Schema Design.
Date: Thu, 09 Dec 2010 09:47:58 -0500
Message-ID: <oup1g6l0qm0rl2u1fr0154qcde61vcmb9u_at_4ax.com>
On Thu, 9 Dec 2010 10:28:01 +0000 (UTC), "Preston" <dontwantany_at_nowhere.invalid> wrote:
>We've got a few applications that currently all use the same schema
>(let's call it "FRED"). There's a main application that all our clients
>have, & a few add-on optional apps. We're about to start building a
>couple of new apps (both in APEX), which again will only be available
>to clients with the main app, but which will have some new tables (&
>plenty of procedures) that are only used by them.
>
>Some of the new objects will be used by both the new apps, & some by
>just one of them. Additionally we're also planning to re-write the
>existing main app (again in APEX), at which point it too will use some
>of the new tables/procedures, but mainly it will stick with what's in
>the current "FRED" schema. With me so far..?
>
>So what I'm trying to figure out is the best way to map applications to
>schemas, bearing in mind this is a database & applications that we
>install & maintain at various client sites. I don't want to stick with
>using a single schema for various reasons.
>
>A separate schema for each app is one option, but who gets the
>tables/procs used by multiple apps? We could use a "SHARED" schema for
>those, but I suspect that would get messy as people develop
>app-specific objects in the future then realise they could be used by
>other apps too & move them to "SHARED". Either option could be a
>nightmare when it comes to remembering to put schema. in front of the
>object names, not to mention the potential re-work needed if objects
>are moved to different schemas.
>
>Thoughts anyone?
The primary design goal should be to make sure the database as a whole is normalized, integrities maintained etc., i.e. it should be a coherent whole regardless of what schema the tables belong to. As you've indicated, apps come and go, are modified, overlap etc. so it's nearly impossible to guarantee that app A will only and forever access schema A.
So the logical design should be seen as one schema. Then ask what reasons and criteria you use for dividing it up. Security? Tablespace management? The former is one reason I seperate some things into seperate schema, since security should be implemented at the database level, not app level.
It's not clear in your question but one thing to avoid is running apps from the owner account. Owner accounts should be locked and all apps etc. should be accessing via a separate account with only privs needed to run the app. As someone else said, use synonyms and roles and only allow DML on the database via packages so it's fairly easy to modify if an object is moved to a different schema.
Some objects will no doubt end up in a shared schema but again, using synonyms and avoiding hard-coding stuff should make it all manageable. Good desgn SW and config management are pretty important of course - it does get messy if you don't have a well-organized and documented approach.
Biggest problem I've encountered is the issue I mentioned in my first paragraph. People - designers, coders etc. start thinking of the separate schemas as seperate databases and end up inadvertantly denormalizing, i.e. duplicating objects and attributes etc. Always remember that the app is just a view of the data (Tom Kyte?).
Fred Pierce - avialantic.com
cobscookbaymusic.com
Easternmost Fred in the U.S. Received on Thu Dec 09 2010 - 08:47:58 CST