Re: Surrogate Key Semantics

jonshin2..._at_yahoo.com wrote:
> I have two questions with regard to database semantics for surrogate
> keys. Thanks in advance. Jon
>
> *************************************************
> Question 1
> **************************************************
> Table A has a surrogate key called ID.
>
> Table B has a 1:1 relationship with table A by way of A.ID. So, B.ID
> maps to A.ID to define the relationship.
>
> Is B.ID called a surrogate key even though it is not made up for that
> table, but for A.ID originally?

No, I would say that the value is only a surrogate in Table A. There it was not originally part of the data being recorded, but in Table B, which references it post factum, it is referring to an item that now _does_ exist, and hence it is not a surrogate as far as Table B is concerned. At least that's my interpretation - others in a more experienced position than myself may disagree.

> **********************************************
> Question 2
> ************************************************
> Table A has a surrogate key called ID.
> Table B has a surrogate key called ID.
> Table C's primary key is a composite key made up of A.ID and B.ID.
>
> Are the two fields in C (A.ID and B.ID) still called surrogate keys
> when we are talking about table C, or are they only known as surrogate
> keys to the original tables?

As per my response to question 1, the entries in C are not surrogates as they refer to items that now exist, and so are now as a consequence part of the real world being modelled. Received on Wed Nov 16 2005 - 18:27:23 CET