Integer vs String in database

Question

When defining datatypes in a database, I have always had a problem with choosing whether to use integers or strings to store certain 'numerical' data.

Say I am building Yet Another Address Book and there is a post code field. Provided that post codes are always a 4 digit number, which data type do I store it as? Integer or string? Technically it is an integer, but I'm not doing any sort of calculations on it, I'm just spitting it out into a table. Would your opinion change if I want to sort the table by post code?

Now, I'm not stupid. I do recognize a valid need for integers, such as page views and unique users or logged in users and guest users. But what about for storing how many files are in a torrent? Integer or string?

Answer 1

In my country, post-codes are also always 4 digits. But the first digit can be zero.

If you store "0700" as an integer, you can get a lot of problems:

• It may be read as an octal value
• If it is read correctly as a decimal value, it gets turned into "700"
• When you get the value "700", you must remember to add the zero
• I you don't add the zero, later on, how will you know if "700" is "0700", or someone mistyped "7100"?

Technically, our post codes is actually strings, even if it is always 4 digits.

You can store them as integers, to save space. But remember this is a simple DB-trick, and be careful about leading zeroes.

But what about for storing how many files are in a torrent? Integer or string?

That's clearly an integer.

Answer 2

I always use the following rule:

If you plan on performing mathematical calculations on it (adding/subtracting/etc) make it an integer or other numerical data type.

If you do not plan on performing any types of mathematical calculations on the field, store it as a string.

In the instance of Zip codes, you should never have a time where you need to add to a zip code, or subtract, or multiply two zip codes together. Mathematical functions generally are not used on ZIP codes because they are used as identifiers and not quantities. Therefore you should store your zip code as a string datatype