Re: Schema Design.

#1

On Thu, 09 Dec 2010 10:28:01 +0000, Preston wrote:

Quote:

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?

Don't hard code schema names in application code; use synonyms.

#2

On Thu, 9 Dec 2010 10:28:01 +0000 (UTC), "Preston"
<dontwantany (AT) nowhere (DOT) invalid> wrote:

Quote:

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.

#3

Fred Pierce wrote:

Quote:

only allow DML on the database via packages

I disagree with (only) this, this is a waste of resources, increases complexity.

#4

On Dec 9, 2:28*am, "Preston" <dontwant... (AT) nowhere (DOT) invalid> wrote:

Quote:

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?

--
Preston.

This winds up being an intractable problem. I work on an enterprise
app that originally was a couple of apps/schemata, then various
modules within those were spun off as separate options. Over time,
one schema became dominant, and an argument could be made everything
should have been lumped in there to begin with. As Fred pointed out,
people think of these schemata as separate databases (which is what
they are called in non-Oracle dbms's and tools like Excel), so strange
things happen - different definitions of the same field or table, or
sometimes the same definition but one table isn't used, and so forth.
Module definitions wind up being hazy at times, as with things being
executed in modules you haven't bought, or bizarro naming conventions
as things go from one module to many or are converted to general
library routines or engines.

I created another "database" when I wrote an extension to an analysis
module for DSS purposes. The idea was, since people would be doing
all this weird stuff, they could put it on their own PC accessed
remotely from the server and not load down the OLTP server with the
processing, and by layering this way only the extract program would
need to be changed on upgrades. What wound up happening is those
programs got bureaucratized and run en mass by an admin on the server,
and the few people who do more extensive analysis bought some SS based
program that (horribly!) accesses the original schema live transaction
tables and sucks down everything, then analytics run on PC's. You
can't win. Fortunately, relatively cheap servers are very powerful
these days.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...es-app-course/

#5

Preston wrote,on my timestamp of 9/12/2010 9:28 PM:

Quote:

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?

Aw, never mind all that "complexity".

Just use this:
http://blogs.oracle.com/mdm/2010/12/...le_schema.html

(yegawds, is there no limit to the idiocy of these people?...)

#6

On Fri, 10 Dec 2010 20:32:45 +1100, Noons wrote:

Quote:

ust use this:
http://blogs.oracle.com/mdm/2010/12/...le_schema.html

(yegawds, is there no limit to the idiocy of these people?...)

I like the picture: one schema to rule them all, one schema to find them,
one schema to bring them all and in EBS bind them...

--
http://mgogala.byethost5.com

#7

On Dec 10, 2:20*am, "Preston" <dontwant... (AT) nowhere (DOT) invalid> wrote:

Quote:

joel garry wrote:
On Dec 9, 2:28 am, "Preston" <dontwant... (AT) nowhere (DOT) invalid> wrote:

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.

This winds up being an intractable problem. *I work on an enterprise
app that originally was a couple of apps/schemata, then various
modules within those were spun off as separate options. *Over time,
one schema became dominant, and an argument could be made everything
should have been lumped in there to begin with. *As Fred pointed out,
people think of these schemata as separate databases (which is what
they are called in non-Oracle dbms's and tools like Excel), so strange
things happen - different definitions of the same field or table, or
sometimes the same definition but one table isn't used, and so forth.

That is something we need to be careful of, not because there's any
confusion over what a schema is, but because we've all been involved
with this system for 14 years & the object names for each process are
firmly ingrained. As many of those processes will now be duplicated to
a certain extent in the new apps, I suspect the developers may start
creating new db objects with the same names as the original ones but
with '_new' tagged on the end to make them easy to remember. I guess
that's one reason to just have one new schema for the new stuff - it
makes a simple naming standard easier.

lol, I can imagine the naming you'll have 14 years from now with all
those old new er uh...

....

Quote:

Heh, that's exactly what one of our clients was doing (in Access), &
drove the decision to create one of the new apps. It will still run on
data sucked down overnight, but simply because they need a static view
throughout the day without any new transactions affecting things.

I still come unstuck in time when I realize I'm writing the same
program I wrote in 1980...

jg
--
@home.com is bogus.
If at first you don't succeed, don't test yourself against Nature.
http://www.canoekayak.com/whitewater...-by-crocodile/

#8

"Mladen Gogala" <gogala.mladen (AT) gmail (DOT) com> wrote

Quote:

On Fri, 10 Dec 2010 20:32:45 +1100, Noons wrote:

ust use this:
http://blogs.oracle.com/mdm/2010/12/...le_schema.html

(yegawds, is there no limit to the idiocy of these people?...)

I like the picture: one schema to rule them all, one schema to find them,
one schema to bring them all and in EBS bind them...

For the purposes of scansion I think you need a little poetic licence here:

One scheme to rule them all, one scheme to find them,
one scheme to bring them all and in the E-Biz bind them.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

#9

On Dec 10, 9:14*am, "Jonathan Lewis" <jonat... (AT) jlcomp (DOT) demon.co.uk>
wrote:

Quote:

"Mladen Gogala" <gogala.mla... (AT) gmail (DOT) com> wrote in message

news

an.2010.12.10.13.37.57 (AT) gmail (DOT) com...

On Fri, 10 Dec 2010 20:32:45 +1100, Noons wrote:

ust use this:
http://blogs.oracle.com/mdm/2010/12/...le_schema.html

(yegawds, is there no limit to the idiocy of these people?...)

I like the picture: one schema to rule them all, one schema to find them,
one schema to bring them all and in EBS bind them...

For the purposes of scansion I think you need a little poetic licence here:

One scheme to rule them all, one scheme to find them,
one scheme to bring them all and in the E-Biz bind them.

--
Regards

Jonathan Lewishttp://jonathanlewis.wordpress.com

Wow, I've already learnt two things today. (Or three, if you count
learnt, which google groups thinks is incorrect.)

From E. Britannica: "The purpose of scansion is to enhance the
reader's sensitivity to the ways in which rhythmic elements in a poem
convey meaning. Deviations in a poem's metrical pattern are often
significant to its meaning. "

From merriam-webster.com: "Definition of SCHEME
1
a archaic (1) : a mathematical or astronomical diagram (2) : a
representation of the astrological aspects of the planets at a
particular time b : a graphic sketch or outline
2
: a concise statement or table : epitome
3
: a plan or program of action; especially : a crafty or secret one
4
: a systematic or organized configuration"

So _that's_ where you got your plan visualization method :-)

Noons, Mladen and Jonathan: the scheme meme dream team!

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...ry-house-burn/

#10

joel garry wrote,on my timestamp of 11/12/2010 4:52 AM:

Quote:

Wow, I've already learnt two things today. (Or three, if you count
learnt, which google groups thinks is incorrect.)

From E. Britannica: "The purpose of scansion is to enhance the
reader's sensitivity to the ways in which rhythmic elements in a poem
convey meaning. Deviations in a poem's metrical pattern are often
significant to its meaning. "

From merriam-webster.com: "Definition of SCHEME
1
a archaic (1) : a mathematical or astronomical diagram (2) : a
representation of the astrological aspects of the planets at a
particular time b : a graphic sketch or outline
2
: a concise statement or table : epitome
3
: a plan or program of action; especially : a crafty or secret one
4
: a systematic or organized configuration"

So _that's_ where you got your plan visualization method :-)

Noons, Mladen and Jonathan: the scheme meme dream team!

Can we start again? I've got an headache...