I am creating a table of cities and their zip codes for the United States. Some zip codes have a list of acceptable cities, as designated by the USPS. For example, the zip code 36613 is associated with Eight Mile, AL, but the University of Mobile is in 36613 and it's address is in Mobile, AL. A look at USPS data and I see that Mobile is on the acceptable cities list for zip code 36613.
Now, I have a few routes that I can take:
I can create a new table called acceptable_city with a FK/PK relationship on city.
Create a column in city called acceptable_city that is defined as an array (
acceptable_city text), and create an index on this column.
So, in a case like this which option is more advantageous? What are the pros and cons of each approach?