Hi Jeff,

I'm certianly not a normilization expert, but if could make a comment (or rather ask a question).

What happens when someone wants to change the Division Name? In the example using Natural keys, you would have to change 5 different fields in the 3 tables, and parse through each of the composite keys to change the name.

Also, you would be storing the same data in multiple times in different tables.

hth

>Hi all,
>
>I'm having a discussion with my boss and a fellow co-worker who is a dba about the usage of surrogate keys for primary keys in tables (he calls them "artificial keys").
>
>My argument is for using single-field sequence numbers as primary keys for every table and using single-field foreign keys in any child tables (which would also contain their own single-field primary keys). My reasoning is that these sequence key fields will never change because they're not part of natural data. I'm also arguing that this approach is more efficient for SQL joins, indexed seeks, etc.
>
>My colleagues on the other hand are saying that it's a mistake to use "artificial" keys because they aren't self documenting in the code. They even argue for composite keys to be used when multiple fields uniquely identify a record.
>
>For example, let's say I have a division table, a department table that is a child of the division table, and a work center table that is a child of the department table. My colleagues are saying that the keyfields should be designed in the following fashion:
>
>DIVISION TABLE
>Field 1: Division # or name
>Primary Key: Division # or name
>
>DEPARTMENT TABLE
>Field 1: Division # or name
>Field 2: Dept # or name
>Primary Key: Division # + Dept #
>
>WORK_CENTER TABLE
>Field 1: Division # or name
>Field 2: Dept # or name
>Field 3: Work Center # or name
>Primary Key: Division # + Dept # + Work Center #
>
>My suggestion would look like the following:
>
>DIVISION TABLE
>Field 1: PKDivision (Integer)
>Field 2: Division # or name
>
>DEPARTMENT TABLE
>Field 1: PKDepartment (Integer)
>Field 2: FKDivision (Integer)
>Field 3: Department # or name
>
>WORK_CENTER TABLE
>Field 1: PKWorkCenter (Integer)
>Field 2: FKDepartment (Integer)
>Field 3: Work Center # or name
>
>I always thought that the going rule of thumb is that surrogate (artificial or auto-incrementing) keys are the way to go. I'm curious to know what the going consensus is here on the UT?
>
>Thanks!
>
>-JT