mysql data type confusion

Question

So this is more of a generalized question about MySQLs data types. I'd like to store a 5-digit US zip code (zip_code) properly in this example.

A county has 10 different cities and 5 different zip codes.

city   | zip code
-------+----------
city 0 | 33333
city 1 | 11111
city 2 | 22222
city 3 | 33333
city 4 | 44444
city 5 | 55555
city 6 | 33333
city 7 | 33333
city 8 | 44444
city 9 | 22222

I would typically structure a table like this as varchar(50), int(5) and not think twice about it.

(1) If we wanted to ensure that this table had only one of 5 different zip codes we should use the enum data type, right?

Now think of a similar scenario on a much larger scale. In a state, there are five-hundred cities with 418 different zip codes.

(2) Should I store 418 zip codes as an enum data type OR as an int and create another table to reference?

Answer 1

Zipcodes should be integers... MEDIUMINT is what you're looking for. Few thousands different values is not good idea for ENUM - besides IIRC ENUM is limited to 64 different values.

You could create another table called city to store cities. The structure would be very simple: just standard ID + column for city name - VARCHAR(50) should be enought. Of course you would have to add city_id to zipcodes table (with Foreign Key attached).

Answer 2

if smallint unsigned (0 to 65535) is too small change to mediumint.

create table city(
 city_id smallint unsigned not null auto_increment primary key,
 name varchar(255) not null
)engine=innodb;

create table city_zip(
 city_id smallint unsigned not null,
 zip_code smallint unsigned not null,
 primary key (city_id, zip_code)
)engine=innodb;