How to: Create database columns that contain only digits

I see this missed so many times I have to bring it up here: "If you have a database column that contains only digits, but will not perform calculations on it, make it a character column."

You CAN store a 10-digit phone number as an integer, but why would you want to? You CAN store a Social Security Number as a 9-digit number, but why would you want to? Surely you're not so concerned of a few bytes savings. Storing an SSN of "0123456789" as a number means you use the leading zero, too, so you lose fidelity of data. Any string of digits follows this rule. You don't perform calculations on part numbers, course numbers, Dewey Decimal numbers, or house numbers, either, so make 'em all character fields.

Same goes for years stored as date datatypes. If you're recording the year that a movie was released, then there's no advantage to having it as a date. Store it as an integer to make it simple to take differences ("How long after Citizen Kane did ET come out?") or comparisons.

Most of all, keep things consistent. If you've got a 10-character column in one table, and an integer in another, then SQL joins will be very expensive, even if both columns are indexed.



Ambrose said:

Just a quick question on that.

What about database tables used purely for normalisation?

As in Caesar Salad is dish id 23, where 23 is an autoincrementing int, primary key, and bacon is ingredient 17, same deal, and somewhere in a joining table you have a row "23 17" to show that bacon is in a Caesar Salad.

Does it make any difference whether the joining table's numbers are text columns or integers? Or indeed the ids?

Andy Lester said:

No, keys that are strictly ID keys are fine as integers. You're also never using them as data fields.

I'll amend the article. Thanks.

Leave a comment

About this Entry

This page contains a single entry by Andy Lester published on January 13, 2008 6:27 PM.

Superstition has no place in programming was the previous entry in this blog.

Designing too far into the future is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.

Other Perl Sites

Other Swell Blogs

  • geek2geek: An ongoing analysis of how geeks communicate, how we fail and how to fix it.