Hi keith,

>>

>>FromPC I, ToPC I, Distance I
>>1614      1616    2101  (meters)
>>1614      1617    4601
>>1614      1618    6708
>>....

>>This table contained about 16 million records and is about 200 MB in size. What am I going to do with surrogates here ? If I would force surrogates the table would be split into two:
>>
>>

Table1:
>>Pc_pk I, Pc_pstfk I, Pc_pstfk2 I, Distance I
>>1        1           2            2101
>>2        1           3            4601
>>3        1           4            6708
>>....
>>
>>table2:
>>Pst_pk I, Pst_postalcode I
>>1         1614
>>2         1616
>>3         1617
>>4         1618
>>....

>Your example is not representative of a normalized database.
The table more closely resembles the results of an aggregate query.

But it is a table and cannot be contructed with a single query because the distance is not computable from any data in the system: in fact it is what I call a linking table. It links a distance to a combination of two postal codes. Such table is needed to determine the distance between two geographical seperated place troughout the netherlands. The table (in the first form) is perfectly normalized.

>Most people would not include surrogate keys in output tables.

I'm not sure what you mean by output tables. If your mean that leaf tables (tables without a child) have no need for surrogate keys, if some other key exist (In my example its the combination of FromPC and ToPc), you're right, and this exaclty the point i'm trying to make: Not every table needs a single integer surrogate key !!

>Also, what is your viewpoint on partitioning a database with intelligent keys?

I'm not sure what you mean by "Partitioning a database". I simply mean to tell that sometimes it makes more sense to use an intelligent key than a surrogate. For example a persons table:

Pk I, Name, SSN, Sex, DayOfBirth, Postalcode, Street, housenumber, City, state, country

To identify the record we use a surrogate primary key. No discussion about that.
But imagine this table is used in a mortage application where different data applies for males and females (Males tend to die earlier). so then I've got to have a Sex table. O.K, Are you going to use a surrogate key here ?

Imagine this table is going to be used in a horoscope application where the DayOfBirth is reffering to another table in which world wide events of that day are stored. Am I going to turn this field from date into a surrogate integer ?

In the netherlands, If I have a complete postalcode table, I could extract the street and city automaticly. To normalize I should omit Street and City because this data can be computed with a join with the postalcode table. However, since my postalcode table does not contain info about foreign countries it might be wise not to do so, or else it will be impossible to enter the addres of these persons. This problem also arises when I try to turn the postalcode into a surrogate key.

When not working with a postal codetable to look up streets and cities, You might want to use lookuptables for both cities and state. How are you going to define them:

Table State:
PK I, code C(2), Fullname C(30)

Table City:
PK I, City C(30)

OR

Table State
Code (2), Fullname C(30)

Table City:
City C(30)

Of course the same applies to country. I simply trying to tell:" Think if you need a surrogate or intelligent key in a particular situation". Many people here have an attitide of using surrogates everywhere while this might be complexing your application way more than use them when neccesary. If the described table (with all the describes childs) is written by a surrogate purist it looks like this:

Table: Persons
PK I, Name C(40), SSN C(15), Sex_FK I, DayOfBirth_FK I, PostalCode I, Street C(30), Housenumber C(7), City_Fk I, Country_FK I
1   , Walter      10209348,   10456,   108756,          129487,       Majoraan,     37                13434,     23423

Compare this with the mixed surrogate and intelligent key approach:

PK I, Name C(40), SNN C(15), SEX C(1), DayOfBirth D, PostalCode C(7), Street C(30), Housenumber C(7), City C(25), Country C(25)
1     Walter      10209348,  M,        07/07/1971,   1616 TP,         Majoraan,     37                Hoogkarspel,Netherlands

We as programmers, are trying to make our programming code as readable as possible. This should also apply to our data: Don't use surrogate keys when not neccesary; it can make a mess of your database.

Walter,