State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912
Since the state names are unique, is there any reason not to make that
field the primary key?
Yes, there are several good reasons. First, you might find some spelling
errors. You might come out with a French version. Or, someone wants the
names to be spelled in German, or whatever tickles your fancy. If you use a
autonumber, and then start using a description for the State in place of the
name, then your database can continue to function without modification.
As others mentioned, there is much philosophy and strong views on each side
of the camp (natural keys vs autonumber keys).
My view is that when you relate a table, I simply want the database to
please give me a relation between those two tables I specify. At that
point, I give NOT one hoot about what field is used, and in fact I don't
even want to waste my brain power coming up with a field to create the
relaton. I want a one to many relaton. What you do after that is your
business!!
Here is my rant on this subject. It also explains why you don't every want
to expose the autonumber to the end user.
Be forewarned...this is a old post..and is a rant..but, it gives you the
idea of how much fervor can go into the subject...
----------------
Why would you EVE"R care what id ms-access uses for the relation?
Do you care what memory segment word gets loaded into? Do you
care if it is memory segment 32, or 8192?
Are you now to ask users with a prompt as to what memory locaton that your
word document will load into? Who cares..that junk is for comptuers to deal
with...not humans...
Who cares about a number you, and your users will NEVER see?
An autonumber is some mechanistic to generate a number. To you and me, all
we care about is that we have a relation from customers to customers invoice
table. Do we really care, or have to know what number is used?
Really, when word loads into memory, we don't care about the number used for
the memory location. Really, when ms-access has a relation between customers
and the invoice file...again we don't give a hoot about what number is used.
Me, or you never sees the segment number when word loads, and we as users
will never see the autonumber either.
These numbers are NOT for humans to see.....
There is a ZILLION kinds of internal numbers that your computer uses all day
to function. Why do you care what memory segment numbers the computer used
to load ms-access, or ms-word? Why care?
If your folks can see, or use those autonumbers...then that is your problem
with autonumbers.
The real wrong being done here is that users can see, or use the
autonumbers. I mean, do you want ms-word to start showing you the memory
segment numbers it uses to load a document into memory? It would be crazy to
force users to deal with memory segment numbers when using word.
With ms-access, YOU NOW are the software developer. So, just like those
developers who creased word, they don't show users what memory location the
documents load into. You as a developer has a responsibly to NOT LET USERS
see the autonumber.
If you need some number for your users, then you need to write your own
custom code that generates those numbers for human consumption (say, things
like invoice number etc.). You do NOT want to use the invoice number for
relations etc (you still use a internal autonumber, and that way you don't
even care if the invoice has a invoice number, or perhaps you wait a
specified time until a invoice number is given. Either way, you can still
have your relational database function...but behind the scenes it is using a
autonumber).
Your database should not crap out just because you don't have a order number
handy. Who even cares if you enter a order number, or not? Why should your
database stop function if you don't enter a order number? Even if you change
the order number, again..why should your database not work? Maybe you need
to delete the order number? (again, what on planet earth does deleing some
arbitrary number like the order number HAVE ANYTHING to do with building a
functional relation between two tables? How possibility does these two
separate concepts have anything in common?).
You users should NEVER EVER see a autonumber.
You mistake here is to try and let humans see, or even refer to, or use the
autonumber in any way. Autonumbers are NOT to be given meaning by
humans...but ONLY to your software.
Who cars if you have a order number, or not? What does the fact of having a
order number have to do with your database to functionally correctly? If you
want to require that a order number HAS to be entered, then make the order
number a required field, but that simple stupid order number HAS NOTING to
do with setting up a relation between two tables.
Setup your relation between tables with internal numbers, and your database
will JUST WORK REGARDLESS of what fields, and things you decide to store as
data. Do not go and attached some number out of the blue like a stupid order
number to build relations between your tables. Can you imagine if products
like QuickBooks, or even products like ms-word exposed internal numbers used
for relations and other internal numbers as to how the software will
function? Software uses ZILLIONS AND ZILLIONS of internal numbers and
pointers to function.
Now that YOU ARE the software developer, it is up to you to hide these
numbers. You can expose these internal numbers (like autonumbers), but that
is just rude, and just services to torture your users. Hide all the internal
number stuff....every other developer before you did this....
Why expose users to the exhaust pipe of a car when all they want to do is
drive? Software is a machine you build. Build it...make it work, and then
give it to your users. Users do NOT need to know about the kinds of teeth
used in the gears for the car...
So, the two concepts of how relations works is that many of us just believe
that setting up a relation between two tables is a conceptual idea, and HAS
NOTING to do with the data that you need to store. Others would disagree on
this concept...
By the way, there are some STRONG augments for using natural keys. For
example, if I adopt a natural key in my data, then can freely move it
between TWO DIFFERENT systems that respect this approach. (of course, you
have to have those two systems respect that approach!!).