View Single Post
  #15  
Old September 25th, 2005, 05:06 PM
Amy Blankenship
external usenet poster
 
Posts: n/a
Default

I'm not sure what you mean by "re-load" the table.

-Amy

"Denis" wrote in message
...
Perhaps I will rephrase my question.

What are the dangers of using an autonumber field as the code for a code
values?
eg can the autonumber field get set to a differnet value if I have to
re-load the table. If it can be guaranteed to be static then I have no
problem with using it as a primary key eg for an employee id but if its
not
there seem to be some dangers to using it as such.

Certainly a persons name can never be a primary key - too many John
Smith's
out there but a key on surname is useful for an ordered lookup.

Bulk updates are not a good argument for not using a field(attribute) as a
key as they should be performed in non-prime time to minimise impact.

Normalisation is always the goal and there will always be some
fields(attributes) in the table that can uniquely define a row.
--
Denis


"Amy Blankenship" wrote:


"BruceM" wrote in message
...
What would you do to guarantee uniqueness in a Contacts table or some
such
involving names and addresses, in light of the fact that names and
addresses are subject to change?

SQL underlies Access queries. The design grid is a sort of SQL GUI (as
I
understand it). So I think you're saying that displayed order (e.g.
sorted by last name) is not what you are talking about when you talk
about
physical order. If I understand, you are saying that the structure of
the
index determines the order on the disk, not the order in the table when
it
is viewed directly.

I have a database that includes an Employees table. The primary key is
the EmployeeID. With it to do over again I might have used something
else, because it is at least possible that they will one day change the
format of EmployeeID, which is just a sequential 4-digit number.


Which is why most people use completely meaningless Autonumber fields as
primary keys. Because you can't change the value, format, or anything
else
of a field that is currently being used as a primary key. Also the
autonumber field will usually have a smaller size (on disk, no less) than
a
more meaningful key. Therefore, if you are using it in a relationship or
relationships, the other tables will have to store less information when
they are referring to that primary key of this table.

So, for instance, if you had an employeeID that was an autonumber, all of
the other tables that refer to your EmployeeID would have saved 11 bytes
every time they had a foreign key to your employee table, and you could
have
stored what is now your employeeID primary key just once, for a total of
just the one 15 byte storage of the employeeID string. This is the whole
point of normalization. Anything that is actually used as data should
just
be stored once, with the smallest possible reference to it from other
places
that need to relate to the base data.

More than likely you'll eventually have to move to an Autonumber primary
key
there for the above listed reasons. Most of us encounter this situation
at
least once, and from that point forward we use Autonumber primary keys,
since fixing the problem once it has developed is much more of a pain
than
preventing it.

Hope this clarifies;

-Amy