Data Types for IDs: Why Passport Numbers Aren't Integers
A simple question about choosing the right data type for storing passport numbers in an SQL database splits developers into two camps: those who think about data semantics and those who go by rote. Picking INTEGER over VARCHAR can strip leading zeros and break identification, which is critical in systems handling millions of users. This article breaks down a core principle of database schema design based on the nature of the data, not its appearance.
Data Semantics vs. Appearance
Most developers, when they see digits in a passport number during interviews, instinctively pick numeric types like INTEGER or BIGINT. This is a classic mistake from superficial analysis. The key question isn't what the data is made of, but how it's used. You never do arithmetic on passport series or numbers—you just compare for equality, search, and transmit them. These are identifiers, not quantities. So the right choice is a string type: CHAR(4) for the series and CHAR(6) for the number, or CHAR(10) for the combined value. Storing in INTEGER irreversibly drops leading zeros: 0306 becomes 306, invalidating the document with no logged errors.
Real-World Fallout from the Mistake
Choosing the wrong data type isn't theoretical—it hits production regularly. Take a parking permit service where the passport field as NUMBER silently trimmed a leading zero, mismatching database records with paper docs. It slipped through code review and testing until it hit users with zero-starting series. In Russia, millions of such passports exist, scaling the issue massively. Optimization arguments like space savings or index speed don't hold up:
- CHAR(4) takes the same 4 bytes as INTEGER for single-byte digits.
- VARCHAR indexes perform at O(log n) efficiency, just like numerics, with negligible differences for fixed strings.
Byte savings don't justify data loss risks.
Applying the Rule to Other Identifiers
The rule "if you don't do math on it, it's not a number" applies to many IDs in Russian and global systems. Key examples:
- SNILS (11 digits, XXX-XXX-XXX XX format): Store as VARCHAR(14) or CHAR(11) after stripping dashes, not BIGINT.
- Personal TIN (12 digits): Can start with zero for regions 01–09, so VARCHAR(12) is a must.
- Phone number: VARCHAR(20) only, to keep the '+' for international formats.
- Postal code: CHAR(6), as international ones can be alphanumeric.
- Bank account (20 digits): CHAR(20), since numeric types can't handle that length without overflow.
String types ensure flexibility and prevent data loss from format changes.
Key Takeaways
- Semantics first: Base data type on operations, not looks.
- Leading zeros matter: Numerics strip them, breaking IDs for millions of records.
- Optimization second: Don't trade byte savings or microseconds for bad data.
- Universal principle: Covers SNILS, TIN, phones, and more.
- Audit your schemas: Check existing databases for hidden risks.
How to Pick the Right Data Type
A solid engineer asks three questions when designing a database field:
- What's the semantics? (ID, quantity, text?)
- What operations? (Compare, search, math?)
- What business rules apply? (Format, length, chars?)
Answers dictate the choice: strings for IDs, numerics for quantities. This separates problem-solving from copy-paste coding and nips expensive bugs early.
Data Types for Russian IDs
| Data | Format | Right Type | Common Mistake | Error Impact |
|------|--------|------------|----------------|--------------|
| Passport series | 4 digits | CHAR(4) | INTEGER | Leading zero loss, invalid doc |
| Passport number | 6 digits | CHAR(6) | INTEGER | Zero trim, broken ID |
| Full passport | 10 digits | CHAR(10) | BIGINT | Up to two leading zeros lost |
| SNILS | 11 digits | CHAR(11) | BIGINT | Trim for 01–09 codes |
| Personal TIN | 12 digits | CHAR(12) | BIGINT | Digit loss for 01–09 |
| Phone | +7... | VARCHAR(20) | BIGINT | Can't store '+', no intl support |
| Postal code | 6 digits | CHAR(6) | INTEGER | No alphanumeric intl codes |
| Bank account | 20 digits | CHAR(20) | No fitting INT | Overflow, financial risks |
This table is your cheat sheet for schema design and audits.
— Editorial Team
No comments yet.