[dba-SQLServer] PK/ANPK Debate

I ALWAYS start EVERY table design with an autonumber PK.  Even when it makes
no sense - like I'll never be doing a sort or find on this table.  Still -
just a habit - but I think it's a good one. I don't EVER have to think about
the consequences of picking a non-autonumber primary key.  

As the philosopher says: "No brains, no headache."

R


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: 10 March 2014 13:58
To: Discussion concerning MS SQL Server
Subject: Re: [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
> 
> 


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com