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.

Database "Agnostic" application vs. Sticking with One

I don't mean to post too many topics.  But while discussing the issue I just posted prompted me to inquire about this.

What are your thoughts about writing applications which support a handful of databases (e.g. Oracle, MySql, SQL Server) vs. just sticking with one?

We write an application which talks to all three of the above.  The problem is that we tend to stick most of the business rules in the client rather than on the database server.  It would be too difficult to code the logic in triggers for all three.  While it has its benefits (larger market), it certainly has its disadvantages.  Especially in this age where soon it will be expected that people should be able to access their systems on their phone (yet another client app to write).

I would like to hear other people's opinion about this.
Richard Gardner Send private email
Wednesday, July 18, 2007
I often think that triggers are the DB vendors way of solving the 'problem' that SQL lets you swap DBs too easily!
I haven't done much advanced DB suff but even with just simple CRUD there are enough differences in the SQL to make supporting multiple DBs a pain.
Martin Send private email
Wednesday, July 18, 2007
We debated this at our user group meeting the other day.

Sometimes the SQL engine is very, very efficient at certain tasks.  Rock solid, too.  Don't completely discount the database out of adherence to a neutrality principle.  Besides, switching DBMS is rare (though it does happen).

Our shop enforces as many rules as possible in the DB through the use of constraints.  Our triggers are very lightweight and few.  Validations are usually replicated in the client for a better UI (meaningful messages and behaviors).

People tend to be passionate about this topic.  I wouldn't be surprised if a flame war started on this thread.
Wednesday, July 18, 2007
There are strong opinions about putting logic in the app vs the database and which database can get fairly religous.
But supporting or at least testing on multiple DBs can be a very good way of uncovering hidden assumptions about the behaviour that have a habit of coming back at you in the future.
Martin Send private email
Wednesday, July 18, 2007
This needs to be a buisness decision. If your intended customer base already have a variety of DBs that they will insist on using, then you need to be compatible with what they have. This is less money for the customer (they can use the DB license they already have instead of getting a new one), but more work for you (you have to code and test against multiple DBs, and possibly multiple versions).

If, on the other hand, the customers are going to buy the entire system from you (DB and all) then you should pick one and go with it. The customer isn't likely to care, and you'll save a lot of engineering effort that can be spent on the rest of the app.

And of course, depending on the application requirements, some databases may not be suitable at all, you wouldn't support those.

What's your market? From that follows the decision.
Michael Kohne Send private email
Wednesday, July 18, 2007
Abstract the database interface away and devise your own Universal SQL that gets translated into specific SQL implementation languages via plug-in templates and state transition tables.

You'll have to dumb things down a bit to a subset of all RDBMs' capabilities and perhaps forego stored procedures.  Might need to store all data types as Unicode strings wrapped as BLOBs.
Wednesday, July 18, 2007
If you sell your product, and it's intended to use a database platform that the customer provides, then there's a real advantage to being able to run on a variety of servers.

If you're writing an internal app, and you're trying to future-proof the app because you might change databases "someday", the benefit is much less clear.

It probably still makes sense to avoid triggers and stored procs in places where you don't get a clear benefit from them.  Just be aware that you can burn a lot of real time preparing for a change that may never come.  If you've got a place where you need a trigger or a stored proc, use it.  As long as this is limited in scale, it probably won't be a big migration issue if you ever need to take on a platform change.

The broader topic of "business rules in code vs. business rules in the DB" is probably worthy of its own topic, but I think you'll find more support for business rules outside the DB -- probably in code or a rules engine.
D. Lambert Send private email
Wednesday, July 18, 2007
"Database "Agnostic" = Lowest common denominator...

You cannot use the advanced features of any dbms, and instead of implementing most of the stuff in the db, you do it in the client or middle tier (if you have one).

Sometimes you have to do it. but I would prefer to choose a dbms (and maybe a specific version).
Totally Agreeing
Wednesday, July 18, 2007
It really depends on what your target sale is.

Are you selling the application or is it internal? If it's internal, do away with agnostic ideas and just code to a specific DB vendor.

Are you hosting your application or will a customer deploy it on their hardware? If you host it, again you can just do away with multiple DB vendors and specifically code.

If you expect a customer to deploy it on a system, it's easiest and best for the business if you abstract the database away and make plugins for each database type as the request comes up. For very little work you can expand the number of databases you use and remove barriers to customers buying your product.

Wednesday, July 18, 2007
I've written my own database abstraction libraries and my experience tells me that it's quite difficult to create the perfect abstraction which will work on all situations, and that several layers of database abstractions might be a better goal, even though the most useful one will be used more often, but as long as it used other layers, it reuses code and helps develop and test them.

I don't believe one-size-fits-all is useful enough in database abstraction, but depending on the use, things can work great no matter how terrible the abstraction is.

For example, by trying to totally abstract away the SQL, your use of the database abstractions tends to be more coarse-grained, and your code might overload much more the database, the network, and even the processing of the returned rows. For instance, there are times you might not want all the columns of the tables, or that you really want to optimize things a bit more by performing custom joins whenever you can... But by working through an abstraction, you might just forget that a lot of work is being done behind the scenes which could be much more fined tunned for general performance than it is...

On the other hand, database abstractions can be useful in creating a layer of security and a common interface to multiple different databases. For instance, there are several different ways of limiting the returned rows to just the few that you are interested in at the time. Many times we just don't care if the database is Oracle, MySQL, PostgreSQL, SQL Server, we just want those 20 paginated rows from them by using a common and optimized interface. If your database abstraction gives you enough support to such easy abstractions, it should payoff to use it in multiple related projects, because it saves you from creating your own custom database abstraction as well.

But, each database has its own custom features which are unique at times, like custom data types or data types that work some unique ways, custom SQL commands, and so on. Also, folks who have standardized on a single SQL database like Oracle and SQL Server might just care about their selections and nothing else, so they just won't understand your excuses for not adopting their databases as they use them, in every little feature.

All of this is largely known though. But as we move to Web services, some of the metadata that might be present in the database might be useful in the layers above the database, because the Internet is a very insecure medium, so you don't want custom SQL hitting your database without having the right checks in place. That is, layers above the database are becoming more important as time passes, and they need more and more of the metadata that could be present in the database. The easiest way to have the metadata is to code it in the abstraction layer above the database, so it's fully available to other abstraction layers above this layer which should be responsible for other kinds of checks and forms of queries.

It's about redundancy as well. Can you afford to have redundant checks and code in the database, in the layer above the database, and in the user interfaces? If not, then you need to embed some smarts in all of these processings and reuse the metadata whenever you can and things like that. For instance, data validation in Web forms. How to keep it in sync with the rules present in the database? And so on...

On one hand, to adopt a single database engine is cool whenever you can, but on the other hand, realities and usages are a lot more cruel, which makes the use of database abstractions almost unavoidable. Choose your poison, as they say.
Joao Pedrosa
Wednesday, July 18, 2007
The answer is--it depends.


Let us know more about what you're trying to optimize for. To have one client on top of three db's, you are indeed going to have to do the LCD. That may be a good tradeoff, maybe not.
Steve Hirsch Send private email
Wednesday, July 18, 2007
As a general rule, I try to adhere to the most accepted standards. For databases that would come down to using standard sql and nothing else. I try to design systems such that they use only trivial db features. I know that overtime things will end up anything but trivial, so starting simple is probably a good idea.

Secondly I always have an integration layer between the app server and the db server. That is a good idea for at least reasons:
1) I can refactor the everything behind the DAL interfaces without destabilizing the rest of the application. ie I can rewrite the whole db if, trivial or not, the existent model doesn't work correctly.

2) I can have multiple dal implementations, one for each specific db (MySQL, Oracle, etc).
Dino Send private email
Wednesday, July 18, 2007
Design for standard SQL and multiple databases at first. When actual performance profiling indicates that use of a database specific feature is actually necessary, deal with it then.

If changing your architecture is too hideously complicated then you'll be screwed the first time you get a feature request that requires the change anyway.

Unless, of course, you have feature requirements that you know will require non portable database features, in which case the specific database (and sincere prayer that the vendor doesn't break what you rely on) is obviously necessary.

If your feature set requires a database that supports replication or real-time redundant failover or whatever advanced features, then you're already limited to just the set of databases that support it, and should abstract the implementation details so that the main application doesn't need to worry about how the database does it.

Wednesday, July 18, 2007
I have written a framework for developing CRUD applications for the web, and I cannot dictate to my users which RDBMS to use, so I currently support MySQL, PostgreSQL and Oracle.

My framework was designed around the 3 Tier Architecture with separate layers for Presentation (UI), Business rules and Data Access. I have a separate Data Access Object (DAO) for each DBMS that I support, so each DAO handles the specifics of a particular DBMS.

All business rules are handled within the Business layer,  and I never use database triggers, stored procedures or database constraints. As far as I am concerned the DBMS is just a data store, not a rules engine.

Being limited to standard SQL is not a problem. Where different engines require a different implementation for something, such as auto-generated primary keys, each implementation is isolated in its particular DAO.

Switching from one DBMS to another is as simple as changing one line of code. The Presentation layer is unaffected, the Busiess layer is unaffected, it is only the DAO which needs to know the identity of the DBMS engine.

Being able to support multiple databases increases my options without imposing any significant limitations, so for me it is a very good idea.
Tony Marston Send private email
Thursday, July 19, 2007
I've been more in the Enterprise Integration area than building single apps. I try very hard to put nothing in the database but data.

We had great success with a vendor framework that put an effective CRUD interface over a variety of database products, MQ-Series and in Web Services (future release, R only I think). Their approach is code generation. You build a common model and the code generator spits out UDB code, Oracle code, MQ code, etc.

I see the database enforcing business rules or providing business logic as a holdover from VB and PowerBuilder days, when unpredictable and untrusted clients might connect to the DB. Then the db was the best enforcer and unit of reuse. Nowadays those clients connect to the app server and I never tell them I even have a database. :-)
Stan James Send private email
Thursday, July 19, 2007
"Might need to store all data types as Unicode strings wrapped as BLOBs."

Oh yeah. The performance of this application is going to rock! Not to mention all the code you're going to write just to sanitize the data...
Thursday, July 19, 2007
My team is building an n-tier distributed app.  Our tiers are DB-SP-DAL-ORM-BL-Service Layer-UI.  We elected to use SP and a DAL to allow for potential database neutrality.  We can swap out DB+SP+DAL to change DB vendors.  We can swap just the SP if the DB structure changes.  The DAL takes care of the various connection string issues, as well as the vendor-specific details of data set creation.  We happen to use C#.  Once we convert DB-vendor specific data into the common MS data set, we are then DB agnostic and can proceed with "common" code from ORM the rest of the way out to UI.

I must strongly disagree with the advice to "wrap uni code in BLOB objects."  I find that ideas like this are often proposed by developers without a strong understanding of what benefits can be realized from a properly designed database structure.  The database is NOT "just a place to persist objects."  The database is about data integrity.  A good database design will save your butt multiple times over the life of a project, just as a poor design will cost you time, money, and will quickly eradicate any savings you thought you'd get with a non-standard roll-your-own solution once the change requests start coming in.

My advice:  Consider the database design and implementation with the same care that you would any other component.  If you implement your application tiers properly, you can make DB vendor decisions with minimal impact. 

Thus, the question is not, "Should I be agnostic."  The question is, "If I decide to be agnostic later on, how much is it going to cost me in terms of investment before I can release a version of my product on the new DB?"  If you come up with an answer that sounds like, "It should be relatively compartmentalized, because we designed our tiers knowing we might do just that" you're fine.  If it scares you because business logic is scattered all up and down the tiers, you don't know what "tiers" are, your whole world is built on some edge case feature of one vendor's DB, or you have vendor-specific SQL in (God Forbid) your UI layer, the right move might be to get a more experienced architect.  And a DBA.
Charles Send private email
Saturday, July 21, 2007
Don't listen to t.i.c.
cbmc64 Send private email
Monday, July 23, 2007

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

Other recent topics Other recent topics
Powered by FogBugz