How to develop a web application compatible with multiple database management systems

Question

How do you design and manage the development of a web application that should be compatible with multiple database management system such as Oracle and MS SQL Server?

If you can't use ORM like NHibernate or EF, how do you maintain database schemas during the development?

My approach now is to have a development database on SQL Server and to port it to Oracle (with a tool) just before releasing a test patch, to test the software on both rdbms. (The tool also generates a file used by the application to upgrade the database)

Is it a good approach? What about a database project of Visual Studio, could it be a better way to keep my db schema?

EDIT: This question is not about designing the architecture of the application (I have already an abstract data access layer), but how to maintain database schemas for different kinds of rdbms during the development.

Answer 1

Realistically the only way to handle this is to decouple your database access from you core application so that you can customize the access code for each database. In other words, exactly what Nhibernate and EF do. If you cannot use one of these tools you will still effective end up writing one any way. Which can be fun and interesting, but will also take up a large amount of time. So I'd be asking some serious questions about why you cannot use an ORM.

Answer 2

Model-driven architecture (MDA): use a generic Database modelling tool to design your database schema. You define the tables/relationships/primary keys/etc. in a generic fashion and then have the designer generate the necessary SQL script (most support output to a variety of databases). As you change the DB model, the tool will generate the necessary SQL code to update the database, or generate it from scratch. The tools also assist in generating documentation and assisting with database versioning, amongst many other things...

I use Context Database Designer and am exceptionally happy with the tool and price. Enterprise Architect also looks like an excellent tool, with the ability generate and reverse-engineer code.

Answer 3

I think the key to this is to make sure that you stick to standard SQL syntax. MS SQL Server ships with Transact SQL (T-SQL) which is a super-set of ISO standard SQL, meaning that it has extra syntax which is not officially part of standard SQL.

SQLZoo is a good site that allows you to compare the syntax support of different databases. You will find that most of the syntax that you use from day to day will be the same between most DBs, but there are a few quirks out there. The best way to find them is to check each of your queries in each environment and then check them into source control.

Your idea to use a database project is a good one. It will allow you to deploy your changes to multiple databases quickly and test them automatically.