Should I use auto-number for my tables?

[alfaista]

Thanks much!

Just wanted to say a big thank you to you all for this discussion! Yes, I
can get out there and google and find even more, but I needed a few good
discussion points and found them all on this one thread, thanks!
I have issues with AutoNumbers, especially when someone decides to use them
extensivley. Such as having used for all keys in all tables. I.e., the
product table has an autonumber for the key, that links to an autonumber for
the producer of said product, that links to an autonumber for a point of
contact for said producer. The product table also links to itself via
another level table that shows which sub-products may belong to an overall
product. This level table is a series of 10 integers, all autonumbers from
the products table.
I find this mind boggling, and I was told that the data was all properly
"normalized".
Am I missing the picture here?
An answer would be great, but I may have developed an opinion from this
thread as it is. Any more discussion is welcome.

Thanks again.

 

[Bruce]

Just remember that you never need to see autonumbers. The database uses
them, but you can ignore them once the relationships are established.
Autonumber to autonumber relationships would be problematic at best, by the
way.

 

[John Vinson]

Autonumber to autonumber relationships would be problematic at best, by the
way.

or to be more brutal, but accurate: autonumber to autonumber
relationships are IMPOSSIBLE to use.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Bruce]

Well, I thought so, but there could have been some obscure corner of
relational database theory where autonumber to autonumber can live, like
there are places under the sea where creatures live on top of sulfur vents,
so I didn't want to make an unequivocal statement.

 

[Jamie Collins]

there could have been some obscure corner of
relational database theory where autonumber to autonumber can live

Create a table which includes an autonumber, insert a row and it gets
an auto-generated value, say 1. Delete the row run the insert a second
time using the same details as before and it gets another value, say
2. The same row/entity has two different values so it cannot be a key
and is non-relational. Two autonumbers doesn't get any close to being
relational <g>.

If you are using autonumber as a key in DRI, you'd have to be
meticulous about keeping backups i.e. you'd have great difficulty
regenerating the autonumber values (if you use a true natural key like
a VIN or ISBN you can always verify against the entity in reality). An
autonumber is undoubtedly useful for ensuring uniqueness i.e. you just
need it to be different for all rows and alternative values may easily
be substituted. But using it in 'relationships' could be asking for
trouble.

Jamie.

--

 

[Brendan Reynolds]

Well, I thought so, but there could have been some obscure corner of
relational database theory where autonumber to autonumber can live, like
there are places under the sea where creatures live on top of sulfur
vents,
so I didn't want to make an unequivocal statement.

E. F. Codd was a smart guy, but he was not as creative as evolution / mother
nature / god / insert the natural force or diety of your choice here !

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.