Iknow several professional applications that use varchar fields to store typically invoice and order numbers, just because it makes it easier to fill this field with non-numerical characters (for example when issuing credit notes and that).
This is pretty irritating on the part of indexing and sorting results: after all, which comes first when using alphanumerical sorting: ‘10001’ or ‘200-1’? Most likely, you’ll lose a lot of speed too: sorting (and comparing) integers goes a lot faster than comparing sets of characters.
Instead of declaring these types of fields varchars, consider making them integer fields: after all, adding dashes (and other non-numerical characters) can be easily made part of a formatting mask in the UI of the application: heck, most graphical UI have masked edit field controls (Win 32/.Net/KDE/Gnome).
Additionally, on typical transactional databases like Postgres and Oracle, (integer) sequences are created atomically [transaction independent], which can save you a lot more headaches than trying to generate a unique combination of characters in a multi-user environment.
Integers in varchars? You’re lucky. You should know how often I run into databases where everything is a “text” field. “But it holds text! Indexing? What’s that?”. Meanwhile these same people produce quite reasonable code. I guess the IT bubble drew a lot of trick-monkeys into the field.
I run into databases where everything is a “text†field.
Ugh. That sounds rather ugly.