Re: Surrogate Keys: an Implementation Issue

paul c wrote:
> I feel like my mouth has been taped over, hands cuffed and head tied to
> a bouncing ball and my neck is aching from trying to follow it.
<snip>
> "produce the result" is a tricky phrase because it hints at both logical
> and physical. We must separate how we produce from what results.
>

paul c,

Sorry if I've given you a headache, and thanks for your patience!

I may be mixing logical and physical models in the discussion. I think part of the problem is that there may well be a third,intemediate level that I'm really attempting to discuss. Sort of like the TRM -- it's a layer bewtween the logical and physical.

In the below discussion, I'll use "table" and "row" for this intermediate level, and resort to "relation" and "tuple" when refering to the logical model. Considers the following "table":

ID AT SNO SNAME STATUS TS
-- --- --- ----- ------ --

1  0    S1   Smith  20      1
2  0    S2   Jones  10      1
3  0    S3   Blake  30      1

And the relation that the DBMS can produce from it:

SNO(pk) SNAME STATUS

---      -----  ------
S1       Smith  20
S2       Jones  10
S3       Blake  30

Now consider making an assignment to the relation that changes the status of supplier
S2 to 40. One way to implement this in the intermediate level is to append a new "row" to the "table":

ID AT SNO SNAME STATUS TS
-- --- --- ----- ------ --

1  0    S1   Smith  20      1
2  0    S2   Jones  10      1
3  0    S3   Blake  30      1
2  1    S2   Jones  40      2

Now, the DBMS just needs a way to convert the "table" to a relation. This is easily done by analyzing the SNO "column" and the AT (append "type", to distinguish inserts, updates, and deletes) "column" to produce:

SNO(pk) SNAME STATUS

---      -----  ------
S1       Smith  20
S2       Jones  40
S3       Blake  30

Note that in some sense, the "log IS the database"

Now the DMBS should also, I think, be a able to produce this relation:

SNO      SNAME  STATUS  TS
---      -----  ------  --
S2       Jones  10      1
S2       Jones  40      2

with the system supplied time stamp TS as a primary key. I don't think this any way violates any relation principles, though finding a way to specify this relation in the relational algebra I'm not sure about.

Now, if I allow the user to change the supplier key from S2 to S20, we get the following
"table":

ID AT SNO SNAME STATUS TS
-- --- --- ----- ------ --

1  0    S1   Smith  20      1
2  0    S2   Jones  10      1
3  0    S3   Blake  30      1
2  1    S2   Jones  40      2
2  1    S20  Jones  40      3

Now the DMBS must use the ID column rather than the SNO column - ID being an immutable identifier, in order to produce the current state of the associated relation:

SNO(pk) SNAME STATUS

---      -----  ------
S1       Smith  20
S20      Jones  40
S3       Blake  30

or the "update history" of supplier S20:

SNO      SNAME  STATUS  TS(pk)
---      -----  ------  --
S2       Jones  10      1
S2       Jones  40      2
S20      Jones  40      3

Thus if we allow changes to a primary key, we must have some other immutable id, and I don't see how the timestamp helps here, though maybe in more conventional implementations it would. Received on Fri Jul 21 2006 - 08:03:09 CDT