Are you SURE you've abstracted your database access?

Here is a simple test to see if your db is really abstracted by your DAO. Rename every single field in one of your tables, make those changes in the DAO, (but nowhere else) and see if all of your forms, validations, transformations, calculations, business rules and display code still works perfectly (custom as well as auto generated code). It doesn't? Then I guess we need to spend a little more time thinking about database abstraction.

Is it worth it?
Firstly, as you are starting to see, database abstraction really isn't that easy. Typically most applications are extremely tightly coupled to the names and the structure of the database fields. So the first question must be whether it is worth the trouble of abstracting database access at all?

The answer to that is (as always) "it depends". But on what? Well, if you’re new to OO programming, this probably isn't a concept to worry about just yet. Equally, if you have a fairly simple application or one where the field names are unlikely to change often, true database abstraction is probably more trouble than it is worth.

If however you're building a highly configurable application (my applications must allow non-programmers to be able to change the data structure of the database without breaking the code), it is a necessity.

Is it Really that Hard?
Hey, how hard could this really be?! Stick a bunch of aliases into the DAO and you’re done, right? Well, you certainly can use aliases in the DAO to solve the "changing field name" problem (which to be honest is fairly bogus – it doesn't happen ALL that often). But that still assumes a 1::1 correlation between fields and attributes. Where things get tricky is when you’re using calculated attributes in your business objects that don't match exactly to your database fields.

To take a simple example, what happens if you have a date of birth field but want to display an age attribute? No big deal, a custom User.getAge() method will take care of you. But now what happens when you want to display a list of users and have a clickable title allowing you to order by age (without any manual programming)?!

Well, first, you better be using an iterating business object otherwise you won't even be able to display a list of ages without sticking some script in your service layer or all of your view code to calculate ages. But how does your application know that to order by Age your need to order by DateofBirth DESC? That is actually a fairly simple problem to solve as it is just an inverse function so you could declaratively set Age.Order = "DateofBirth DESC" in your DAO and a smart script in your base DAO to avoid putting double DESCs would see you through. The problem becomes a little more acute if you want to display by ChargeablePrice where the chargeable price uses complex logic performed on multiple price fields to determine the chargeable price for a given item.

Of course, the easiest solution is to push all of this back on to the database. It is also the most performant solution and in some ways the simplest approach. Simply create a view for each object with the attributes pre-calculated and your views are taken care of. However, this still requires some carefully handing as your DAO will have to know enough about the underlying field names to handle the inserts, updates and deletes appropriately.

However, the database approach is not optimal. It tends to tie you very tightly to a specific database platform, makes it much more difficult to work with other persistence stores, and typically db IDEs are less sophisticated than those for programming languages. You can keep SQL in version control by storing your .sql within your subversion (or CVS or – shiver – VSS) repository, but it isn’t a great solution and if you have a separate IT/DBA person/group it becomes the project from hell trying to deal with them every single time you want to make a change to the object model. On the bright side, it means the object model and business rules can more easily be reused across platforms (a CF and a .NET app) without the performance hit and complexity of having to create an underlying model web service abstracting database access for all of your applications.

If you are not going the database route, the only other option is to have a set of optimized sorting libraries so that you can make the iterating business object iterate by the calculated value of any attribute. An interesting technical challenge (to do efficiently) and one that I’m working on now. If anyone has any great ideas, now would be the time to suggest them in the comments (please!). Right now I am thinking about looping through the object to create a list or array of the actual values of a given calculated attribute against their numerical position in the internal data representation and then some kind of algorithm to order by those values and to replace them with a new set of keys (1 for first, 2 for second and so on) which could allow you to easily iterate using those key values.

But it has been a while since I took CompSci classes, and I slept through most of them (:->), so anyone with ideas or who'd even be willing to help play with this would be much appreciated. Let me know! I would even be willing to pay a modest bounty as well as to share the credit to anyone willing to help out or write the routine. Anyone interested just comment below or email to pbell at systemsforge dot com. Anything contributed will be Apache open sourced along with all of the other code I release.