Re: Lookup tables in multiple databases?

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. Received on Fri Aug 17 2007 - 08:16:52 CDT