0

The Data

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.

The Question

Now, I have a few routes that I can take:

  1. I can create a new table called acceptable_city with a FK/PK relationship on city.

  2. 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?

1

There is no clear answer to that. Both variants have pros and cons. Many here will advice to properly normalize data with a separate table.

You might get best of both worlds with a MATERIALIZED VIEW:
Properly normalize your data model, and offer an aggregated view on the data in the materialized view.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.