Subject Re: FB: Cross db queries, temp tables etc
Author oneway_111
Very briefly I will comment on the responses:

1) "Merge dbs into one"
Applying this approach you will end up having one db per organization
since the data generally are cross-dependant. This will leave you
with one HUGE db. The bigger the db, the higher the changes that it
will get corrupted and, at the same time, the longer it will take to
recover it. If all your apps work against the same db, all of them
will be completely down until the db is recovered. Very bad for any
organization, realistically unacceptable.

2) "Import data from another db into db where you need it"
Not good, since you need to code jobs to import the data and keep it
in synch. Increases processing window, creates a potential for a
total mess when the same data are duplicated in several different
places and are never quite in synch.

3) "Put the logic into the middle tier where all dbs are accessible"
This basically results in manually coding joins. Possible but
performance may be a problem, plus extra development effort. It just
seems simpler (and perhaps less expensive) to get one of commercial
RDBMSs, where cross-db queries supported (all major vendors support

my R0.02

--- In ib-support@y..., Daniel Rail <daniel@a...> wrote:
> At 30/08/2002 05:25 AM, you wrote:
> >Why? Sure, I can see that it occationally can be useful to join
> >several databases, but mainly in spesial circumstances. And in
> >circumstances the alternatives of pumping the data from one of the
> >databases into the other or writing a special program getting
> >candidates from one database and then checking if they are in the
> >normally isn't too scary. If the requirement to use SELECT across
> >databases is more of a regular happening, I'd rather say that
there could
> >be something wrong with your design and that the databases maybe
ought to
> >be merged into one.
> >
> >Basically, I'm just curious as to why you think this is a very
> >feature,
> Just my 2 cents here.
> I personally don't see this feature as very important, but still
good to have.
> First of all, I do understand your point about having a good
> design. It doesn't make sense to have multiple databases that are
used by
> the same application all the time, it's better to have the data all-
> database.

> I will be in a position where such a feature would be helpful,
> in the areas of reports. Let me explain my scenario. There are
> offices and a head office. The stores connect to the regional
> database. The regional offices databases are replicated to the
head office
> at least once a day. The head office wants to ba able to generate
> off of those replicated databases. If the databases are very big,
would it
> be wise to create another bigger database to enclose all the data
of the
> replicated databases in order to generate reports or lookup
> data. Let say each regional offices DB has approx. 2GB of data
each and
> you have 4 offices, then you already have 8GB of data that is
replicated on
> the header offices server. Do you want to another database on that
> that would total close to 8GB in data, making the storage space on
> server grow to 16GB used? I know hard-drives are cheap and 16GB is
> much nowadays for todays hard-drives, but imagine if the regional
> are 20GB each(for the same scenario the server would have to be
able to
> handle 160GB of file storage).
> But, since cross-db queries are not supported at the moment, I'll
have to
> bite the bullet and create a program that merges the data from
> regional offices.
> Have a nice day.
> Daniel Rail
> Senior System Engineer
> ACCRA Group Inc. (
> ACCRA Med Software Inc. (