[dba-SQLServer] PK/ANPK Debate

Arthur,

Languages, like Countries, have standard ISO codes, but there are also changes
being made to them, like countries. Because of that, an autonumber 
primary key is
pretty much required so changes are not cascaded.

I always do a surrogate PK in every table.  It is just good 
design.  If you start
with a method, you stay with a method.  Consistent design...

Robert

At 11:39 PM 3/10/2014, you wrote:
>Date: Mon, 10 Mar 2014 08:43:16 -0400
>From: Arthur Fuller <fuller.artful at gmail.com>
>To: Discussion concerning MS SQL Server
>         <dba-sqlserver at databaseadvisors.com>
>Subject: [dba-SQLServer] PK/ANPK Debate
>
>To start off the week, I thought that I would re-open the PK/ANPK debate,
>limiting the discussion to a specific group of tables: lookup tables.
>Consider this example:
>
>SCENARIO 1:
>Table: Volunteers
>PK: VolunteerID (autonumber)
>Columns: Name, Address, etc.
>
>Table: Languages
>PK: LanguageID (autonumber)
>Columns: Language(varchar(20)
>
>Associative Table: VolunteerLanguages
>PK: VolunteerID, LanguageID
>Columns: VolunteerID, LanguageID, and perhaps Profiency
>
>SCENARIO 2:
>Table: Volunteers
>PK: VolunteerID (autonumber)
>Columns: Name, Address, etc.
>
>Table: Languages
>PK: Language
>Columns: Language(varchar(20)
>
>Associative Table: VolunteerLanguages
>PK: VolunteerID, Language
>Columns: VolunteerID(int), Language(varchar(20), and perhaps Profiency
>
>Both scenarios exist to allow the recording of multi-lingual Volunteers.
>The knee-jerk choice is to go with the first scenario, but I am calling
>this into question, on the grounds that it complicates queries, comboBoxes
>etc. unnecessarily. The name of the language is sufficient as a PK in
>itself. No one wants duplicate occurrences of any language name in this
>table.
>
>In the associative table, the second Scenario takes up a little more space.
>But surely in this age when laptops have multi-TB hard disks, this is of no
>real concern. So why complicate queries, combos, listboxes etc. with a
>pointless lookup and additional Join?
>
>The same argument could be applied to many lookup tables, for example
>Countries? Why bother with an ANPK when the official ISO abbreviations
>exist, are easily obtained, and are universally recognized? Perhaps the
>argument is even stronger here, since the ISO codes are char(3)?
>
>In case it isn't clear, I am not arguing for this design in the common
>Customer+Orders+OrderItems situation, but only in the subset of tables
>where there are a small number of rows whose text values must be unique. In
>the past, I have designed this subset according to the first scenario,
>Scenario 1 demands either a unique index on the text column or logic in the
>front end to achieve the same thing. In short, I no longer see the point.
>
>--
>Arthur

Robert L. Stewart

Any fool can write code that a computer can understand. Good 
programmers write code that humans can understand. --Martin Fowler

www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com