[dba-SQLServer] PK/ANPK Debate

I really shouldn't bite - but OK, here e go again.

Seceral reasons

1. A 20 character string takes rakes up considerable more storage that a ANPK,  in both the 
record and the index(es)

2. A 20 character string takes much longer to sort/filter than a number.

and the biggie.

What happens when you have a lot of records and someone decides that they want to  
change the name of a language ( Pidgin to Tok Pisin for example)

I san again, natural keys which have meaning to the user are never appropriate as (part of) a 
PK.   They will always be liable to change.

I'd much rather go with the slight additional complexity of designing the user interface versus 
the above deificiencies.

As for " either a unique index on the text".   A text index on one occurrence of each item is a 
relatively short list has so little impact that it is  not worth worrying about.  

-- 
Stuart

On 10 Mar 2014 at 8:43, Arthur Fuller wrote:

> 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
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
>