View Single Post
  #13  
Old September 23rd, 2005, 10:09 PM
Amy Blankenship
external usenet poster
 
Posts: n/a
Default


"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