Home -> Usenet -> comp.databases.theory -> Re: Surrogate Keys: an Implementation Issue

Re: Surrogate Keys: an Implementation Issue

From: Paul Mansour <paul_at_carlislegroup.com>
Date: 21 Jul 2006 06:03:09 -0700
Message-ID: <1153486989.391427.38540_at_m79g2000cwm.googlegroups.com> 







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 - 15:03:09 CEST












Original text of this message