Data Saving Strategy for Numbers

Hey kids! Let's chat about data storage!

When we save data to a database, we need to think carefully about what type of field we're saving it into. The data type you choose determines how the database stores, reads, and handles that value .... picking the wrong one can corrupt your data in ways that are really hard to debug later.

One of the most common beginner mistakes? Storing zip codes as numbers.

Saving an address - the wrong way

Let's say you're building a table to store customer mailing addresses. You might set it up like this:

-- ❌ WRONG: Do NOT do this
CREATE TABLE addresses (
    id        INT PRIMARY KEY,
    street    VARCHAR(100),
    city      VARCHAR(50),
    state     CHAR(2),
    zip       INT        -- This is the problem!
)

This seems reasonable at first - zip codes look like numbers, after all. But storing zip as a number is a trap.

Here's what happens when you insert data:

-- ❌ WRONG: Inserting with an INT zip column
INSERT INTO addresses (id, street, city, state, zip)
VALUES (1, '10 Main St', 'Blandford', 'MA', 01234);

-- What actually gets stored:
-- zip = 1234   <-- leading zero is GONE. Silent data loss!

You get ghosted. The database just drops that zero and doesn't tell you. You won't notice until someone's package ends up in the wrong state, or your front end address validation fails or stops a user from entering their data. Unless you set the number length to be 5 by using DECIMAL(5, 0), but let's face it - that is rarely done irl.

The fix: save zip codes as VARCHAR

-- ✅ CORRECT: Do this instead
CREATE TABLE addresses (
    id        INT PRIMARY KEY,
    street    VARCHAR(100),
    city      VARCHAR(50),
    state     CHAR(2),
    zip       VARCHAR(10)   -- Handles 5-digit AND ZIP+4 formats
)

Using VARCHAR(10) covers both the standard 5-digit format (01234) and the extended ZIP+4 format (01234-5678)

Now when you insert that same zip code:

-- ✅ CORRECT: Inserting with a VARCHAR zip column
INSERT INTO addresses (id, street, city, state, zip)
VALUES (1, '10 Main St', 'Blandford', 'MA', '01234');
--                                            ^^^^^^
--                                   Quoted as a string!

-- What actually gets stored:
-- zip = '01234'   <-- Leading zero preserved, exactly as entered ✅

Notice the zip code is now wrapped in quotes because it's a string, not a number.

Comparison of Int vs Varchar results:

-- ❌ INT column         | ✅ VARCHAR column
-- --------------------- | ---------------------
-- INSERT: 01234         | INSERT: '01234'
-- STORED: 1234          | STORED: '01234'
-- RETRIEVED: 1234       | RETRIEVED: '01234'
--  (boo!)               |  (yay!)

Phone numbers are also interesting, based on what you're willing to allow to be stored. Do you want the numbers only, just the base 10 or the country code too? What if someone pastes the + sign with it? Let's chat about that .....

Phone numbers in particular are another field that look like numbers, but really should not be stored as one, for your own sanity down the line. Let's look at why.

-- ❌ WRONG: Phone number as a numeric type
CREATE TABLE contacts (
    id           INT PRIMARY KEY,
    name         VARCHAR(100),
    phone        BIGINT    -- Please no
);

Even setting aside the leading zero problem, BIGINT can't store a phone number the way a real user would enter it. Here's everything that breaks:

-- ❌ All of these get mangled or fail entirely with a numeric column:

-- Leading zero stripped (common in UK numbers):
-- INSERT: 07911123456  →  STORED: 7911123456  

-- Plus sign for international format can't be stored as a number:
-- INSERT: +14155552671  →  ERROR or data loss

-- Country code with formatting:
-- INSERT: +1-415-555-2671  →  ERROR due to dashes 

-- Common US formatted style:
-- INSERT: (415) 555-2671  →  ERROR due to parentheses

The fix is the same as with zip codes — use a string:

-- ✅ CORRECT: Phone number as VARCHAR
CREATE TABLE contacts (
    id           INT PRIMARY KEY,
    name         VARCHAR(100),
    phone        VARCHAR(20)   -- Handles international formats, symbols, spaces
);

VARCHAR(20) gives you plenty of room for international numbers in any format your users might enter:

-- ✅ All of these store perfectly as VARCHAR:
INSERT INTO contacts (id, name, phone) VALUES (1, 'Alex',  '07918675309')
INSERT INTO contacts (id, name, phone) VALUES (2, 'Sam',   '+14155552671')
INSERT INTO contacts (id, name, phone) VALUES (3, 'Jules', '+1-415-555-2671')
INSERT INTO contacts (id, name, phone) VALUES (4, 'Morgan','(415) 867-5309')

One extra tip here .... if your app needs to support international users, you may want to look into always storing phone numbers in E.164 format (the +14155552671 style). It's a universal standard that strips all formatting down to country code + number, which makes it much easier to validate, search, and pass to services like SMS providers. There are a bunch of 'copyable' functions in places like StackOverflow in every language that you can reuse, so just do a search with the language you need the code in, search for 'international phone number regex', or use the library https://github.com/google/libphonenumber.

---

The Golden Rule 🏆

A good rule of thumb: if you're never going to do math on a value, it probably shouldn't be a number type. 

You wouldn't add two zip codes together, or calculate the average of a set of phone numbers. These are identifiers, not quantities. Identifiers belong in string fields.

The same logic applies to other fields that look numeric, but aren't:

-- ❌ All of these should probably not be INT/numeric types:
phone_number   INT      -- 0800123456 becomes 800123456
ssn            INT      -- 012-34-5678 becomes unusable
order_id       INT      -- 00892 becomes 892
product_sku    INT      -- 007ABC can't be stored

-- ✅ These should all be strings:
phone_number   VARCHAR(15)
ssn            VARCHAR(11)   -- also: please hash this, its PII
order_id       VARCHAR(20)
product_sku    VARCHAR(50)

When in doubt, ask yourself: does the leading zero matter? Does the format matter? If yes - make it a string.

Stay safe out there, happy coding, and keep your data in good shape!! Until next time ....