Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy
 To: Andy Kramek
  Where is Andy Kramek?
 Hot Springs Village
 Arkansas - United States
 Andy Kramek
 Tags
Subject: RE: Primary Key
Thread ID: 143107 Message ID: 143401 # Views: 40 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, August 28, 2007 3:48:57 PM         
   


> > Yet, why do people insist on saying how long they've been in the industry?
>
> I can't tell you that in general terms.
>
> In my case I merely meant that my personal preferences are based on what I have seen done in different places over time. I have seen all sorts of approaches, some good, some bad, some indifferent. I try to base what I do on what I have seen proven to work best in practice in preference to relying on theory, marketing or even product documentation (and no, I am NOT trying to suggest that you, or anyone else in particular, does so).
>
> The only grounds I have for making those decisions, or even voicing my opinion, is my own experience.
>
> Regards
> Andy Kramek
> Microsoft MVP (Visual FoxPro)
> Tightline Computers Inc, Akron Ohio, USA

I would dearly LOVE to get in a seminar situation with you two discussing the relative merits of GUID vs Integer based Primary Key fields. This discussion (and an earlier one) taught me an amazing amount about primary keys.

I thought I might summarize what you have been discussing here and you can correct me if I am wrong.

Mike, you prefer to use a GUID as a primary key as it is globally unique and will be for the forseeable future. You recognize Andy's argument that a primary key needs to be "unique within a table" but counter that a GUID is indeed unique within that table - AND it is unique within that application and even beyond. The downside of a GUID is that it quite a bit larger than a 4 byte integer, but the upside is that you can generate unique primary keys locally.

Andy, you prefer to use an integer based primary key because it is only unique within the table. You state that you do not need something globally unique and the overhead of that 16 byte character string will slow down data transmission over a network or when running a query. 16 bytes is obviously 4 times larger than 4 bytes so it will take 4 times longer to transmit a GUID. While this may not make much difference on small queries, on larger queries or when the data must be transmitted over slow Internet connections, this delay can add up to something noticable.

The randomized GUID is "statistically guaranteed" to be unique. It is essentially a randomized series of 16 bytes that, in all probablity, will not be duplicated for the next 10 billion years or so. (Like the dinosaurs, we will probably be extinct by then, so if at that time a non-unique key is generated, who cares?)

With an integer based primary key, it is entirely possible that you will "run out of primary keys" because it is only a four byte integer. For most applications (even large applications) the likelyhood of running out of numbers in an integer based primary key is so small as to be non-existant. Integer based primary keys are not randomly generated. Indeed they are generated in order so you can take advantage of that incremental order when doing s search.

In my mind the key to your arguments are:

GUID - Upside: They are unique globally, so why not use them? You get to generate PK's locally, you can generate them across tables or even across databases. Downside: They are much bigger and therefore less efficient. They are also random so you cannot use any sort of order in doing a search.

Integer PK's - Upside: They are much smaller and therefore more efficient to transmit and use. They are generated in order, so you can take advantage of that order in a search. Downside: They are not unique globally so if you are attempting to merge two tables (or two databases) you are going to run into problems when the two tables that you are merging both have a recordID = 1.

Do I have the jist of both arguments?

Ken
You shall know the truth - and the truth shall set you free. (John 8:33)

ENTIRE THREAD

Primary Key Posted by Aaron Hathaway @ 8/24/2007 10:25:32 PM
RE: Primary Key Posted by Borislav Borissov @ 8/24/2007 10:46:52 PM
RE: Primary Key Posted by Aaron Hathaway @ 8/24/2007 11:09:39 PM
RE: Primary Key Posted by Ken Murphy @ 8/25/2007 1:47:10 AM
RE: Primary Key Posted by Barbara Peisch @ 8/25/2007 5:01:14 AM
RE: Primary Key Posted by Mike Yearwood @ 8/26/2007 2:02:59 AM
RE: Primary Key Posted by Andy Kramek @ 8/27/2007 1:01:44 PM
RE: Primary Key Posted by Mike Yearwood @ 8/27/2007 3:37:30 PM
RE: Primary Key Posted by Andy Kramek @ 8/27/2007 5:57:08 PM
RE: Primary Key Posted by Mike Yearwood @ 8/27/2007 7:55:38 PM
RE: Primary Key Posted by Andy Kramek @ 8/27/2007 8:31:58 PM
RE: Primary Key Posted by Mike Yearwood @ 8/28/2007 2:33:15 PM
RE: Primary Key Posted by Andy Kramek @ 8/28/2007 2:48:25 PM
RE: Primary Key Posted by Ken Murphy @ 8/28/2007 3:48:57 PM
RE: Primary Key Posted by Andy Kramek @ 8/28/2007 5:06:25 PM
RE: Primary Key Posted by Ken Murphy @ 8/28/2007 6:03:54 PM
RE: Primary Key Posted by Andy Kramek @ 8/28/2007 9:10:24 PM
RE: Primary Key Posted by Ken Murphy @ 8/28/2007 10:48:19 PM
RE: Primary Key Posted by Andy Kramek @ 8/29/2007 12:41:37 PM
RE: Primary Key Posted by Ken Murphy @ 8/29/2007 12:58:20 PM
RE: Primary Key Posted by Mike Yearwood @ 8/29/2007 7:49:54 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:09:30 PM
RE: Primary Key Posted by Tore Bleken @ 7/25/2016 8:03:30 PM
RE: Primary Key Posted by Hugo Ranea @ 8/29/2007 9:34:10 PM
RE: Primary Key Posted by Daniel Hofford @ 8/29/2007 11:46:07 PM
RE: Primary Key Posted by Mike Yearwood @ 7/24/2016 1:53:22 PM
RE: Primary Key Posted by Benny Thomas @ 7/24/2016 2:30:24 PM
RE: Primary Key Posted by Mike Yearwood @ 7/24/2016 3:20:23 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:16:41 PM
RE: Primary Key Posted by Mike Yearwood @ 7/25/2016 8:49:05 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:04:07 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 6:59:58 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:00:06 PM
RE: Primary Key Posted by Koen Piller @ 7/25/2016 11:37:27 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/26/2016 8:01:52 PM
RE: Primary Key Posted by Mike Yearwood @ 7/26/2016 8:59:17 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/27/2016 7:02:39 PM
RE: Primary Key Posted by Paul Gibson @ 7/27/2016 12:21:25 PM