SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2008
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Setting up a database... Best Practice

    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. #2
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Ben

  3. #3
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Enthusiast
    Join Date
    Dec 2008
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Ben and Loui!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •