When to Store Numbers as Text
Not all number-based fields should be stored as numeric data types.
Any data that you can store in a numeric field can also be stored in a text field. So, how do you decide which one to choose? For example, how would you store the following?
- 5-digit US ZIP codes
- 9-digit (5 + 4) US ZIP codes
- Phone numbers
- International Standard Book Numbers (ISBN)
- Universal Product Codes (UPC)
- Stock Keeping Units (SKU)
- Check Numbers
- Bank Account Numbers
- Bank Routing Numbers
- Student Grades (0 - 100 scale)
- Student Grade Point Averages (0 - 4.0 scale)
- US Social Security Numbers
The Numeroliteral Test
The word "numeroliteral" derives from the Latin numerus ("a number") and the Latin litera ("letter of the alphabet").
Yes, I made it up.
The Numeroliteral Test is a heuristic for determining whether data should be stored in a numeric database type or a character-based database type. To apply the test, you need only answer one simple question:
- Does it make sense to add, subtract, multiply, or divide the contents of the field?
If the answer is Yes, then store the data in a numeric field.
If the answer is No, then store the data in a character-based field.
External references
Image by Kamalakannan PM from Pixabay
UPDATE [2021-11-26]: Changed article title from "Storing Numbers as Text" to "When to Store Numbers as Text." URL slug stayed the same to avoid breaking links.