Regarding Primary Key and the Foreign Key objects.
In my business I have seen many different Invoice Number formats on computer-generated orders from venders.
For example:
1. A20595
2. 00048768
3. 175878
The vender that uses the A prefix example uses the A prefix during the first 15 days of the month and uses a B prefix during the remainder of the month starting the 16th of month until the end of the month. These are the two billing periods that become due on specific dates. These invoice (OrderID) numbers would require a Text Field to accommodate the A/B prefix and would require a computed PK.
The 00048768 generated invoice (OrderID) number would require a Text Field to accommodate the preceding 0’s. (Zeros). In this example, if the Pk field were a numeric field access would strip the preceding 0’s and save only the 48768 part of the number. These also require a computed PK.
The third example 175878 is the Invoice numbering system I use in my order system. The field is a number field with a field size of Long Integer.
Although my order numbers are numeric, and not alpha-numeric in nature, I decided to not to use an Auto Number Field for my OrderID field for several reasons. My Invoice numbers are computed for each invoice using the previous OrderID value to compute the next OrderID.
Some of my reasons are:
1. The user selects the wrong account and begins an order, or even completes an order, when he realizes he selected the wrong account.
2. The user inadvertently finds himself in the order form and presses a key that sets the next OrderID when an Auto Number field is used and then exits the form.
The fact that these occurrences would trigger the next Auto Number and, when the order is deleted from the system or cancelled, would result in invoice numbers that don’t exist.
I feel it is important to track every one of my orders and that they remain in sequence. When an OrderID – Invoice number – is missing from the order system, which would be the case of an Auto Number created OrderID that is cancelled, you begin to wonder if you are missing an invoice that somehow got deleted from the system. That being the case you begin to think of the money that invoice represents and so on… and so on… and so on…
One very important point I would like to make about keeping all of your Ducks In A Row. In an audit by the state (Sales Tax) or in an audit by the federal government (IRS) they may indulge you with “I can’t find this invoice, please provide it!” (It’s happened to me)
My advice is to use a computed OrderID number instead of an Auto Number for peace of mind. I try to keep this in mind whenever I create my applications and I use a computed PK instead of Auto Number wherever necessary.
Richard
PS: In my cash register application I use 20090108-183824-0002 as the Primary Key. This contains the date, time and computer used for generating the sale. This is also the receipt number that shows on a printed receipt.