When implementing tables that store information such as
- Invoice Numbers
- Cheque Numbers
- Part Numbers
- Document Numbers
- Certificate Numbers
- Serial Numbers
applications often require a mechanism to automatically generate a unique, sequential, gap-free series of numbers. I will refer to such continuous numbers as "serial numbers" on this site to distinguish them from "sequence numbers" that are generated via database sequences. Database sequences cannot be used to generate serial numbers because they produce a series that is not necessarily continuous. In this section we see how to create serial numbers using custom code that implements what I call a "serial number generator".
When designing tables for entities with serial number attributes, it is often tempting to use the serial number as the primary key. I recommend against this practice. In my experience there is little benefit and significant risk with this approach. I prefer to use one column for the primary key, populated from a database sequence, and a separate column for the serial number, populated using one of the methods outlined in the tutorials to follow.
Using a single column may seem like a simple design that saves a little disk space, but down the road it limits the application's flexibility and scalability. Flexibility is limited because it prevents you from saving the same serial number twice in the same table. This may not be a problem when you first design say, an invoice table, because your company does not currently issue the same invoice number twice. However, when your company does something like spin off a subsidiary and the accountants want to store its invoices in your invoice table using the same series of invoice numbers as the parent company, you will be out of luck (I've seen this happen).
Scalability is limited because generating gap-free primary key values requires a serialization mechanism to prevent two different transactions from both fetching the same next available serial number. Serialization prevents parallel operations, which in turn prevents your application from scaling well.
While using the two column approach is better than the single column approach, it still has its disadvantages. Some two column implementations may be flexible, but not scalable. Others may be flexible and scalable, but may require extra code or batch processes. Before implementing a serial number column consider whether being gap free is a "nice to have" or a necessity. If your users can tolerate gaps, use a database sequence instead of a serial number generator.
If you need to generate the same series of numbers repeatedly, e.g. for numbers that will not be stored in a database column but are instead used in calculations or reports, see Integer Series Generators.