Re: Stupid Database Tricks

"Brian Selzer" <brian_at_selzer-software.com> wrote in message news:JtX4i.29854$Um6.3419_at_newssvr12.news.prodigy.net...
>
> "Roy Hann" <specially_at_processed.almost.meat> wrote in message
> news:gradnembFKkVusnbnZ2dnUVZ8sGvnZ2d_at_pipex.net...
>> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
>> news:d9V4i.6084$4Y.5622_at_newssvr19.news.prodigy.net...
>>> Now, now, Marshall. System-generated surrogates can simplifiy things if
>>>
>>> (1) the database is temporal--particularly historical,
>>> (2) some constraints are temporal,
>>
>> You are describing a possible workaround, not a desirable feature. One
>> should make that clear. Also it is not clear this is even a good
>> workaround. I suspect it is a very inadequate one.
>
> Workaround? Perhaps. I suspect that you're wrong, though. See below.

I'll leave it to someone who really knows what they're talking about to settle this. All I know is that my not-very-recent reading of a systematic approach to temporal databases concluded the need to engineer temporal support into the DBMS. Hence my characterization of this as a workaround.

>>> (3) keys may be updated
>>
>> Wrong. Seriously wrong. See my earlier response to David Cressey re ON
>> UPDATE CASCADE.
>
> Cascading updates can cause other problems, like deadlocks.

That is a spurious objection. Deadlocks are always possible in a multiuser system and cannot be eliminated even in principle. We always have to expect to have to handle a deadlock every time we touch the database. Furthermore, deadlocks are--in principle--easily handled (provided you know what updates the transaction includes, and I admit SQL makes that very hard).

> Multiple assignment is a better solution,

I would very much like a DBMS that supported multiple assignment, but I don't see why you think it would be a "better" solution. Better at what? I see how it would be worse. In terms of reliable coverage it is no better than a series of single assignments: you have to specify which tables to update, and that means relying on future developers retrofitting any necessary additional table references everywhere the multiple assignment is used.

> but there are problems with it as well.

Agreed, so what benefit makes it worth suggesting?

> There are other solutions to the problem that are implementation specific,
> but whether or not key updates are cascaded or not isn't the issue: the
> issue is that if keys can be updated in a temporal database, then you
> might end up rewriting history! See below.

I can easily see how one *could* choose a table design that attempts to support temporal queries with a non-temporal DBMS where this would be a problem. But I don't see why one necessarily always has to choose a design that would be subject to that kind of anomaly.

I do notice that we are probably arguing at cross-purposes here. Marshall was objecting to designs that *always* automatically include system generated values; you are saying they *sometimes* have their uses. I actually agree with that.

Roy Received on Wed May 23 2007 - 16:57:04 CEST