Fog Creek Software
Discussion Board

Refering data in two seperate schemas/databases

If I have data in database 1 and similar data in database 2

- when the results are produced on a webpage from database 1 - how could I also create a link to relevant data in database 2?

i.e. from database 1 - the link for'Seattle' appears - if I click on Seattle - it will display the record from database 2.

Is there a data linking mechanism that can take care of this?

Wednesday, August 27, 2003

I think we need more information to answer your question.  First, what type of databases are these?  That can make a big difference.

Second, you can link database in oracle using 'CREATE PUBLIC DATABASE LINK <SID>' if I remember correctly.  This will allow you to log into one database, and run queries on another.

Third, this could probably be handled in the application layer, and isn't database dependant.  That would probably be the easiest way to make this work.

But this is all rampant speculation, as you didn't give us enough information.

Andrew Hurst
Wednesday, August 27, 2003

Once launched this application will only be accesable from a web interface.

For example, a user runs a query on the cities where it rains above 50mms per day in January.

On the webpage, the results 'Vancouver, Seattle, Portland' are displayed. Each have a hyperlink. (database 1)

Now if I click on Seattle - it will produce the rainfall history for the month in Seattle.
(database 2)

Now assuming that I do not have control nor access to both databases - but I do know the schema on both databases. How can this be done?

As far as I am aware this should not depend on the make of the database.

And the queries can change.

Wednesday, August 27, 2003


Lets start with the basics:

1. What web technology are you using? PHP? ASP?
2. Do you know how to write SQL?
3. What database technology are you using? Access? MS SQL Server? mySql?


Wednesday, August 27, 2003

This is an example which shows that the database platforms used can make a difference.

I have one client with an application which takes hire car data from their hire car software system, generates statistics and happens to update their sales orders.

The sales orders live in one database, happens to be a set of Foxpro 2.6 tables, and the hire data lives in a Btrieve database (not exactly rocket science in either direction).

I solved it by creating a third database which consisted of largely views of the data, those views were queries using ODBC in the case of the Btrieve, native use of the Fox tables and some housekeeping tables.

In that way I was able to heterogeneously join across the three different databases in order to provide statistical reports and fill the downstream accounts tables.

What made that possible was Visual Foxpro and its ability to  combine external views within a database.  In that sense its quite an odd mix of both client and database platform solution. 

You can achieve similar results in other client or middle ware.  For instance,  using ODBC connections you can get data from multiple sources and use a common syntax of SQL for the most part.  However, using ODBC (or JDBC), also means that you can't use the peculiarities and the strengths of a particular database engine.

The simplest way to marshall that data is to create a shell database to encapsulate it, then once you've built the queries you can use standard linkages, ASP or whatever that the client side developers are comfortable with.

If you have people that are familiar with Visual Foxpro then I'd recommend that, you can have it interface with ASP.  But the same can be achieved with Perl/Python/PHP

Simon Lucy
Thursday, August 28, 2003

*  Recent Topics

*  Fog Creek Home