DB Design - Architecture Question.

All,

Our Application runs on Java and Oracle 9i.
We are planning to have a generic database where a set of tables will
be populated with data from
more than one source (couple of oracle databases and one SQL Server
DB).

Let's say, GENERIC_TBL1 in my genric database will be populated from
different source databases.

Whenever, the source DB is populated we would like to have the
GENERIC_TBL1 in generic schema populated with the data at real time. I
can think of couple of options, we would like to go
for an option, where the effor required is minimal and flexibility of
including more source databases.

1. Oralce data replication ( I am not sure, whether a table can get
inputs from
multiple different dbs (oracle and sqlserver)
2. Using Change data capture
3. Using Oracle Streams
4. Using Oracle queuing technique.
5. Using Java queues.

Would like to know, whether anyone has worked in this kind of
distributed DB environment and the efficient solution for this?

Thanks and Regards
A Ganesh

AnGanesh wrote:
> We are planning to have a generic database
That doesn't sound good...

> Whenever, the source DB is populated we would like to have the
> GENERIC_TBL1 in generic schema populated with the data at real time.
And that sounds like a real time data warehouse.

So here is a good article on generic databases:
http://www.simple-talk.com/opinion/o...ces/bad-carma/

Here is an intro to RTDW:
http://www.dbazine.com/datawarehouse...icles/rittman5

And here is some info about connecting to SQL Server:
http://download-uk.oracle.com/docs/c...544/gencon.htm

--
Michal Kuratczyk

Dear Michal,

Thanks for your mail and the links.Our requirement is the client is
having 3 different systems for each business line and financing module,
he wants to keep a generic application. Data keyed in from individaul
business lines will be moved to this generic application/db and this
application will process this business data and will compute money
flow. We don't have control over all the 3 applications. We are the
owner of one application and this generic system. We have to move the
data from other system as well... I meant a generic database in this
sense. I was thinking of a generic interface, which will populate data
in my generic database and all 3 different systems will give data in
the required format.

Thanks
A Ganesh
Michal Kuratczyk wrote:
> AnGanesh wrote:
> > We are planning to have a generic database
> That doesn't sound good...
>
> > Whenever, the source DB is populated we would like to have the
> > GENERIC_TBL1 in generic schema populated with the data at real time.
> And that sounds like a real time data warehouse.
>
> So here is a good article on generic databases:
> http://www.simple-talk.com/opinion/o...ces/bad-carma/
>
> Here is an intro to RTDW:
> http://www.dbazine.com/datawarehouse...icles/rittman5
>
> And here is some info about connecting to SQL Server:
> http://download-uk.oracle.com/docs/c...544/gencon.htm
>
> --
> Michal Kuratczyk

AnGanesh wrote:
> Dear Michal,
>
> Thanks for your mail and the links.Our requirement is the client is
> having 3 different systems for each business line and financing module,
> he wants to keep a generic application. Data keyed in from individaul
> business lines will be moved to this generic application/db and this
> application will process this business data and will compute money
> flow. We don't have control over all the 3 applications. We are the
> owner of one application and this generic system. We have to move the
> data from other system as well... I meant a generic database in this
> sense. I was thinking of a generic interface, which will populate data
> in my generic database and all 3 different systems will give data in
> the required format.
>

Even senior people with years of database design experience can find
themselves heading down a path of no scalability when trying to
implement generic database designs.

Read Tom Kyte's most recent book and get some ideas of why you don't
want to use generic database designs. You need to use and exploit the
features that oracle provides to create useable, scalable, and high
performing systems.

Stay away from doing a fixed price bid on this system if the generic
database features are not removed from the specifications is my advice.

Look at purchasing a packaged application such as the oracle biz suite
or other financial applications that should be just fine for
interfacing with data fed in from multiple sources.

On 27 Oct 2006, anganesh@gmail.com wrote:
> Dear Michal,
>
> Thanks for your mail and the links.Our requirement is the client is
> having 3 different systems for each business line and financing
> module, he wants to keep a generic application. Data keyed in from
> individaul business lines will be moved to this generic application/db
> and this application will process this business data and will compute
> money flow. We don't have control over all the 3 applications. We are
> the owner of one application and this generic system. We have to move
> the data from other system as well... I meant a generic database in
> this sense. I was thinking of a generic interface, which will
> populate data in my generic database and all 3 different systems will
> give data in the required format.

You can have a generic interface to all your databases, but that is
where your generic solution should end. Choose the stored code solution
and let the parameters and returning resultsets be your generic
interface. Then, each flavor of database and SQL for each database can
be in charge of delivering the contract of the interface.

With this in place, your application should be able to be one design.

--
Galen Boyer