Re: The fine art of database programming

[pdcawley]

My top tip for database programming is don't do it until you absolutely have to. Hopefully by then you'll have a good idea of the 'shape' of your data and you'll end up with a reasonably stable database Schema.

Despite what gmax says about not being afraid of writing raw SQL and using DBI directly I'd still say that you should try and route all your database access through one or two modules (either tools from CPAN, or home rolled) as a way of limiting your dependencies and avoiding the problem of a single change in the database leading to the Death of a thousand cuts as you trawl your codebase hunting down everything that deals with the affected tables.

The 'performance hit' you'll see as a result of doing this will be nothing compared to increase in 'cost of change' you see when scatter SQL throughout your code like an incontinent puppy. (Guess who's had to work with code like that recently. It's painful.) And then there's testability. When you access the database only through your 'mediator' modules, it becomes possible to drop in a replacement that only pretends to talk to a 'real' database. This can be a remarkably powerful testing technique...

[gmax]

don't do it until you absolutely have to

Especially if you don't feel confident about database programming. ;)

update. You don't decide to use a database for your own pleasure, but because the application requires it. A recent node by ChOas explained very well that good programming is data analysis in the first place. If your analysis calls for a database backend, then you must implement it.



you should try and route all your database access through one or two modules (either tools from CPAN, or home rolled) as a way of limiting your dependencies

Using the DBI doesn't mean that you cant program in a modular way. My point is that if you are skilled enough, you should call the database without intermediate modules.
You can accomodate your programming logic inside one or more modules. By "programming logic" I mean what your application should do, not some set of rules to make the database interface more user friendly. You can build your logic either using the DBI directly or through some wrappers. I prefer the direct way, but it doesn't mean that using the DBI directly will turn my scripts into spaghetti code.


 _  _ _  _  
(_|| | |(_|><
 _|   

[ignatz]

> don't do it until you absolutely have to

I disagree with this. When designing a database application the structure and relationship of the data is a fundamental. Making it a development afterthought can lead to some big time structural problems as you get hit with new demands on data tooled for code with completly different goals. This would be a lot like advising someone new to object oriented programming not to worry about the relationship between the objects until after they've got all of functionality of the methods worked out so that they can get a clearer picture of how they need to work together.

It's usually not hard to tell applications that have been designed in this manner: Flatfilesque table designs. Lot's of duplication of data to meet specific code needs. No abstraction in the data models. Most of the time you can tell the difficulty of refactoring an application simply by looking at its database schema.

When designing any database application, the first question I ask is what is the information that I need and what is the bast way that I can store it? Once I have those relational models in place things like Classes and their relationships just seem to flow out of it. This approach may be slower in terms of initial development, but you'll avoid a lot of major headaches down the road. It's a lot easier to fix bad code with good data than it is to fix bad data with good code.

People interested in database design should check out the writings of Ralph Kimball.

()-()
 \"/
  `                                                     

[pdcawley]

I think you might be misunderstanding me. I'm not saying that, when the time comes that you absolutely can't avoid using a database, you should slap it on the side and hope. Codd's rules for normalization and all jazz are just as important if you add a database late in the day as they are if you're going down the 'Big Design Up Front' route.

This is just the XP "You Aren't Gonna Need It" principle in action. Until the code tells me I need a database I won't worry about it. Once I know one is needed I'll take the time to make sure it's well factored and well normalized because that's the only way to keep my cost of change down.

The problem I have with RDBMSes is that the Relational model doesn't really map onto objects and back again that well; What you want is some kind of general solution that you can chuck an arbitrary object at and have it automagically stored, giving you back a magic cookie that you can use to retrieve it later. Given careful design it's possible to work things so that the day to day running of your code doesn't require complex queries (because complex queries are *nasty* with the kind of database that'll stash arbitrary objects).

So, the approach I'm taking with the code I'm working at the moment is to have two different databases. One object database (not yet written, we don't actually need it yet) that doesn't mess around trying to do cunning queries or relational magic, which will probably use a combination of BerkeleyDB and Storable, and a reporting/logging database which will be properly relational, allowing for the kind of reporting and queries that are needed for invoice generation, user reporting and all that jazz.

Speaking of which, the customer is pushing that to the front of the story queue, time to sit down with a bunch of index cards, a handy whiteboard, and the office's token big iron database programmer.