It's about the performance so I'm not sure what should be the best table structure for my requirement:
There are approx 15,000 ZIP / Postal Codes and as of now 3 shipping partners, we may tie up with more in future.
Not every shipping partner ships in all areas so for each ZIP / Postal Code we have to store who is shipping for given ZIP / Postal Code and if all of them are shipping then we've preferences, of course based on time and cost, which is fixed and not going to change.
Zip Code, ShippingPartner1, ShippingPartner2, ShippingPartner3
xxxxxxxx, NO, YES, YES
yyyyyyyy, YES, YES, YES
So record one says send through ShippingPartner2.
Record two says send through ShippingPartner1.
What should be the best structure to store this so that when a query runs to find out who's the preferred partner for a given ZIP Code, it uses least resources and runs very fast.