May 22, 2002
Solving Problems by Displacement
This is an interesting technique I learnt a long time ago. So long, in fact, I can't remember when I picked it up. It goes something along the lines of, when trying to solve a problem, don't pick at it, go and do something else. What then happens is that your self conscious goes to work and before you know it a solution can present itself to you almost fully formed. At the very least you come back to your problem a little more objectively and are able to tell the wood from the trees or look at it a little differently and maybe a fresh angle is all you need.
After my last post I headed for the local swimming pool and followed the black line for a while. During one of my laps I found myself thinking that maybe I was asking the wrong question. And I was. The question was not "How do I emulate NVL like functionality in databases that don't support it?"It should have been "Where do I enforce these kind of rules?" The answer, of course, is not in the database layer, but in the application (or business object) layer we build on top of the database layer.
In my years spent developing Oracle solutions I have always enforced as many business rules in the database as possible. This is because the vast majority of applications I have worked with have been developed in tools like Oracle Developer where the developer writes lots of SQL. But in this case that won't be true. Anyone writing code for the application will go through the object model which I craft on top of the database, because all of the code will be in Python and frankly its much easier to write a bit of Python than try and figure out my relational data model and write some SQL.
This isn't foolproof, but its certainly good enough. The result of this is that my object layer will worry about complex features of attributes, and the database classes will only have to worry about persisting the information in the right data type. So my new code for the insert I mentioned in my previous post, and which will work with Oracle, MySQL and Gadfly, will be
INSERT INTO stock_prices
( symbol, price_date, price )
VALUES
( '%s', '%s', %0.3f )
Posted by Andy Todd at May 22, 2002 03:28 PM