Re: Stupid Database Tricks

"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.

>> (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. Multiple assignment is a better solution, but there are problems with it as well. 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.

>> and either (1) or (2),

(1) If a key can be updated in a temporal database, then any query that involves a state of the database at some previous point in time will be incorrect. To fix this there are three alternatives (that I know of) (a) introduce a system-generated surrogate that is guaranteed to never change (or be reused), (b) introduce additional attributes to record the original key value and add a transition constraint that prevents those attributes from being updated (but see (2) below), or (c) maintain a timestamped key-change log. Since natural keys can involve several attributes, using a surrogate can significantly reduce the amount of storage for both data and indexes, so unless there is a need for the original key value beyond that for entity continuity, a surrogate can significantly improve performance in a temporal database. (That's one of the reasons that they're used in data warehouses!) Queries involving a timestamped key-change log can be very involved and are much more susceptible to human error since they involve traversing a linked list. At a minimum, they involve an additional theta-join, which can significantly affect performance, especially for large data sets. As a result, system-generated surrogates can reduce the complexity (Big O) for such queries.

(2) Unless your database engine supports FOR EACH ROW triggers, not all temporal constraints can be enforced for set-based updates--particularly if the update involves prime attributes. One way to fix this is to add a system-generated surrogate.

>> (4) the schema may evolve and (1), or
>
> I suspect this is wrong too, though to be honest I've no idea what you
> have in mind here.
>
> Roy
>
>
Received on Wed May 23 2007 - 15:38:49 CEST