Re: Lookup tables in multiple databases?

On Aug 17, 9:16 am, EdStevens <quetico_..._at_yahoo.com> wrote:
> On Aug 17, 7:12 am, alsal..._at_hotmail.com wrote:
>
>
>
>
>
> > On Aug 17, 12:20 am, CrazyKarma <ska..._at_gmail.com> wrote:
>
> > > Here are things that I might do, if I were you
>
> > > 1. Consolidate all the data for a given lookup table from other
> > > databases to one database that you think is going to act like a
> > > primary source.
> > > 2. Make others point this master lookup table through
> > > dblink(synonyms)
> > > 3. Drop the other clones that exist in other databases
>
> > > This solution doesn't call for creating a new database just to hold
> > > lookup tables.
>
> > > - CrazyKarma
>
> > Here the talk is about non-distrusted environment where all
> > applications reside on a single server.
>
> > What about distributed one? That is, different databases of
> > applications physically located on different servers. I have seen
> > companies implementing this type of architecture where for PERFORMANCE
> > reasons they decide to scatter databases across servers.
>
> It looks to me as if the OP *was* describing a distributed system. I
> didn't see anyone really taking the discussion away from that. And
> many distributed systems are built for many different reasons, not
> just the one you state. Actually I believe it is much more common for
> enterprise systems to "become" distributed as more and more initially
> separate apps start to grow hooks into data owned by other apps,
> rather than some grand enterprise design. FAR FAR more systems 'just
> got that way' than are totally engineered top to bottom.
>
>
>
> > Now, we all know that it is more costly to communicate across servers
> > than on a single one, thus the issue of going with non-shared entities
> > becomes an advantage over shared ones. Even Oracle TRMs say so. Just
> > look at any of these trms and you can see duplicate tables in
> > different ERP modules, i.e... It is the BUSINESS NEEDS that should
> > drive your design.
>
> > Grawsha
>
> And the business need would argue for eliminating redundant data in
> the system. Say your various apps each maintain some sort of
> reference table about U.S. states. Each has a primary key of the 2-
> char postal code for the state (ie: NY for New York, NE for Nebraska),
> and then some columns for whatever the app needs to know about that
> state. Let's say one app keeps the name of the capital and the name,
> address, phone, and email of the governor. And another app keeps the
> name of the capital, the name of the governor, and some economic
> info. And a third keeps the name of the capital, the name of the
> governor, and campaign contribution info. All of these tables in
> separate databases that were independently built to run separate
> apps. Makes perfect business sense to combine them into a single
> table and have all of the apps reference that single table. If you
> only have one entity that is a candidate for this type of
> consolidation, you certainly wouldn't build a whole new database for
> it - you'd just pick one of the existing ones to hold it. But as you
> get more and more of these you come to a point that it makes more
> sense to either create a special schema in an existing database or
> create a whole new database.- Hide quoted text -
>
> - Show quoted text -

>>

 believe it is much more common for enterprise systems to "become" distributed as more and more initially separate apps start to grow hooks into data owned by other apps, rather than some grand enterprise design
<<

Oh, so true in my experience.

Using database links to reference remote tables is one possible solution
Replication (materialized views) is another

There are many variations of how this could be handled from being a 100% Oracle solution to making use of traditional file transfer solutions. The best final solution will depend on the exact environment such as having a 1,000 locations each with its own copy of the application and database which syncs with the master db once per day or having only 5 database in two locations.

Review the available options and pick a solution that best matches the business needs.

HTH -- Mark D Powell -- Received on Sat Aug 18 2007 - 09:51:13 CDT