« The order of events i… | Home | DynaPDF with gradient… »

Use text fields for numbers

Due to current events in an office nearby, here again an announcement announcement for all database developers:
Please treat the following numbers as text for processing and storing in a database.

Zip codes
While we call them zip numbers in Germany and they are just five digits here, a zip code may contain various other characters like letters, spaces and dashes. e.g. "SW1A 1AA" is a valid zip code in the United Kingdom. The length of post codes may be over 10 characters in some places and contain leading zeros. In Argentina even a plus may be used like "46C2+32".

Invoice Numbers
The number on an invoice, an offer or an order often has a scheme defined by the used software. Some companies use dashes to separate sections of the number like year-branch-number to give each shop branch their own running count for invoices. We have seen invoices numbers with prefix or postfix to indicate branches or types of invoices.

Phone numbers
How often did I run into US software, which expected a phone number to always have 10 digits. But in Europe we have no fixed size. Area codes can be as short as two digits e.g. 30 for Berlin and longer like 02632 for Andernach. Some older people in a village may have been an early adapter for the phone number and got a 3 digit phone number. Newer phone numbers assigned in the same village may get 4, 5 or 6 digits. And then a company may have a phone system with extensions. So a published phone number for someone in an office may be +49-2632-958954-123.

Tax IDs
Various tax IDs need to be stored like those for sales tax or VAT. Please note that a VAT id may look like it's just a number, it usually has a prefix for the country and may contain letters. The Dutch VAT IDs like to have a B towards the end.

Bank account numbers
While we say numbers, they nowadays may contain letters for the country prefix. An IBAN can be up to 32 character long for St. Lucia.

Record numbers
Even for your database the primary key for a table may not be a normal number counting up, but this may be an UUID or otherwise a random alpha numeric string. Using UUIDs avoids duplicate keys if you merge two databases and makes it unlikely for someone to guess a valid record identifier.

Unless you know a key field in a table is always numeric (e.g. 64-bit integer), please consider text to pass along record identifiers.

Person #
If people get a number like an employee number, please don't expect them to be numeric. Again they may have characters as prefixes or contain dashes, space or leading zeros for formatting.

Credit Cards
One thing to leave is to pass credit card numbers as text. While they usually currently are 16 digits, that's not always the case. American Express only uses 15 digits. And because we may run out of available numbers eventually, there may be more digits. Currently you may see Meastro cards with 17 digits.
It may be good to just pass the number on to whatever processing is done later.

Cleanup
In all cases it is still recommended to do cleanup like a trim(). That should remove extra space before and behind. Also you may want to remove line breaks within the number or to replace Char(160) with Char(32) to sort out non-breaking spaces.

For all fields you may still do a reasonable limit in how many characters are allowed to go in a field.

Did we miss something? Please let us know. The biggest plugin in space...
07 10 21 - 14:42