Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I am designing a table configuration that will contain all of the zip codes in the United States. I have a .csv file of all the zip codes in the U.S., so thats at least a start.

This will end up as a one-to-many relationship between our vendors (they are waste services companies), and the various zip codes they service. Does anyone have advice on a good way to set up the tables to track this data? Some vendors may only service 5-10 zip codes, while the national players can handle nearly all 45,000 zip codes, so I'd like to set it up in a manner that this does not become a nightmare later on.

Any advice is appreciated!

share|improve this question

1 Answer

up vote 1 down vote accepted

I would say you would have:

Table: vendor id, name, etc

Table: vendor_area id, vendor_id, area_code_id

Table: area_code id, area_code, country_id, region_id (etc)

I'm suggesting that not knowing your business. But I would hate you go in a naive route assuming that you'll do US only and in a couple year you have to redesign the whole thing. The above will give you flexibility to expand and add more meta data to your areas/regions being covered.

share|improve this answer
I think thats perfect. Then I can easily generate a query that shows 'what vendors work in area code 12345? So just in terms of the size of those tables - the first one, with Vendor ID, is going to be limited by my vendors - could realistically be around 5,000. The third is going to be all Zip Codes/area codes, so for the USA thats 45,000. The second could then balloon to 225,000,000, if every one of my vendors worked in every area code? If only my business gets that large! – CDD Feb 25 at 4:43
If every vendor works in every area code, then you don't need the table :) But yes, that table could possibly get "big". You'll want indexes on both vendor_id and area_code_id probably so lookup from either directions. – ETL Feb 25 at 14:57
One other design question I'm hung up on right now - I also want to track what type of material a hauler/vendor will service. There are about 500 types of waste material tracked in the market. I thought I'd make a table similar to the Area_Code ID table, but for these recyclable materials. Whats the best way to implement this, so that I can run a query find HAULER where AREA_CODE = 15238 and MATERIAL = Cardboard ? Do I make the MATERIAL table, and another table with vendor_id and material_id ? – CDD Feb 25 at 15:18
Same principle. One table would define the things - like materials. And one table defines the relationship between the materials and the vendor. – ETL Feb 25 at 18:00
In terms of query you would have: select * from vendor where exists (select 1 from vendor_area where vendor_area.vendor_id = vendor.id and area_code_id = 15238) and exists (select 1 from vendor_material where vendor_material.vendor_id = vendor.id and vendor_material.material_id = Cardboard) ----- or similar with joins, etc. – ETL Feb 25 at 18:02
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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