mysql data type confusion
- by zen
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?
