Ian Bicking: the old part of his blog


Dumbing down the database doesn't protect it from anything but a lack of willingness to learn SQL.
The smart/dumb database argument is a perennial one amongst a certain type of OO purist prevalent in Java circles. I tend to the "smart database" end of this debate - This is my take on this argument:

Typically the "intrinsic" logic in the database concerns enforcing cardinality and conditional relationships (child record can only exist when the parent record has values a, b or c in field X). Think in terms of data-related validations that an application would otherwise have to check for (e.g. this date must be after that one). Declarative constraints can get you quite a long way; use triggers as it makes sense to do so for more complex validations. This is much a matter of taste as anything else, but there are things that triggers do really well.

The smarter the database the more that applications can assume about the data (and therefore they are simpler). Note the plural "applications" here - this is never a 1:1 relationship if the database contains important information. Moving intelligence to the database allows it to be reused between applications. Databases are a mature technology today and tend to suck a lot less (and therefore turn over less frequently) than application development technologies *Cough*J2EE*Cough*.

The python language and DB-API provide fairly powerful features for manipulating data as data. Python's type system fits nicely with database work through pythonic DB-API interfaces [For a quickie PDF report generator CGI try putting a lightweight generator (to do rollups) over a large sorted query result and pretty-print the output to LaTeX].

One of the arguments in favour of "model object" architectures is that business logic all lives in the same place (ABC costing models in Java anyone?). There is some merit in this argument but it needs to be implemented in a fairly purist way to achieve this goal. Doing this tends to bring in a lot of infrastructural baggage like CMP entity beans to the architecture of a business application. Hard to tune, hard to troubleshoot, "magic" stuff happening behind the scenes that nobody understands.

I'm not convinced that a complex object mapping layer (try implementing one that supports labelled M:M relationships, transactional units of work and optimistic concurrency) adds much value to Python. Database work in C++ or java is clumsy due to the relatively low level nature of the interfaces and impedance mismatch of the type systems. Python lets you manipulate data with relatively high level constructs so there is not so much saved effort as there is with Java. IIRC cx_Oracle 4.0 now supports XA transactions.

More importantly, placing all your validation in the middle-tier locks you into J2EE, .NET, COBOL or whatever for your application architecture happens to be today. Placing intelligence into the database makes some steps to decoupling the applications from the data. See http://www.microfocus.com for a look into the market for tools to maintain legacy COBOL applications.

Appropriate use of not-null fields, default values, referential and check constraints goes quite a long way to keeping the data relatively clean. Consider using database triggers to encapsulate more complex validation on important tables. Always use triggers or materialised views (where available) if you need to denormalise data in a transactional application. This way the de-normalisation is inherent in the schema and the database never forgets it.

Back in 4GL days it was common to have insert, update and delete sprocs for every table in a database; data level validation and business rules were implemented in these procedures. They provided a single place to look for business logic; the principal argument in favour of purely middle-tier business logic. More than one way to skin that particular cat. I've seen databases in this style moved from Oracle forms to J2EE applications with little modification.

There's a whole essay in this particular topic (and many have been written) which I don't have time to write at the moment. Take this as food for thought from someone who's designed a big database or two in their day.
Comment on Persistent Persistence
by Nigel Campbell