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.

Best approach to create a real multi-database application

I was wondering what is the best approach to write an application to support several databases, just like FogBugz does. I am developing an e-learning web application based on Asp.Net and one of the functionalities I would like it to be able to store data in Sql Server, Access, MySql and eventually Oracle.

I've been very interested in the Provider Pattern, specially after Microsoft implemented it in several of the subsystems of Framework 2.0. I plan to develop an abstract class that defines the basic API, and then develop a custom implementation for each database engine as a custom class.

I believe this approach is very straighforward, since it will enable the customer to simply select the database of choice by changing a value in the web config file.

Is there any better approach than that? Any help would be really appreciated.
Humberto Oliveira Send private email
Friday, November 18, 2005
The provider pattern is a good solution. In Java you would code against an O/R mapping framework - which implements the provider pattern itself. Depending on runtime configuration, the framework would instantiate one SQL dialect provider or the other. Such frameworks are hibernate, toplink, etc. I don't know how things work in .net, but I suppose you could use nhibernate - the .net port of hibernate. If it works as well as the java version does, you're set.

Emil Kirschner
Friday, November 18, 2005
Funny, I thought this was the problem SQL was created to solve.
David O Send private email
Friday, November 18, 2005

Unfortunatly we can't ensure that the same SQL statement works in diferent databases whithout minor changes. In addition to this, I might use data sources that are not accessed by SQL, such as XML files or the Active Directory.

For instance, one of the modules of this system I am developing controls the logins and passwords. Most companies use Active Directory to hold these kind of data and are not willing to replicate it in another database.

By using the Provider pattern I can easily create a custom class that implements the base methods, but this time reading and validating the data on ActiveDirectory insteade of a database.
Humberto Oliveira Send private email
Friday, November 18, 2005
David, I think you are right, in theory. However I think software companies desire to add new features outstrips the pace of standards organisations. So in the end they just do their own thing will little regard for standards.
Friday, November 18, 2005
RemObjects DataAbstract may be part of what you're looking for.  An RO/DA middleware server works well either as a layer behind your web app or as a server that is connected to directly by Win32 or .NET Winforms apps.
Herbert Sitz Send private email
Monday, November 21, 2005
We've successful to use standard SQL with DAO pattern to main a large multi-database application.

Of course using ORM tool may be simipler, but just SQL can be also straight forward
Carfield Yim Send private email
Monday, November 21, 2005
Here is a good article for you from redgate.
Tuesday, November 22, 2005

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

Other recent topics Other recent topics
Powered by FogBugz