StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>DON'T</strong> encode <strong>ANY</strong> mutable customer/order information into the numbers! And you have to assume that <strong>everything is mutable!</strong></p> <p>Some of the above suggestions include a region code. Companies can move. Your own company might reorganize and change its own definition of regions. Customer/company names can change as well.</p> <p><strong>Customer/order information belongs in the customer/order record. Not in the ID.</strong> You can modify the customer/order record later. IDs are generally written in stone.</p> <p>Even just encoding the date on which the number was generated into the ID might seem safe, but that assumes that the date is never wrong on the systems generating the numbers. Again, this belongs in the record. Otherwise it can never be corrected.</p> <p>Will more than one system be generating these numbers? If so, you have the potential for duplication if you use only date-based and/or sequential numbers.</p> <p>Without knowing much about the company, <strong>I'd start down this path:</strong></p> <ul> <li>A one-character code identifying the type of number. <strong>C</strong> for customers, <strong>R</strong> for orders (don't use "O" as it could be confused with zero), etc.</li> <li>An identifier of the system that generated the number. The length of this identifier depends on how many of these systems there will be.</li> <li>A sequence number, unique to the system generating it. Just a counter.</li> <li>A random number, to prevent guessable order/customer numbers. Make this as long as your paranoia requires.</li> <li>A simple checksum. Not for security, but for error checking.</li> </ul> <p>Breaking this up into segments makes it more human-readable as others have pointed out.</p> <p><strong>CX5-0000758-82314-12</strong> is a possible number generated by this approach . This consists of:</p> <ul> <li>C: it's a customer number.</li> <li>X5: the station that generated the number.</li> <li>0000758: this is the 758th number generated by X5. We can generate 10 million before retiring this station ID or the station itself. Or don't pad with zeros and there's no limit.</li> <li>82314: this was randomly generated and results in a 1/100,000 chance of guessing a customer ID.</li> <li>12: checksum.</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload