Re: Schema Design.

> On Dec 9, 2:28 am, "Preston" <dontwant..._at_nowhere.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.

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

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.

-- 
Preston.