[AccessD] 2 quick questions

Sorry Jim, but as John pointed out, you are not living in the real world.

I am NOT going to use a composite natural key for a primary key.
Why, well, for the same reason John would not.  I am not going to tie
up the database doing an update to hundreds of thousands of records
if I change the natural key.  If I use an IDENTITY (autonumber in MS
Access), I never change it and do not have to contend with a cascading
update.

Check the definitions and then realize that you are talking about a logical
model and not a physical implementation of that logical model.

The absolute only time a composite natural key is valid for use is in the
fact table of a data mart.warehouse.  And, except in very rare occasions,
depending on they type of dimensions you are building, you might cause
an update of data.

Head knowledge is nice for some things, but real live experience will beat
out that any day.

Robert


At 02:51 PM 5/31/2011, you wrote:
>Fra: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] P? vegne af Jim Dettman
>Sendt: 30. maj 2011 02:19
>Til: 'Access Developers discussion and problem solving'
>Emne: 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>>
>
>   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.

Robert L. Stewart
www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com