Re: [SQLObject] two postgres questions

[Edmund L. <el...@in...>]

Bud P.Bruegger wrote:

> I have been thinking about constraints etc a bit and my impression is
> so far that this is better dealth with in python objects than in the
> dbms.  I see some advantages by doing it in Python:
> 
> * the app programmer doesn't need to worry about SQL.  [The difficulty
>   here is particularly the marshalling that separates the two worlds
>   that either the app programmer or the backend programmer has to deal
>   with]
> 
> * it is more portable across dmbs (particularly those that don't do
>   constraints well)
> 
> * the backend developer has to worry about less things
> 
> * SQL constraints may not be expressive enough to do full consistency
>   checking, so you need python code in any case.

Constraints are essential for data integrity, but they are often 
incomplete or poorly utilized--because good data modellers are hard to 
find, and because RDBMS theory is poorly taught/learnt in schools.

A good data model uses extensive constraints to ensure that it is never 
possible to create invalid combinations of data in the DB via invalid 
insertions, deletions or modifications. But where to add them?

To my mind, the constraints should really be in the DB, because it is 
the DB that should enforce the constraints. The problem is that it is 
then very hard to port the data model to a different DB. On the other 
hand, other applications (in possibly other languages) that use the data 
  will be subject to the same checks and constraints.

To improve portability, one can move the constraints to some middleware 
layer. But now, you have more complexity, and potentially just as many 
portability issues (does the middleware layer run on my platform? Can I 
call it from my chosen language? etc.) If you write your own middleware, 
you have more APIs/code to maintain, etc. And, writing a decent 
transaction processing layer is non-trivial (because if we care enough 
to use constraints, we care about having robust transactions).

I've seen these issues again and again. I've chosen, in my latest work, 
to forgo a third-party TP layer and just put the constraints in the DB. 
This way, I don't have to deal with API and cross language issues since 
the data is accessed from Python, VB, Access, Crystal Reports, etc.

A middleware layer would not have worked in this situation, since things 
like Crystal Reports and Access would not have interfaced to it.

But what if the DB doesn't support constraints or transactions well? If 
you care about data integrity, change your DB. ACIDity is mandatory for 
reliability.

...Edmund.