Databases: January 2008 Archives
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.