postgres development

Matthew Vanecek mevanecek at yahoo.com
Thu Mar 6 18:18:58 CST 2003


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 6 Mar 2003, Roland Roberts wrote:

> --[PinePGP]--------------------------------------------------[begin]--
> >>>>> "Andrew" == Andrew Pimlott <gnucash-devel at andrew.pimlott.net> writes:
> 
>     Andrew> I expect you can find a way to enforce most of them in the
>     Andrew> database, although it may be awkward in SQL, and you would
>     Andrew> be duplicating the same constraints in the code.  But how
>     Andrew> onerous are these invariants?  Obviously, splits must
>     Andrew> balance, and there are probably (?) some limitations on
>     Andrew> which kinds of splits can go in which kinds of accounts.
>     Andrew> Am I missing a lot?
> 
> I can't answer you final question, but the constraints can be managed
> in PostgreSQL via triggers.  I have added similar trigger-based
> constraints to recalculate certain columns values based on other
> columns' values.  The use of triggers will prevent the use of less
> feature-complete databases, but the major RDBMS products will all have
> triggers and stored procedures (although every one of them will be
> different).

This is entirely true.  Calculated fields, however, are a blatant and 
egregious violation of BCNF.  That being said, they are extremely useful 
in certain cases, as are other de-normalizations.  In any case, I'm not 
entirely certain that the use of triggers for this type of activity (e.g., 
calculating field values) is entirely desirable.  It would definitely 
duplicate Engine functionality. In general, calculations should be left up 
to the application, and data storage and integrity is the realm of the 
RDBMS.

> 
> However, how much of that do you really want to push into the
> database?  Right now, the XML file doesn't enforce any
> constraints/invariants; that's done by the front-end and the back-end
> is "merely" a storage container.  Pushing the constraints into the
> backend makes it possible to maintain consistency even with a badly
> written alternate front-end application, but is that something you
> want to worry about?  If it is, then triggers will do the trick.

That's true.  But the dbms is there to enforce the relationships, not the 
validity (e.g., each Split belongs to a Transaction.  That's the rule the 
dbms enforces.  The Engine must enforce the 2-Splits-per-Transaction 
rule).  Not saying it *can't* be done--I just seriously doubt the 
appropriateness of the trigger idea.  Calculations, definitely, are out, 
but the min. 2 Splits-per-Transaction rule may be enforceable in the DB, 
too, but not that Transactions balance...

At some point, you have to say, "Enough!", and simply concede to passing 
valid data to your data store. :)


- -- 
Matthew Vanecek
perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'
********************************************************************************
For 93 million miles, there is nothing between the sun and my shadow except me.
I'm always getting in the way of something...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE+Z+V2i/CNzDSN0RIRAhOtAJ94m69NUE0f2dWtS8YlzYc8MfwZpQCfXFc2
tyIrVoopfvSL9FhLNxvMVaI=
=zAEm
-----END PGP SIGNATURE-----



More information about the gnucash-devel mailing list