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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior 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.

Barry CunneyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.