Jim Dettman
jimdettman at verizon.net
Sun May 29 19:18:57 CDT 2011
<<Primary Key = Nothing a user can use for anything and is totally not dependant on data. For db system use only>> Pure hogwash. The very definition of a PK in the relational model is exactly opposite of what you describe. John's problem is exactly what I tried to point out on this list a while back; there is a fundamental difference between a primary key and a tag or a pointer. John's problem is related to the meaning of the data itself; how to determine that a row within the relation is unique. In other words, John is looking for the primary key; that combination of attributes which identifies a specific row and ensures that the row is not duplicated. That is not the same thing as adding a unique tag or pointer on every row (an auto number). And despite popular belief, a auto number in relational terms is not a surrogate key. John needs to come up with a key based on the data that will ensure that a row is unique. If the field(s) used to do that cannot ensure that, then more fields (attributes) need to be added to the table (relation). For *performance* reasons in the DB, he will use an auto number as a pointer (like all of us do), but in addition to that, he will need to create a unique index based on the PK. He might take the shortcut of making that index a hash, which for performance reasons may be required, but it's a bad idea because it can lead to an update abnormalities. If you can live with that, great and at the end of the day, it may be required to get the job done. Since it's only one index on the main table, I wouldn't take that shortcut. And as far as the primary key not being able to change (as in a name change or SS#), again total hogwash. That's why relational DB's allow for cascading updates. There is nothing stated in any paper on the relational model that says a primary key cannot change. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert Stewart Sent: Friday, May 27, 2011 04:03 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] 2 quick questions Primary Key = Nothing a user can use for anything and is totally not dependant on data. For db system use only Business Key = Something within the data that is unique and can be used by a user for identification. Business Keys can be one or more fields, when more than one, it is a Composite Business Key. Remember the above and you will not have any issues. Example: tlkp_NameSuffix NameSuffixID Autonumber NameSuffix Text(30) NameSuffixID is the primary key NameSuffix is the business key, and has a unique index on it. Robert L. Stewart www.WeBeDb.com www.DBGUIDesign.com www.RLStewartPhotography.com At 01:18 PM 5/27/2011, you wrote: >Date: Fri, 27 May 2011 08:18:47 -0500 >From: "Dan Waters" <df.waters at comcast.net> >To: "'Access Developers discussion and problem solving'" > <accessd at databaseadvisors.com> >Subject: Re: [AccessD] [Spam]8.31 Re: 2 quick questions >Message-ID: <001101cc1c70$9d48a380$d7d9ea80$@comcast.net> >Content-Type: text/plain; charset="us-ascii" > >A field with an 'Intelligent Key' is fine - as long as you don't use it in a >table relationship. In other words, always have an arbitrary (autonumber, >etc.) key as the key that is used in table relationships. That way you'll >NEVER have to spend hours or days rebuilding tables when the key you used in >a dozen or more table relationships is changed. > >Dan > >PS - Even RFID's can be swapped out, and Tattoos can be removed! > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan >Sent: Friday, May 27, 2011 1:06 AM >To: Access Developers discussion and problem solving >Subject: Re: [AccessD] [Spam]8.31 Re: 2 quick questions > >Intelligent Keys are an excellent concept when you can attach them to the >item concerned or to it's container. > >Unless you can tattoo them or implant an RFID chip, there is little point in >trying to use IKs to identify people. :-) > >-- >Stuart > >On 27 May 2011 at 1:55, Arthur Fuller wrote: > > > I don't know where you obtained this rule against the use of > > Intelligent Keys, but I seriously object, and cite Chris Date and > > Fabian Pascal and the inestimable Joe Celko for reasons why IKs are a > > good thing, in certain applications (eggs are a bad example; car parts > > are an excellent example). > > > > A. > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com