Re: MERGE as the imperative form of aggregation

"Marshall" <marshall.spight_at_gmail.com> wrote in message news:1176706962.249281.173130_at_n76g2000hsh.googlegroups.com...
> On Apr 15, 11:05 pm, "Brian Selzer" <b..._at_selzer-software.com> wrote:
>>
>> That's because you appear to have bought into the [...]
>
> Anyone who's been reading this newsgroup for any length
> of time is familiar with the fact that "correlating tuples"
> is a pet issue of yours. Let's not rehash those old arguments;
> it was not productive the first n times, and there is strong
> reason to believe it won't be productive time n+1.
>
> Much of the discussion rests on the importance of transition
> constraints. I would like to discuss that. Are they important?
> Fundamental? What theoretic basis do they have? What
> are we to make of the fact that they are tuple oriented
> when the RA isn't?
>
> Perhaps the best way to frame this discussion is in terms
> of a use-case. Please note that such a use case *cannot*
> be stated in terms starting with "Assume you need to
> have a transition constraint." That's called assuming the
> conclusion, a particularly nasty logical fallacy. Instead
> we should hope for something that will be a recognizable
> business requirement, that could be enforced, say, in
> application logic, but which could not be enforced centrally
> except via a transition constraint. That I would find illuminating.
>
> Anyone?
>

I think it's best to start at the beginning. Constraints can be classified into two categories: those that define the set of consistent database states (state constraints) and those that define the set of acceptable changes of state (transition constraints). The main difference between them is that state constraints are defined in terms of the contents of the proposed database state, whereas transition constraints are defined in terms of the difference between the contents of the proposed database state and that of the current database state. This is where it can get confusing, and I think I'll stop with the definitions here so as not to muddy the waters. Note that a transition constraint, like all constraints, is defined in the database to protect the database.

A simple use case: Consider a part as it winds its way through a manufacturing process. That process can be modeled using a state machine. Each step in the process has prerequisites that must be met before the step can be performed and inspection requirements that must be met before the part can move on to the next step. There may be loops in the process to deal with rework, branches to deal with capacity or outsourcing, or the part may exit the process as scrap. A transition constraint can be defined to verify that the proposed next step in the process is valid, given the current step. Clearly, this requirement can be enforced in application logic, but that leaves open the possiblity that an ad-hoc update could corrupt the database. Since the set of valid next steps depends on the current step, this can't be enforced by the database engine except via a transition constraint.

I would like to point out that I believe that constraints should be enforced by the database, not application logic--regardless of where that application logic resides. Although they can be used to enforce constraints (at least for every process that calls them), stored procedures contain application logic and as such are part of the application, even though they're stored in the database. The existence of a stored procedure does not prevent ad-hoc updates, so any system that depends on them to enforce constraints leaves open a gaping hole.

> Actually I'm also interested to hear opinions. I've never
> come across a particular need for transition constraints,
> but I don't regard my own experience as definitive. (Hardly!)
> However if a bunch of folks say the same thing, that is
> more suggestive. Clearly Brian is a firm believer in transition
> constraints, however by himself he can be disregarded as
> a statistical outlier. (I'm not picking on Brian here; any *one*
> of us is not a statistically significant trend.)
>
>
> Marshall
>
Received on Mon Apr 16 2007 - 15:18:40 CEST