Primary Key/opinion/question

  • Thread starter Thread starter SStory
  • Start date Start date
S

SStory

There is something that I've wondered about for a while. I wonder if others
handle things similar to me or if I am missing something.

In designing various databases, I have come to use and autonumber for the
primary key for the majority of them because users tend to need to change
them if it is something useful like SSN or whatever, and this is a problem
due to other tables using as a foreign key, etc.

I know there is cascading updates and deletes in access, but just wondering,

Am I right to just use autonumbering for primary keys and store these
would-be primary keys in a non key field to avoid the issue?

Thanks for your input.

Shane
 
I going to repost what I wrote on this a few days ago...

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.....

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).

It is a 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.

So, yes...Shane, your line of thinking makes a lot of sense. Further, often,
some things like invoice number, or other numbers are not needed until a
given time. For example, my system does not generate invoice numbers until a
user actually tries to print. Lots of editing, and other things can occur,
and these things occur without a invoice number. If no invoice gets printed,
then no invoice number is generated, but that does not effect the relational
operation of my software. It also means that we don't have any kind of
problem of numbers and "gaps" missing that people typically complain about
when using autonumbers. We get those gaps etc since a lot of data processing
does need relational data...but then again...with autonumbers...who cares!
 
Am I right to just use autonumbering for primary keys and store these
would-be primary keys in a non key field to avoid the issue?
Click to expand...

This question can (and maybe will) generate a lot of heated
discussion. It can almost become a religous issue - "Heretic! Heretic!
He doesn't use Autonumbers! To the stake with him! <g>"

Many (probably a mojority) of developers use the Autonumber as a
surrogate key. Some (myself among them) will use a "natural" key if
there is a good one - "good" being unique, reasonably stable, and
reasonably short. For example, there's no point IMO of putting an
Autonumber into a table of US States, or even US States and Canadian
provinces - there is a unique, short, and rarely changed two-letter
state code which makes a perfectly good natural key.

If you do put other "candidate keys" in a separate field, it's
probably a good idea to put a unique Index on the field (or
combination of fields), since the one bad thing an autonumber does is
to make otherwise-identical records non-duplicates.
 
I would one would hate to see what would happen if this
was something Albert felt strongly about.

By the way, how can I get to see the memory allocation
when word loads a document? ;-)
 
Back
Top