Need help to define the table structe


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.

Who is Participating?
Barry CunneyConnect With a Mentor Commented:
Hi Practioner,
Here are some ideas.
Having a second table for cross referencing ShippingPartners by Zip Code may be a good option, because then you will not be limited to the number of Shipping Partners per Zip Code and also you can link into details of the actual shipping partner.

So your cross reference table would be something like the following

Zip Code    |   ShippingPartner_Code  |   Preferred
XXXXXX      |    PARTNER01                  |     0
XXXXXX      |    PARTNER02                  |      1

The Preferred field could be of data type bit with a 1 or 0 -  1 = Yes this is the preferred Shipping Partner.

If the Zip Codes are uniform and always have the same number of characters, for example if they are always 12 characters then make the Zip Code field char(12)

Then think about creating index on the Zip Code field
David ToddConnect With a Mentor Senior DBACommented:

The usual way to handle this - at least on paper - is to have a link table between zip and shipper.


Then its easy to add a 4th and 5th shipper. Otherwise your initial suggestion requires a redesign of the application and addition of two more columns. The additional columns could be justified if they were mostly yes.

practitionerAuthor Commented:
Thanks David and BCUNNEY,

That's the standard and traditional way of doing it, is there a better approach? Something like PHP Serialization?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.