language agnostic - 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.