Re: Schema Design.

> 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
> >
> > 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.
>
>
> 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.

Security is one aspect, especially as one of the new apps will be web-facing (but not open to the public, it's akin to online banking). There are other aspects involved which I can't go into in public, but if all else was equal, they'd drive me down the route of using pipelined functions to create 'logical tables', which would bear little resemblance to the physical tables. It would work, & any performance hit wouldn't be an issue with the number of users we're talking about, but it just goes so against the grain.

> 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.

Almost all DML will involve some sort of transaction processing, so yeah, there'll be very little CRUD in the apps.

> 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?).

No too much of a problem with us as we're a very small company, so it's easy for me to keep a handle on things. Thanks for your thoughts.

-- 
Preston.