Database design for database-agnostic applications Database design for database-agnostic applications sql sql

Database design for database-agnostic applications


The short answer is to stick to features that are standardly, or close to standardly implemented. What this means in more detail is:

  • Avoid anything that uses the database's procedural language (stored procedures or triggers) since this is where the huge differences between the systems come in. You may need to use them to emulate some features, but don't use them to create your own functionality.

  • Separate auto-increment fields' sequences from the fields themselves. This will look a bit forced for MSSQL but will implement cleanly in Oracle, DB/2 etc without needing any emulation fixes.

  • Keep char and varchar fields below the smallest maximum size for the set of engines you're aiming at.

  • When you're writing queries use full JOIN syntax, and bracket the JOINs so that each join is between a single table and bracketed expression.

  • Keep date handling logic in the code, not the queries, since a lot of the date functions are outside the standard. (For example: if you want to get stuff for the past two weeks calculate the date two weeks ago in code and use that in the query.)

Beyond that the effort involved shouldn't be too intimidating, so it may well be worth it.


If I were you, I'd think hard about the return on your investment here.

It always sounds like a great idea to be able to hook up to any back end or to change back ends whenever you like, but this very rarely happens in The Real World in my experience.

It might turn out that you may cover 95% of your potential customers by supporting just Oracle & SQL Server (or MySQL & SQL Server, or... etc.).

Do your research before going any further, and good luck!


I currently support Oracle, MySQL, and SQLite. And to be honest it's tough.Some recommendations would be:

  • avoid stored procedures, but you may need them to emulate missing features on some platform (see below)
  • learn how to use triggers, because you'll need them to emulate missing features (for example with Oracle you don't have auto-increment, so you need to emulate it, and a good choice is with triggers)
  • have a decent test environment because you'll need to test lots of SQL before knowing for sure that it's doing what you wan on all your platforms.

Is it worth it... well depends. Commercially it is worth it for enterprise level applications, but for a blog or say a website you might as well stick with one platform if you can.