Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Relate to multiple entries in a different table

    Hello dbforums.com users!

    I've been trying to help myself but I can't seem to find a way to implement what I want to do so I decide to post here.

    I'm looking to create a DB of service providers and their info, be able to search based by location, and type of service, as well as your basic alphabetical db dump to a chart.

    To be able to list the locations and service types individually it seems the best way is to have their own table.

    Example of what I'm looking to do


    Code:
    TABLE - PROVIDERS
    Key - Name  - Phone - NET  - TV  - Location
     1  - Bell  - 1     - 1    - 1/3 - 1/2/3
     2  - Rogers- 2     - 2    - 2   - 1
     3  - Acanac- 2     - 1/2  - 3   - 2/3
    
    TABLE - Phone
    Key - Type
    1 - POTS (plain ol tele)
    2 - VOIP
    
    TABLE - NET
    Key - Type
    1 - DSL
    2 - Cable
    
    TABLE - TV
    1 - Satellite
    2 - Cable
    3 - IPTV
    
    TABLE - LOCATION
    1 - Ponyville
    2 - Apple Acres
    3 - Equestria
    Problem is - there doesn't seem to be a way to have multiple values like I want to do in the first table.

    Soooo I'm wondering if anyone has any suggestions as to how to design this db.
    Any help is greatly appreciated.
    Last edited by TheOddPerson; 02-13-13 at 10:27.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    add four intersection tables:
    1. Table:Provider_Phone
      1. ProviderID is Foreign Key to Provider(Key)
      2. PhoneID is Foreign Key to Phone(Key)
      3. Primary key is ProviderID, PhoneID
    2. Table:Provider_Net
      1. ProviderID is Foreign Key to Provider(Key)
      2. NetID is Foreign Key to Net(Key)
      3. Primary key is ProviderID, NetID
    3. Table:Provider_TV
      1. ProviderID is Foreign Key to Provider(Key)
      2. TVID is Foreign Key to TV(Key)
      3. Primary key is ProviderID, TVID
    4. Provider_Location
      1. ProviderID is Foreign Key to Provider(Key)
      2. LocationID is Foreign Key to Location(Key)
      3. Primary key on ProviderID, LocationID
    Intersection tables are used to create many-many relationship between two tables.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Feb 2013
    Posts
    3
    Thank you very much for your help!

    It did take me a little bit to get the concept of many-many and how a junction table should look but I completely understand now.

    I can now get straight to work on my db!

    Thank you, thank you thank you!

Posting Permissions

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