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