Auto Number mysql

**Jeff North** · Nov 10 '06, 09:15 PM

Re: Auto Number mysql

On 7 Nov 2006 02:58:49 -0800, in mailing.databas e.mysql
S.Dickson@shos. co.uk
<1162897129.896 632.30890@e3g20 00cwe.googlegro ups.comwrote:

It can be done but why bother.
The function of auto-increment/autonumber fields is to ensure that the
record within the table is unique. The user should never see this
value as the information is meaningless.

The next question that is usually asked is:
I've set a auto-increment/autonumber on a field and there are gaps in
the numbers, how do I fix this.
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

**Gordon Burditt** · Nov 10 '06, 10:05 PM

Re: Auto Number mysql

>The function of auto-increment/autonumber fields is to ensure that the

>record within the table is unique. The user should never see this
>value as the information is meaningless.

There are a lot of numbers which are both meaningless by themselves
but act as a database key to meaningful information. Customers
might need to see these. For example: invoice numbers, account
numbers, order numbers and (almost) social security numbers.

>The next question that is usually asked is:
>I've set a auto-increment/autonumber on a field and there are gaps in
>the numbers, how do I fix this.

Don't fix it, worship it. If you need to put picky constrants on
these, like inserting a date as part of an order number, don't use
auto-increment, use some other method (triggers, maybe)?

**Jeff North** · Nov 11 '06, 03:35 AM

Re: Auto Number mysql

On Fri, 10 Nov 2006 22:20:21 -0000, in mailing.databas e.mysql
gordonb.vxi3h@b urditt.org (Gordon Burditt)
<12l9up5t60c039 1@corp.supernew s.comwrote:

>| >The function of auto-increment/autonumber fields is to ensure that the
>| >record within the table is unique. The user should never see this
>| >value as the information is meaningless.
>|
>| There are a lot of numbers which are both meaningless by themselves
>| but act as a database key to meaningful information. Customers
>| might need to see these. For example: invoice numbers,

Generated AFTER the information has been submitted.

>| account numbers,

Not a candidate for auto-numbers.

>| order numbers

Again, not a candidate for auto-numbers.

>| and (almost) social security numbers.

Again, not a candidate for auto-numbers.

>| >The next question that is usually asked is:
>| >I've set a auto-increment/autonumber on a field and there are gaps in
>| >the numbers, how do I fix this.
>|
>| Don't fix it, worship it. If you need to put picky constrants on
>| these, like inserting a date as part of an order number, don't use
>| auto-increment, use some other method (triggers, maybe)?

---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

**Gordon Burditt** · Nov 12 '06, 12:15 AM

Re: Auto Number mysql

>>| >The function of auto-increment/autonumber fields is to ensure that the

>>| >record within the table is unique. The user should never see this
>>| >value as the information is meaningless.
>>|
>>| There are a lot of numbers which are both meaningless by themselves
>>| but act as a database key to meaningful information. Customers
>>| might need to see these. For example: invoice numbers,

>
>Generated AFTER the information has been submitted.
>

>>| account numbers,

>
>Not a candidate for auto-numbers.

Why not? For banks it would be a bad idea because the numbers are
easily guessed. For dentists or plumbers or most other businesses
that isn't an issue.

>>| order numbers

>
>Again, not a candidate for auto-numbers.

Why not? Maybe not for the way your business does things,
but why wouldn't it work?

>>| and (almost) social security numbers.

>
>Again, not a candidate for auto-numbers.

True, largely because you will never get into a position to
issue them.

**Jeff North** · Nov 12 '06, 04:25 AM

Re: Auto Number mysql

On Sun, 12 Nov 2006 00:39:01 -0000, in mailing.databas e.mysql
gordonb.nc8cp@b urditt.org (Gordon Burditt)
<12lcr95jqrcqaa d@corp.supernew s.comwrote:

>| >>| >The function of auto-increment/autonumber fields is to ensure that the
>| >>| >record within the table is unique. The user should never see this
>| >>| >value as the information is meaningless.
>| >>|
>| >>| There are a lot of numbers which are both meaningless by themselves
>| >>| but act as a database key to meaningful information. Customers
>| >>| might need to see these. For example: invoice numbers,
>| >
>| >Generated AFTER the information has been submitted.
>| >
>| >>| account numbers,
>| >
>| >Not a candidate for auto-numbers.
>|
>| Why not? For banks it would be a bad idea because the numbers are
>| easily guessed. For dentists or plumbers or most other businesses
>| that isn't an issue.

User-friendliness of the application.
Would you want to remember numbers like: 54132685796143 or JSMITH55. I
know businesses prefer to use an easily remembered code for customers.

Here in Australia we have a medical card with a serial number. I'd
image most databases would use this number. Again it is not an
auto-generated number.

>| >>| order numbers
>| >
>| >Again, not a candidate for auto-numbers.
>|
>| Why not? Maybe not for the way your business does things,
>| but why wouldn't it work?

....and if the PO contained alphanumerics?? ??

>| >>| and (almost) social security numbers.
>| >
>| >Again, not a candidate for auto-numbers.
>|
>| True, largely because you will never get into a position to
>| issue them.

Not to mention the privacy issues that are controlled by legislation.
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

**Gordon Burditt** · Nov 12 '06, 05:15 AM

Re: Auto Number mysql

>Here in Australia we have a medical card with a serial number. I'd

>image most databases would use this number. Again it is not an
>auto-generated number.

You mean a PLUMBER is going to ask for your medical card?

I'm not familiar with your system, but my bet is that corporations
don't have medical cards but they DO have toilets that might need
fixing, so they might hire the plumber. And wouldn't this cause
problems with the privacy laws you mention later?

Even doctors in the USA seem to have their own account numbers for
patients, even though they also ask for SSNs also. I've never been
asked for the account number by a doctor but they seem to use it
internally for billing. An ACCOUNT doesn't always map nicely to a
person when someone else is assigning the numbers..

>>| >>| order numbers
>>| >
>>| >Again, not a candidate for auto-numbers.
>>|
>>| Why not? Maybe not for the way your business does things,
>>| but why wouldn't it work?

>
>...and if the PO contained alphanumerics?? ??

It's not a problem because the application is *ISSUING* the order numbers,
and it doesn't happen to put any alphanumerics in them. Why are they mandatory?
Granted, it's an issue if this application is replacing an older one that did
use alpha in PO numbers, but not if it's supposed to be a NEW system.

>>| >>| and (almost) social security numbers.
>>| >
>>| >Again, not a candidate for auto-numbers.
>>|
>>| True, largely because you will never get into a position to
>>| issue them.

>
>Not to mention the privacy issues that are controlled by legislation.
>---------------------------------------------------------------
>jnorthau@yourp antsyahoo.com.a u : Remove your pants to reply
>---------------------------------------------------------------

**Jeff North** · Nov 12 '06, 07:55 PM

Re: Auto Number mysql

On Sun, 12 Nov 2006 05:36:28 -0000, in mailing.databas e.mysql
gordonb.ace4j@b urditt.org (Gordon Burditt)
<12ldcms76hh041 c@corp.supernew s.comwrote:

You've dishonestly snipped my explanation. The medical card number was
just a further example.

If you wish to use auto-increment/numbers for inappropriate fields
then that is your prerogative.

I use the simple rule that auto-increment/numbers are generated by the
database and I shouldn't reply on/worry about the numbers.

[snip 2 end]
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

**Skarjune** · Nov 14 '06, 04:05 PM

Re: Auto Number mysql

Jeff has an understandable position, but perhaps oversimplified. The
issue is not just whether or not an auto ID can ever be shown to a
user; it's also about data modeling strategy. The dichotomy is between
domain (aka natural) keys and automatic keys, and the trade-offs are
between user-friendliness and database efficiency.

For example, Acme Inc. is not Acme Int. so we could have company IDs
based on their names. But what happens when Acme Inc. changes it's name
to Acme & Sons?

We could stupidly call them Acme Inc. and ignore reality or change the
ID to Acme & Sons. When we do that, we have to Update Cascade to all
related records and rebuild indexes. Not hard to do, but it is a
performance hit on a big system. Plus, there is the risk that
non-heterogeneous batches with the database don't get the Update
Cascade, so the ID breaks in a batch job.

Some database designers would rather assign Acme Inc. a number and when
they change to Acme & Sons, it's just an attribute change in the
Company domain with no effect anywhere else in the system nor upon the
non-heterogeneous batches.

The question remains: Should the user see the number? Sometmes No,
Sometimes Yes. It depends upon the needs of the user and the workflow
of the system.

Like Jeff, all database professional DO need to take a position on this
and be consistent in your work. There is not necessarily one right
answer. You can see the full extant of the debate on the original
WardsWiki:

http://c2.com/cgi/wiki?AutoKeysVersusDomainKeys

P.S. if you have never seen the Original Wiki, check it out:

http://c2.com/cgi/wiki

-David Hedrick Skarjune

Jeff North wrote:

On Sun, 12 Nov 2006 05:36:28 -0000, in mailing.databas e.mysql
gordonb.ace4j@b urditt.org (Gordon Burditt)
<12ldcms76hh041 c@corp.supernew s.comwrote:
>

| >Here in Australia we have a medical card with a serial number. I'd
| >image most databases would use this number. Again it is not an
| >auto-generated number.
|
| You mean a PLUMBER is going to ask for your medical card?

>
You've dishonestly snipped my explanation. The medical card number was
just a further example.
>
If you wish to use auto-increment/numbers for inappropriate fields
then that is your prerogative.
>
I use the simple rule that auto-increment/numbers are generated by the
database and I shouldn't reply on/worry about the numbers.
>
[snip 2 end]
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

**Jeff North** · Nov 14 '06, 08:55 PM

Re: Auto Number mysql

On 14 Nov 2006 08:26:07 -0800, in mailing.databas e.mysql "Skarjune"
<dhs@wordimage. com>
<1163521567.062 709.3560@f16g20 00cwb.googlegro ups.comwrote:

>| Jeff has an understandable position, but perhaps oversimplified. The

:-) I didn't want to get into the 'theory debate'.

>| issue is not just whether or not an auto ID can ever be shown to a
>| user; it's also about data modeling strategy. The dichotomy is between
>| domain (aka natural) keys and automatic keys, and the trade-offs are
>| between user-friendliness and database efficiency.

[snip]

>| The question remains: Should the user see the number? Sometmes No,
>| Sometimes Yes. It depends upon the needs of the user and the workflow
>| of the system.

Correct. You, as the db developer, must look at the business rules,
the most correct design of the database and db normalisation.

What works in one business environment will not necessarily work in
another environment (as debated within the link you provided).

>| Like Jeff, all database professional DO need to take a position on this
>| and be consistent in your work. There is not necessarily one right
>| answer. You can see the full extant of the debate on the original
>| WardsWiki:
>| http://c2.com/cgi/wiki?AutoKeysVersusDomainKeys

Interesting debate. Thanks for the link.

>| P.S. if you have never seen the Original Wiki, check it out:
>| http://c2.com/cgi/wiki
>|
>| -David Hedrick Skarjune

[snip]
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

**Gordon Burditt** · Nov 16 '06, 01:35 AM

Re: Auto Number mysql

>Jeff has an understandable position, but perhaps oversimplified. The

>issue is not just whether or not an auto ID can ever be shown to a
>user; it's also about data modeling strategy. The dichotomy is between
>domain (aka natural) keys and automatic keys, and the trade-offs are
>between user-friendliness and database efficiency.

Suppose you need to generate invoice numbers. You are replacing
an old system which involved pre-printed invoice forms with sequence
numbers put on them by the printer. How would you do it? It seems
an obvious application of an auto-increment field. And it seems a
waste to have an internal invoice id and an external invoice
identifier when there's no reason for two of them.

Incidentally, nobody tries to memorize invoice numbers: the company
issuing them looks them up on its computer, and the purchasing
company presumably doesn't have so many orders with this company
outstanding at once that looking for a piece of paper or looking
it up in THEIR accounts payable system is a problem.

>For example, Acme Inc. is not Acme Int. so we could have company IDs
>based on their names. But what happens when Acme Inc. changes it's name
>to Acme & Sons?

This, I think, is one of the reasons why account numbers should not
be derived from names. Account numbers don't get married and change
their names even if the customers do. And there's no necessary
reason why an individual or a company can't have multiple accounts
(Some large companies have individual FedEx accounts for each region.
Lots of individuals have more than one account of the same type at
the same bank (e.g. this is the vacation account, this is the
downpayment on a house account, this is the retirement account,
....) And unless there is a security issue with being able to guess
account numbers (banks, credit card, etc.) I see no harm in assigning
them with an auto-increment field, which is no less insecure than
using a pad of pre-printed, pre-numbered forms.

>We could stupidly call them Acme Inc. and ignore reality or change the
>ID to Acme & Sons. When we do that, we have to Update Cascade to all
>related records and rebuild indexes. Not hard to do, but it is a
>performance hit on a big system.

Yes, it hard to do, because you can't update paperwork in the hands
of the customer, nor paperwork taped onto packages in transit, nor
statements in the mail.

>Plus, there is the risk that
>non-heterogeneous batches with the database don't get the Update
>Cascade, so the ID breaks in a batch job.

>Some database designers would rather assign Acme Inc. a number and when
>they change to Acme & Sons, it's just an attribute change in the
>Company domain with no effect anywhere else in the system nor upon the
>non-heterogeneous batches.

It's not just *database designers*. Banks (and lots of other
companies) already had "account numbers" before the existence of
computers. They aren't artificial artifacts of the database
implementation, they are a key part of the business process. But
I'm saying you can use auto-increment to assign new Domain Keys as
well as AutoKeys.

Regardless of the number of name or address changes by the customer,
the account number stayed put, and the transactions to that account
didn't have to change because of it. But that didn't prevent them
from putting the account number on the billing statement so the
customer could read it over the phone to identify the account in
question.

>The question remains: Should the user see the number? Sometmes No,
>Sometimes Yes. It depends upon the needs of the user and the workflow
>of the system.

Incidentally, there's no necessary reason why the account number
has to be used to log into a web-based ordering system used by the
customer, just because it's visible on a bill. They might have to
enter it once to set up a login for a purchasing agent, if it wasn't
set up when the account was created. Authorized purchasing agents
may come and go, but the account may last much longer.

>Like Jeff, all database professional DO need to take a position on this
>and be consistent in your work. There is not necessarily one right
>answer. You can see the full extant of the debate on the original
>WardsWiki:
>http://c2.com/cgi/wiki?AutoKeysVersusDomainKeys
>
>P.S. if you have never seen the Original Wiki, check it out:
>http://c2.com/cgi/wiki
>
>-David Hedrick Skarjune
>
>Jeff North wrote:

>On Sun, 12 Nov 2006 05:36:28 -0000, in mailing.databas e.mysql
>gordonb.ace4j@b urditt.org (Gordon Burditt)
><12ldcms76hh04 1c@corp.superne ws.comwrote:
>>

>>
>You've dishonestly snipped my explanation. The medical card number was
>just a further example.
>>
>If you wish to use auto-increment/numbers for inappropriate fields
>then that is your prerogative.
>>
>I use the simple rule that auto-increment/numbers are generated by the
>database and I shouldn't reply on/worry about the numbers.
>>
>[snip 2 end]
>---------------------------------------------------------------
>jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
>---------------------------------------------------------------

>

**Jeff North** · Nov 16 '06, 08:05 AM

Re: Auto Number mysql

On Thu, 16 Nov 2006 01:56:10 -0000, in mailing.databas e.mysql
gordonb.zzpt9@b urditt.org (Gordon Burditt)
<12lnh9qsltms62 3@corp.supernew s.comwrote:

>| >Jeff has an understandable position, but perhaps oversimplified. The
>| >issue is not just whether or not an auto ID can ever be shown to a
>| >user; it's also about data modeling strategy. The dichotomy is between
>| >domain (aka natural) keys and automatic keys, and the trade-offs are
>| >between user-friendliness and database efficiency.
>|
>| Suppose you need to generate invoice numbers. You are replacing
>| an old system which involved pre-printed invoice forms with sequence
>| numbers put on them by the printer. How would you do it? It seems
>| an obvious application of an auto-increment field. And it seems a
>| waste to have an internal invoice id and an external invoice
>| identifier when there's no reason for two of them.

This is a good example why auto-numbers should NOT be used. Why?
Let's say the invoices comes in boxes of 500. The person stacking
these boxes isn't too careful about the stacking ordering. The printer
needs a new box of pre-printed invoices. This box starts with number
101500. Some time goes past and the printer needs a new box of
pre-printed invoices. This box starts with the number 101000.

See the problem? Auto-numbered fields (usually) wont let you specify a
starting value that is already less than the maximum entry.

How to fix the 'problem'. Tell the company to dump it's pre-printed
stationary so that the system can 'take care of' the invoice numbers
for them. (It will actually save them money and time in the long run).

---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

**Gordon Burditt** · Nov 17 '06, 12:05 AM

Re: Auto Number mysql

>>| >Jeff has an understandable position, but perhaps oversimplified. The

>>| >issue is not just whether or not an auto ID can ever be shown to a
>>| >user; it's also about data modeling strategy. The dichotomy is between
>>| >domain (aka natural) keys and automatic keys, and the trade-offs are
>>| >between user-friendliness and database efficiency.
>>|
>>| Suppose you need to generate invoice numbers. You are replacing
>>| an old system which involved pre-printed invoice forms with sequence
>>| numbers put on them by the printer. How would you do it? It seems
>>| an obvious application of an auto-increment field. And it seems a
>>| waste to have an internal invoice id and an external invoice
>>| identifier when there's no reason for two of them.

>
>This is a good example why auto-numbers should NOT be used. Why?
>Let's say the invoices comes in boxes of 500. The person stacking
>these boxes isn't too careful about the stacking ordering. The printer
>needs a new box of pre-printed invoices. This box starts with number
>101500. Some time goes past and the printer needs a new box of
>pre-printed invoices. This box starts with the number 101000.
>
>See the problem? Auto-numbered fields (usually) wont let you specify a
>starting value that is already less than the maximum entry.

Did you see the part where I said the pre-printed forms are what
you are REPLACING? And large gaps in the sequence numbers is not
a problem (although re-use of a number is).

Oh, yes, the old method is fine, regardless of how the boxes are
stacked, as long as someone keeps track of the next number to order.
(Somehow banks seem to do that for me, regardless of how I mix up
individual pads of checks.) But we're getting rid of that anyway,
and letting the database allocate new invoice numbers.

>How to fix the 'problem'. Tell the company to dump it's pre-printed
>stationary so that the system can 'take care of' the invoice numbers
>for them. (It will actually save them money and time in the long run).

Fine. How do you allocate invoice numbers the NEW way? I say use
auto-increment. Do you have a better way? WHY is it better?

**Jeff North** · Nov 17 '06, 06:45 AM

Re: Auto Number mysql

On Fri, 17 Nov 2006 00:27:56 -0000, in mailing.databas e.mysql
gordonb.xxtjg@b urditt.org (Gordon Burditt)
<12lq0gc8dk7lba 8@corp.supernew s.comwrote:

[snip]

>| >How to fix the 'problem'. Tell the company to dump it's pre-printed
>| >stationary so that the system can 'take care of' the invoice numbers
>| >for them. (It will actually save them money and time in the long run).
>|
>| Fine. How do you allocate invoice numbers the NEW way? I say use
>| auto-increment. Do you have a better way? WHY is it better?

It's not what I think or suggest it is what the business rules
dictate.
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

Auto Number mysql

Auto Number mysql

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment