The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Application with multiple databases

I have to develop an application with multiple databases. According to the requirement the application should authenticate using an existing application database, majority of the new application data is present in another application's database. I have to use web service to retrieve the data from the database. But all the new data related to this new application should be kept in a new database created for this application. please give me your suggestion for this problem.
williamdiz Send private email
Wednesday, September 24, 2008
What problem? You don't know how to access multiple databases?
Wednesday, September 24, 2008
Why go to all the work to pull it out of a database and put it in another?

The easiest way would be to create a view in the source database that captures just the data you want.

Then set up a table link in the target database that uses the aforementioned view as a data source.  Then just use a SELECT...INSERT to pump the data into your target tables.
Walter Mitty Send private email
Wednesday, September 24, 2008
Dare I ask why this requirement exists in the first place?

If either of your database servers goes down your app goes down. Why double your risk this way?
Wednesday, September 24, 2008
I would avoid services and just set up multiple connection strings for each of the database you are connecting to. Not complicated. You could also set up one master table in one database that stores all the references and connections to the others. This scales. You could then build a say a web admin tool to allow them to manage and add new databases as they go online, but the main database holds all those connections in a table. You can also build dynamic SQL in a stored procedure that pulls or updates data across databases if in the same instance, or that are linked. Ive done this before in Dynamics. The good things about having a master db table to manage this is you can later build a tool and error checking piece that will help you determine failed connections or datbases, should one or more go down. That doesnt take your whole system down with it. Also, I would store security as well in this master database. That way, the only dependency in the whole system is the one database....not the many.

I dont like allot of the web applications that come out of Microsoft that has all these tangled couplings of tables and services and systems. If one fails the whole house of cards comes down. Ive found if you take out the web service dependecies, COM+, third party plugins, and other junk, a multi-database system like this runs much better. I say keep it centralized, standardized and most of all, simple!
Wednesday, September 24, 2008
Thanks for your replies. The databases for this application is sql server 2000. My idea is to create linked servers in the new database and access data present in the other two existig databases. I hope that linked databses does not have much performance issues. Also I wonder how can I estiblish the forien key relationship in the newly created database for the primary key values present in the existing database. I feel that with out this foriegn key relationship there is a chance that the newly created database contains inconsistent data due to deletion/modification of primary key values from the existing database. hope you understand my concern.
williamdiz Send private email
Thursday, September 25, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz