Re: *Proper* solution for 1..* relationship?

David Johnston <polobo@yahoo.com> wrote:

> i would much like to hear situations where those risk factors are
> not negligible for models that are truly 1..*.

For my part, the only cases I've run into where it was important to
ensure that children did in fact exist at commit time have been in
financial transactions; and in those cases ensuring that was
necessary *but not sufficient*.  A deferred trigger was necessary
anyway.  In the simplest case, you have a transaction header with
transaction type (assessment, receipt, adjustment, etc.),
transaction date, and other information about the transaction as a
whole, and a number of transaction detail records with the money
amounts.  We never wanted a header without detail, but we never
wanted a detail row with a zero money amount, *and the sum of the
detail rows for a transaction had to total zero*.  Positive numbers
represented debits and negative numbers credits in this
double-entry accounting system.  Of course, I'm vastly
oversimplifying, because there were many other things which needed
to tie out before the database transaction could be allowed to
commit.  Once committed, these were "written in ink" and could not
be updated again; errors had to be fixed by adding adjustment
transactions so that the entire history was always viewable.

I guess my point is that a declarative way to say that "at least
one child row is needed" would have been worth very little, because
of all the other checks which needed to be done in a deferred
trigger.  I suspect that there are cases where there would be value
in such a declarative constraint, but I've been working with a very
wide variety of organizations for decades without having come
across such a case yet.  Without some solid detail about real-world
use cases, nobody can do a good job of designing a feature.

I think it's safe to say that if anyone would demonstrate a clear
need for such a feature, and put forward resources for its
development, it would be added.  So far nobody has made such a
case; nor has anyone put any resources into developing such a
feature.

> Railing against prevailing wisdom and the decisions that people
> have made over the last 40 years - not to mention insulting
> others - is a waste of effort that would be better spent crafting
> a more detailed message.

Right.  Insulting people doesn't make the case, nor do much to
motivate anyone to work on such a feature.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company