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


> >
> > 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?

As far as it goes. I would clarify three points:

First:
The non-sequential issue is not just one of searching - it has physical impacts in server databases that rely on clustered indexes. This is where the data in the table is PHYSICALLY ordered in the index sequence. While it is the most efficient type of index for retrieval, it is a pain when you need to insert a record 'between' existing records. (The process is similar to the old VFP SORT command - you copy all records out to a temporary table, add back those prior to the new one, then the new one, then the rest. NOT at all efficient and positively slow when tables are large or you are doing a lot of inserts).
This is the reason that all databases have some form of sequential auto-generation for primary keys that ensures that new records are only ever added at the 'end' of the table.

Second:
I am not against using GUIDS where you need them - as I have already said. For merge/replication add a GUID by all means to uniquely identify records - but unless you also know where it came from that isn't much use. An equally valid solution is to generate a new (integer) PK and store the source table and old source table PK in the new record. The result is a three-column or 'poor-mans' GUID (NEW PK, Source + Old PK). But at least you can find the original record from it - which you can't if all you have is a GUID!

Third:
You mention running out of integers - well maybe in VFP that supports only 4-byte integers that is an issue (though if you are using VFP to store tens of millions of records I would have to question the wisdom of that decision) but in back end servers you can use 8-byte (+/-9,223,372,036,854,775,807) or even 16-bit Integers (which are actually UUIDs anyway). So that really isn't an issue.

Finally I would also point out as a general principle that generating PK values on the client as opposed to the server is really very bad practice! The only way you can be truly certain that a generated key does not already exist, is to check all other extant keys. It follows, therefore, that the only reliable place to generate a primary key is in the database.

Q.E.D

Regards
Andy Kramek
Microsoft MVP (Visual FoxPro)
Tightline Computers Inc, Akron Ohio, USA

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