Lookup tables in multiple databases?
graws...@yahoo.com
I have a question about "the best" approach to deal with lookup
tables(employees, sections, etc..) that show up again and again in
many database (HR, Inventory, CRM ) of an enterprise with Oracle DB
servers.
Should a separate database be created to include such lookup tables
and have that database referenced on the front-end with the main
database of the application whenever needed; Or have these tables
created in databases, thus, one ends up with duplicate lookup tables,
more or less.
Interms of performance, scalablity and all issues of Oracle GP DB
design??
Any urls, books to help.
MTIA,
Grawsha
EdStevens
I don't know that I'd necessarily create an entire new database, but
I'd at least designate an existing db to be the official database of
all enterprise-wide data. Put it in its own schema. Then again,
maybe I would create a database specifically for this. First rule:
eliminate data duplication.
In the words of my first DBA instructor, "it depends."
Mark D Powell
I agree with Ed. I would also add that developers with a MS SQL
Server and some other database manager product backgrounds often use
the term database when he or she should be just discussing use of a
schema or object naming convention.
Generally speaking with Oracle you want one database for every
application that shares the same uptime/upgrade restrictions/
requirements. That is, if the applications can share the same
database then share it.
HTH -- Mark D Powell --
CrazyKarma
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
alsa...@hotmail.com
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.
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
EdStevens
> On Aug 17, 12:20 am, CrazyKarma <ska...@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.
CrazyKarma
they are distributed or not distributed. If you donot conform the data
then you run into the risk of inconsistent data, data corruption can
easily happen and affecting the quality of product that your company
delivers, which I believe is much bigger price to pay.
The other approach is to use materialized views. If they are really
lookup tables then I don't see any rational in maintaining then
separately in every instance of database.
Translating the business needs to right scable design is also
something to bear in mind..
CrazyKarma
Mark D Powell
>
> - 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.
joel garry
There is a minor performance issue having to do with table sizing.
"Small" tables ("the maximum of 2% of the buffer cache and 20,
whichever is bigger" - performance tuning guide) are not put on the
tail end of the LRU list in the buffer cache. So you can have funny
things happen when comparing duplicate lookup tables vs. everything
lookup tables within a database (I'm assuming you were using
"database" as Oracle people use "schema", since you specified HR,
Inventory etc.). For an example similar to Ed's, you might have
small tables with different attributes of states and governers, each
of which is considered a small table, but when you put them together
with a bunch of other unrelated things and access large portions of
the table, the result is no longer small. If it is pounded on, then
it will stay in memory anyways (but if something in it is updated by
many users, you may wind up with latching/cpu issues), but if it is
lightly used and lots of other stuff is going on, it may slow things
down by aging out of memory and then being brought back in. And then
the definition of "small" changes simply by changing the cache size.
And many things are "small" these days since memory has gotten cheaper
and SGA's larger.
So really, there are many variables, and predicting what will happen
requires knowing what they all are and how they interact. Too many to
make a general statement about which way to go, so that's why many
people recommend just designing the applications correctly and don't
try to play games to make it fast until you find something that
actually is making it go slow. There are many knobs to play with,
including multiple buffer pools, which can mean lumping everything
together may reduce your ability to fine tune issues.
jg
--
@home.com is bogus.
""I stay away from fried foods." - Jeremy Orme, vendor of deep-fried
Pepsi.