I should note that, if I were going to write the app again from scratch, I'd have just used GUID's as primary keys. :) In the end, the application ended up being much more flexible this way, but it came at a cost of complexity.
Ben Rogers http://www.c4.net v.508.240.0051 f.508.240.0057 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Ben Rogers > Sent: Wednesday, December 08, 2004 10:30 AM > To: [EMAIL PROTECTED] > Subject: RE: [CFCDev] GUIDs as Primary/Foreign Keys > > > How do you manage having both identity integers and GUIDs? I'm > > leaning toward this approach, but have concerns over maintenance > > etc. Do you use the integer as the PK/FK and just have the GUID > > sitting there for when you need to communicate between systems? > > I worked on an application platform for a couple of years. The basic > premise > is that it allows business people (sans-programming skills) to build web > enabled applications. It was originally meant to be a tool to assist in > data > migration and to replace the use of spreadsheets and desktop databases for > distributed or collaborative apps. > > Anyway, it uses a mixture of natural and surrogate keys (the latter using > SQL Server's identity constraint). As some of these apps grew into front > ends to SAP, we found we needed to be able to distribute the apps (for > resale) and to push application level changes from development to staging > and from staging to production. > > Rather than overhaul the entire database structure, we just tacked on a > column to each table. The column is a GUID which uniquely identifies the > record but is not used as a primary key. When an app gets installed, > records > are inserted and new identity values are generated. When an app gets > updated, we check to see if a record exists with the specified GUID. If it > does, then we update it. Otherwise, we insert a new record. > > The main point is that, by using the GUID, we don't rely on the identity > values being the same from instance to instance. In addition, we didn't > have > to rebuild a large portion of the app to add these features. Also, it > works > with various types of database design (surrogate and natural keys, > identity > columns or GUID's as primary keys, etc.). So, we can use the same methods > for shipping application data as we use for shipping the applications > themselves. > > Ben Rogers > http://www.c4.net > v.508.240.0051 > f.508.240.0057 > > ---------------------------------------------------------- > You are subscribed to cfcdev. To unsubscribe, send an email > to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev' > in the message of the email. > > CFCDev is run by CFCZone (www.cfczone.org) and supported > by Mindtool, Corporation (www.mindtool.com). > > An archive of the CFCDev list is available at www.mail- > [EMAIL PROTECTED] ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). An archive of the CFCDev list is available at [EMAIL PROTECTED]
