[AccessD] Surrogates vs Natural

Hi Stuart and Arthur

Well I can, or maybe not ... 

If you, say, assemble input data from a table from several external databases into one central table. Each table will have an autonumber ID but - as each ID may be represented in several tables - you have to have an additional field in the central table. 
This field can either be a fixed value for each of the external tables (1, 2, 3 etc.) or - better - an surrogate key identifying the external tables hold in a separate table describing these external tables. For the second method, this field combined with the original ID will be a compound key which represents a surrogate unique ID for the records in the central table.

However, you could also regard these fields as simple foreign keys to the original tables. Thus, a new surrogate key had to be added to the central table, a bit like you do when you add a replication ID. This ID could be an autonumber or a GUID.

I can see no reason not to follow this last method, except for situations where a new surrogate key is not needed for anything. This could be the case if you just collect data for statistics where you summarize and so on without a need for identifying the single record, or for a central archive of the external tables.

/gustav


>>> artful at rogers.com 29-12-2006 00:59 >>>
AFAIK there is no such thing as a multi-column surrogate key. If I'm wrong, it wouldn't be the first time, but the concept itself makes me dizzy.

----- Original Message ----
From: Stuart McLachlan <stuart at lexacorp.com.pg>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Thursday, December 28, 2006 5:34:34 PM
Subject: Re: [AccessD] Surrogates vs Natural

?multifield surrogate keys?

I presume you mean "natural".
I can't think of any reason to have a multifield surrogate.

On 28 Dec 2006 at 14:22, Charlotte Foust wrote:

> Yeah, well try working with multi-table joins on multifield surrogate
> keys and see how handy it is! :o<

-- 
Stuart