Question on primary key

[Ron]

Hi All,

Okay, I've read previous suggestions about not showing the primary key to
the user of forms. If a number is to be shown (let's say, customer number)
it should be generated independently of the primary key (which is
autonumbered and no dups). I think I've gotten that all accomplished.

However, now how should I link files, or perform query's? Should these
types of things be based on the customer number I generate or should I use
the primary key still? I'm confused (which, by the way, is a fairly normal
state for me). Is there something wrong with using my own number that I've
generated instead of using the primary key? Is there some advantage to
using one over the other?

TIA,
ron

[salad]

Ron wrote:

Hi All,

Okay, I've read previous suggestions about not showing the primary key to
the user of forms. If a number is to be shown (let's say, customer number)
it should be generated independently of the primary key (which is
autonumbered and no dups). I think I've gotten that all accomplished.

However, now how should I link files, or perform query's? Should these
types of things be based on the customer number I generate or should I use
the primary key still? I'm confused (which, by the way, is a fairly normal
state for me). Is there something wrong with using my own number that I've
generated instead of using the primary key? Is there some advantage to
using one over the other?

TIA,
ron

Let's say you have a field called ID, type autonumber, and CustomerID,
type number (long) manually created in the CustomerTable. In the Orders
table, you normally would use the ID field to link the two.

You CAN show the customerid to the user, if autonumber. The problem is
that for the most part nobody cares about the customer number...they are
interested in the customer name. But there is no reason you can't
display it. Autonumbers can have sequence breaks in them. That is why
people usually don't display them...they are very usefull for linking a
primary table to child/associated tables.

There really is no need to have an autonumber in your situation if you
manully generate/create the id. I suppose you could check, when you
enter the customerID, in the BeforeUpdate event of the customerID, to
veryify it does not already exist in the customer table. If it does,
and you can't have duplicates, warn the user and cancel it.

[pks]

Ron,

Do your linking via the PK. Nothing wrong with having the customer
number as indexed, no duplicates but you don't want the database to
rely on it for any internal functions. Don't let them even KNOW ABOUT
your PK if you can help it, and by all means don't put it on your
forms.

Don't know about the official reason, but my personal favorite is a
simple one. Users need a number to call their own. Otherwise, at some
point you will find that the users have assigned some importance to the
PK. Then there will be some user issue, such as a customer not liking
their customer number, or a user wanting to change it because they
mistyped it in a Word document that they mailed to the customer, or a
desire to change the format of the customer number, wanting to append
an "A" to a numeric autonumber field, or some other such thing. And
then you have the user telling you that your PK is "wrong" and needs to
be changed.

This is a lot easier to do on a single field unrelated to any of your
other tables. It's a much bigger deal if you have multiple other
tables that link to your customer number as a foreign key. Otherwise,
you might find yourself making enough changes to justify building an
entire routine to cycle through all of the related tables to change the
customer number in that table. And that's assuming you don't have to
change the data type! Trust me, I've been through this. I love the
users--they keep me in business--but there's no limit to what they can
decide they need.

Setting up a relationship with cascade updates might help, but I don't
like them myself. And you'd still have to delete relationships and
then rebuild them if a user change requires you to change a data type.

[Tim Marshall]

salad wrote:

There really is no need to have an autonumber in your situation if you
manully generate/create the id.
Note to file: There are two vociferous schools of argument concerning
"natural" PKs which you are suggesting here and sequence generated PKs.
Arguments between the two sides can reach epic proportions.
I suppose you could check, when you
enter the customerID, in the BeforeUpdate event of the customerID, to
veryify it does not already exist in the customer table. If it does,
and you can't have duplicates, warn the user and cancel it.

It would be far wiser, IMO, to incorporate such a check as part of the
database structure. This can be done by either making the customerID a
primary key, in which case Jet will not allow duplicates or by indexing
the customerID field allowing no duplicates (see the field property in
the table design view).

Anything you can do to utilize table level rules is a good thing IMO as
it reduces the need to write code. Anything that is a strutural part of
your data should, IMO, be enforced if at all possible, at table level.

Such errors cn be trapped at the form level on error event, but that may
be a bit too advanced at this time for Ron at his current knowledge
level... 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[salad]

Tim Marshall wrote:

salad wrote:

There really is no need to have an autonumber in your situation if you
manully generate/create the id.

Note to file: There are two vociferous schools of argument concerning
"natural" PKs which you are suggesting here and sequence generated PKs.
Arguments between the two sides can reach epic proportions.

As far as I'm concerned, an autonumber is always part of my table
structure. It's a pita to always be checking, when you add a record and
you generate the key, to see if it already exists.

I suppose you could check, when you enter the customerID, in the
BeforeUpdate event of the customerID, to veryify it does not already
exist in the customer table. If it does, and you can't have
duplicates, warn the user and cancel it.

It would be far wiser, IMO, to incorporate such a check as part of the
database structure. This can be done by either making the customerID a
primary key, in which case Jet will not allow duplicates or by indexing
the customerID field allowing no duplicates (see the field property in
the table design view).

If only the error message were "pretty". Validation error messages
ususally don't look nice...in fact a bit nastry.
Anything you can do to utilize table level rules is a good thing IMO as
it reduces the need to write code. Anything that is a strutural part of
your data should, IMO, be enforced if at all possible, at table level.
True. But I like to warn the users nicely before I cancel and action.
Such errors cn be trapped at the form level on error event, but that may
be a bit too advanced at this time for Ron at his current knowledge
level... 8)

[Ron]

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:e0**********@coranto.ucs.mun.ca...

salad wrote:

There really is no need to have an autonumber in your situation if you
manully generate/create the id.
Note to file: There are two vociferous schools of argument concerning
"natural" PKs which you are suggesting here and sequence generated PKs.
Arguments between the two sides can reach epic proportions.

I suppose you could check, when you enter the customerID, in the
BeforeUpdate event of the customerID, to veryify it does not already
exist in the customer table. If it does, and you can't have duplicates,
warn the user and cancel it.

It would be far wiser, IMO, to incorporate such a check as part of the
database structure. This can be done by either making the customerID a
primary key, in which case Jet will not allow duplicates or by indexing
the customerID field allowing no duplicates (see the field property in the
table design view).

Anything you can do to utilize table level rules is a good thing IMO as it
reduces the need to write code. Anything that is a strutural part of your
data should, IMO, be enforced if at all possible, at table level.

Such errors cn be trapped at the form level on error event, but that may
be a bit too advanced at this time for Ron at his current knowledge
level... 8)

Actually, I've got that I think. Rather than have a user assign the
CustomerID, I increment the CustomerID by Me!CustomerID =
DMax("CustomerID", "tblCustomer") + 1. I've got that error trapped in
Form_error so if 2 users get the same number, the second one hops back to
the dmax again and gets another number. That all seems to work fine as far
as I can tell.

However, I just don't really understand why I need to do all this when that
primary key is autonumbered. People have said "but, autonumber can skip
numbers, or become negative
', or whatever. Seems like a flaw in autonumber then, eh? So, I need to do
all this kludging stuff because of flaws? Is that why it's better to "never
show a pk to the user"? And, if that's the case, then why have a
autonumbered pk in the first place. why can't my dmaxed CustomerID be the
pk? Will it also weird out like an autonumbered pk? I'm trying to set this
up for the future, so I don't have to redesign this later because of some
other oddity, you know?

Sometimes a little knowledge is a very dangerous thing...
ron

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Lyle Fairfield]

"Ron" <ro*******************@earthlink.com> wrote in
news:Nm***************@newsread1.news.pas.earthlin k.net:

However, I just don't really understand why I need to do all this when
that primary key is autonumbered. People have said "but, autonumber
can skip numbers, or become negative
', or whatever. Seems like a flaw in autonumber then, eh? So, I need
to do all this kludging stuff because of flaws? Is that why it's
better to "never show a pk to the user"? And, if that's the case,
then why have a autonumbered pk in the first place. why can't my
dmaxed CustomerID be the pk? Will it also weird out like an
autonumbered pk? I'm trying to set this up for the future, so I don't
have to redesign this later because of some other oddity, you know?

Sometimes a little knowledge is a very dangerous thing...

Almost no knowledge is a very stupid thing.

--
Lyle Fairfield

[Bernard Peek]

In message <a4***************@newsread1.news.pas.earthlink.ne t>, salad
<oi*@vinegar.com> writes

Tim Marshall wrote:

salad wrote:

There really is no need to have an autonumber in your situation if
you manully generate/create the id.

Note to file: There are two vociferous schools of argument
concerning "natural" PKs which you are suggesting here and sequence
generated PKs. Arguments between the two sides can reach epic
proportions.

As far as I'm concerned, an autonumber is always part of my table
structure. It's a pita to always be checking, when you add a record
and you generate the key, to see if it already exists.

But if you can't generate a key from the data, before adding an
autonumber, then you shouldn't create the new record anyway. Using an
autonumber just hides the fact that your data is bad. There are lots of
times when you really don't have any alternative to using some sort of
artificial identifier, but adding one where it isn't necessary just adds
one more thing that can go wrong. If you do add one you still have to do
that uniqueness check on the real data.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

[Ron]

"Ron" <ro*******************@earthlink.com> wrote in
news:Nm***************@newsread1.news.pas.earthlin k.net:
<snip>

Sometimes a little knowledge is a very dangerous thing...

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119...
Almost no knowledge is a very stupid thing.

--
Lyle Fairfield

So, like...Lyle. Don't worry, bud. You've got time to turn it around.
Just don't give up yet.

::grin::
ron

[Bernard Peek]

In message <Nm***************@newsread1.news.pas.earthlink.ne t>, Ron
<ro*******************@earthlink.com> writes

Actually, I've got that I think. Rather than have a user assign the
CustomerID, I increment the CustomerID by Me!CustomerID =
DMax("CustomerID", "tblCustomer") + 1. I've got that error trapped in
Form_error so if 2 users get the same number, the second one hops back to
the dmax again and gets another number. That all seems to work fine as far
as I can tell.

However, I just don't really understand why I need to do all this when that
primary key is autonumbered. People have said "but, autonumber can skip
numbers, or become negative
', or whatever. Seems like a flaw in autonumber then, eh? So, I need to do
all this kludging stuff because of flaws? Is that why it's better to "never
show a pk to the user"? And, if that's the case, then why have a
autonumbered pk in the first place. why can't my dmaxed CustomerID be the
pk? Will it also weird out like an autonumbered pk? I'm trying to set this
up for the future, so I don't have to redesign this later because of some
other oddity, you know?

Sometimes a little knowledge is a very dangerous thing...

I really must get my web site fixed so I can just post a pointer to this
stuff. I keep having to repeat it in comp.databases.theory too.

From a theoretical standpoint my argument over there has always been
that it's fine to use an artificial key provided you understand the
problems it can introduce, and you are prepared to deal with them.

First remember why you need a key, it's what you use to identify one
particular record in the table. That's why it has to be unique, but
uniqueness is not the only thing required. Not only does the key have to
be unique, you have to know exactly which unique key will retrieve data
about each object that the record describes. There has to be a precisely
defined 1:1 mapping between the real-world objects and the data that is
supposed to describe them. If you know the value of the key you should
be able to tell unambiguously which object it refers to.

The problem with artificial keys is that there is no intrinsic
connection between the value of the key and the identity of the object
that it is supposed to identify. It's up to the database designer to
manage that 1:1 mapping. It's usually done outside the database itself.
When the US government uses a sophisticated version of the autonumber
system to give each person a Social Security Number it hands each person
their number and tells them to remember it. That's one way of managing
that mapping.

If you use an artificial key in a database you take on the
responsibility for managing the mapping between key values and the
things they identify. When you create a new record you first have to
make sure that the object you want to describe isn't already in the
database. That often means that you have to take whatever information
you have about it and search the database to make sure that there isn't
already a record. If you only rely on the autonumber filed to ensure
uniqueness there is nothing stopping you from having three kids called
Amy 1, Amy 2 and Amy 3. But if you need to check for uniqueness why not
just give them unique names, and use their names as the key?

So the problem is that adding an artificial key doesn't save you any
processing time when you add a new record, it just adds one more thing
you need to do and one more thing that can break. And after you have
created your identifier someone has to make sure that this label you
have created remains attached to one-and-only-one object.

There are sometimes valid reasons for using artificial keys internally
to the database. When the fields that make up the natural key are large
it becomes inconvenient to use them as foreign keys in other tables, so
you create an ID field and use that instead. This is the type of
artificial key that you shouldn't tell the users about, because if you
do that you lose control over it. When disk space was expensive saving
disk space was important. Now, making systems simpler and more reliable
is likely to be more important than saving a few terabytes. So if you
can use natural keys without affecting performance too badly then I
think you should do so. Use artificial keys when you must, natural keys
when you can.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

[Bernard Peek]

In message <11**********************@i40g2000cwc.googlegroups .com>, pks
<pk*******@yahoo.com> writes

Don't know about the official reason, but my personal favorite is a
simple one. Users need a number to call their own. Otherwise, at some
point you will find that the users have assigned some importance to the
PK.
That's an important point. It can be difficult to stop them doing that.
When I had to create an industry-wide numbering scheme I made sure that
numbers were issued to different companies in small blocks so that it
was impossible to guess which number had been issued to which company.
You sometimes need to work hard to stop people making unwarranted
assumptions about patterns they see in the data.
Then there will be some user issue, such as a customer not liking
their customer number, or a user wanting to change it because they
mistyped it in a Word document that they mailed to the customer, or a
desire to change the format of the customer number, wanting to append
an "A" to a numeric autonumber field, or some other such thing. And
then you have the user telling you that your PK is "wrong" and needs to
be changed.

Good primary keys are, in order of preference either a) enforced by the
laws of physics b) issued and rigidly controlled by the database
designer or c) issued and rigidly controlled by some trusted authority
(as is usually the case with SSNs for example.)

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

[usenet]

Bernard Peek <ba*@shrdlu.com> wrote:

In message <a4***************@newsread1.news.pas.earthlink.ne t>, salad
<oi*@vinegar.com> writes

Tim Marshall wrote:

salad wrote:

There really is no need to have an autonumber in your situation if
you manully generate/create the id.
Note to file: There are two vociferous schools of argument
concerning "natural" PKs which you are suggesting here and sequence
generated PKs. Arguments between the two sides can reach epic
proportions.

As far as I'm concerned, an autonumber is always part of my table
structure. It's a pita to always be checking, when you add a record
and you generate the key, to see if it already exists.

But if you can't generate a key from the data, before adding an
autonumber, then you shouldn't create the new record anyway. Using an
autonumber just hides the fact that your data is bad. There are lots of
times when you really don't have any alternative to using some sort of
artificial identifier, but adding one where it isn't necessary just adds
one more thing that can go wrong. If you do add one you still have to do
that uniqueness check on the real data.

Exactly! I have (well I'm creating) a database of photographs. One
of the tables is a 'Film' table listing each roll of film. They have
numbers of the form YYYY.NN where YYYY is the year and NN is a serial
number. The primary key is this number, I want to be protected
against trying to create another entry with the same number.

--
Chris Green

[David W. Fenton]

"Ron" <ro*******************@earthlink.com> wrote in
news:iA***************@newsread1.news.pas.earthlin k.net:

However, now how should I link files, or perform query's? Should
these types of things be based on the customer number I generate
or should I use the primary key still?

Customer Numbers that a human being is forced to use are an artifact
of the early days of computer systems where it was inefficient and
difficult to program user interfaces that made it easy for users to
find data. In generaly, I would say that you should never force a
user to have to know a customer number in order to figure out which
customer they are looking for -- you should instead provide the user
with a search interface that allows them to find the correct
customer using the real attributes of that customer, the name,
address, etc. For that reason, I would never have a derived number.

Of course, all that is an ideal, and there are real situations where
you do need a generated number because of outside dependencies, the
insistence of the users on having it because they've always had it,
or that the data is such that you really do need some unique data to
distinguish otherwise identical records (that are identical for
reasons beyond your control).

But my recommendation is to start from a design that hides all that
from the user, and allows the user to find information using
real-world attributes of the data being represented.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bernard Peek]

In message <4433bb1d.0@entanet>, us****@isbd.co.uk writes

But if you can't generate a key from the data, before adding an
autonumber, then you shouldn't create the new record anyway. Using an
autonumber just hides the fact that your data is bad. There are lots of
times when you really don't have any alternative to using some sort of
artificial identifier, but adding one where it isn't necessary just adds
one more thing that can go wrong. If you do add one you still have to do
that uniqueness check on the real data.

Exactly! I have (well I'm creating) a database of photographs. One
of the tables is a 'Film' table listing each roll of film. They have
numbers of the form YYYY.NN where YYYY is the year and NN is a serial
number. The primary key is this number, I want to be protected
against trying to create another entry with the same number.

This is another situation where it's difficult to work out what a
"natural" key might be. If you use 35mm film then the frames are
numbered, which gives you some chance of creating a key. But if you have
mounted slides the number isn't visible. I fixed that by finding labels
small enough to stick on a slide-mount and each slide now has a
permanent label attached.

For 35mm print films you might be able to label each strip of negatives.
It's easy to print consecutively numbered labels. The full key could
then be the strip-number and the frame number printed along the edge of
the film. You could add the year too if you wanted but it's not strictly
necessary if the strip numbers are consecutive, or at least unique. Of
course if you keep the whole film as a single strip then this works out
to be the same as your scheme.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.