Setting up a database... Best Practice

PHP
1

I’m working on a project that will have a list of suppliers delivering to between 10 and 20 postal codes (zip codes) each. What is the best option for setting up a database to store the postal codes that each supplier delivers to? Clients will be presented the suppliers who deliver to their delivery address postal code.

Should I setup a table with each record being a postal code and supplier ID? What is the most efficient approach? I’d love to hear some thoughts…

I’m using PHP/MySQL

2

I would say three tables based on the information you gave.

Table 1: Suppliers
Table 2: Zip codes
Table 3: Supplier zip code lookup

Table 3 would have two columns supplier_id and zipcode_id and allow the suppliers to be assigned to multiple zip codes.

Then when you need to lookup suppliers by zip code you would join your tables to get all of the suppliers based on the zipcode input.

3

I agree with Ben. In database jargon thats called a many to many relationship (with the table linking Suppliers and Zips being called a bridge or link table)

4

Thank you Ben and Loui!