My boss asks me to create only ANSI SQL to really make it database independent. However I learned that it's not too simple as no database fully ANSI SQL compatible. SQL code can rarely be ported between database systems without modifications.

I saw people do different method to make their program database independent. For instance:

  1. Externalize SQL claims to resource files.
  2. Write many companies class to aid different database.
  3. Write only simple SQL, and ward off from advance functions/joins.

Would you always write your code "any database ready"? Or get it done only when needed? If so, how can you achieve it?

You could utilize one of the numerous Object/Relational Mapper tools, like Hibernate/NHibernate, LLBLGen, and so on. That may enable you to get a lengthy method to database portability. Regardless of what you need to do, you must have some abstraction layer involving the model and also the relaxation of the code. This does not mean you'll need some kind of dependency injection infrastructure, but good OO design can enable you to get pretty far. Also, adhering with plain SQL and convinced that will enable you to get portability is quite naive. That might be true in case your application was trivial and just used very trivial queries.

For always writing a credit card applicatoin to become "any database ready," It's my job to apply certain kind of abstraction layer so it's easy to maneuver in one database system to a different. However, in lots of conditions, this isn't needed, you're developing for that Oracle platform or SQL Server or MySQL whatever which means you should not sacrifice the advantages of your selected RDBMS only for the potential of a completely seamless transition. Nonetheless, should you develop a good abstraction layer, even focusing on a particular RDBMS will not always be too hard emigrate to another RDBMS.

To decouple the database engine out of your application, make use of a database abstraction layer (also data access layer, or DAL). You did not mention what language you utilize, but you will find good database abstraction libraries for the major languages.

However, by staying away from database-specific optimizations you'll be passing up on the benefits of your unique brand. It's my job to abstract what's possible and employ what's available. Altering database engines is really a major decision and does not happen frequently, and it is best to make use of the tools available for you towards the max.

Inform your boss in your thoughts their own business. No, obviously one can't say may be to a person's boss, but stay updated.

What's interesting is exactly what business value should be based on this requirement. One apparent candidate appears to become the database code should be prepared for focusing on other database engines compared to current. In the event that's the situation then that is what ought to be mentioned within the requirement.

After that the choice is yours being an engineer to determine the various ways to accomplish this. One may be writing ANSI SQL. One may be utilizing a database abstraction layer.

Further it is your responsibility to see your manager exactly what the costs from the different options are (when it comes to performance, speed of development, etcetera).

"Write ANSI SQL"... gah!

Only for the record. There's an identical question here on Stackoverflow:

Database design for database-agnostic programs