View Single Post
  #28  
Old September 26th, 2005, 04:33 PM
Roger Carlson
external usenet poster
 
Posts: n/a
Default

wrote in message
oups.com...

Again, you've misunderstood. I can add to your list of properties: 3)
It has special meaning in the SQL implementation. In the case of
Access/Jet, this special meaning is clustered index. Are you aware of
this special meaning? If you are, why do you think an incrementing
integer with no meaning in the data model makes for a good clustered
index?


I don't. I don't recall talking about clustered indexes at all. I also
believe that a clustered index is a requirement for a primary key.
Certainly, EF Codd said nothing about it. This is an implementation issue,
not a design issue.

Perfect? As I said up thread, the only good things about autonumber is
that it is convenient (because it provided by the 'system') and it is
efficient (data type = INTEGER). Do you really think there is no better
key generation algorithm than autonumber's? If autonumbers were
perfect, we wouldn't see daily posts of the like, 'Why do I have gaps
in my ID numbers?' I can see how 'quick and dirty' may equate to 'good'
given the right circumstances but 'perfect' may be an overstatement.


Yes, perfect. (BTW, autonumbers are Long Integer, not Integer). In terms
of Relational uniqueness, why do you care if gaps develop? This is internal
to the database and the user should never see them. People who ask those
questions don't understand the purpose of an autonumber primary key. It's
got nothing to do with 'quick and dirty'. And in fact, convenience is only
a small part of what makes autonumber PKs perfect. As you will see below,
they also minimize redundant data in the database, make query design and
application design quicker and easier. And if you don't think that's
important, then you are not an application developer.

Why not use a UNIQUE on the autonumber column and use your clustered
index (PRIMARY KEY) for a more worthy candidate?


Because this is backwards. There is no reason to put a Unique constraint on
an autonumber. It is unique by definition. One major problem with compound
primary keys is that it increases the amount of Redundant data. Since
Normalization is the process of removing redundant data, by Codd's
definition, the smallest unique identifier is the best. An autonumber will
provide the smallest amount of redundancy.

In terms of efficiency, not only is the autonumber more efficient for
executing Joins, it is much easier to write queries with autonumber
primary/foreign keys than compound text fields. Consider the Contact table.
Suppose each Contact could have multiple phone numbers. In this case,
proper design dictiates you would need a separate table for the phone
numbers. The tables would look like this:

Contacts PhNumbers
========= ===========
LastName(cpk)-----LastName(cpk fk)
FirstName(cpk)----FirstName(cpk fk)
Address(cpk)------Address(cpk fk)
(more fields) Phone (cpk)

Creating a query would look like this:
SELECT Contacts.*, PHNumbers.Phone
FROM Contacts INNER JOIN PHNumbers ON
(Contacts.Address = PHNumbers.Address) AND
(Contacts.FirstName = PHNumbers.FirstName) AND
(Contacts.LastName = PHNumbers.LastName);

Whereas with an autonumber primary key, you reduce the size of your database
by storing smaller primary/foreign key values, it is also easier to write
queries:

Contacts PhNumbers
========= ===========
ContactID(pk)--| PhoneID (pk)
LastName --ContactID( fk)
FirstName Phone
Address
(more fields)

Creating a query would look like this:
SELECT Contacts.*, PHNumbers.Phone
FROM Contacts INNER JOIN PHNumbers ON
(Contacts.ContactID = PHNumbers.ContactID);

This would be even worse if Contacts participated in a Many-To-Many
relationship.

I am saying I believe you have misunderstood what PRIMARY KEY means.
All you have said indicates you are unaware of the special clustered
index meaning that PRIMARY KEY has in Access/Jet. I am open minded to
there being another explanation: why do you think an autonumber makes a
good clustered index?


I understand quite well what a Primary Key means. By database theory it has
nothing to do with custered indexes and I don't know why you insist upon it.
While a clustered index may be a good thing, it is by no means required by
database theory or for a good database design. Especially when it
complicates things like creating queries, forms, reports and the like.
Database design which complicates application design is not a good thing
either.

Apologies if you feel disparaged. That was not my intention. We all
make misstatements from time to time.


I have made no misstatements. Certainly I disagree with you, but that
doesn't by definition make me wrong.