Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12
  1. #1
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    I would like to develop some code that will help to automatically make an intermediate table, that is, a table that goes between 2 tables that normally form a many to many relationship, to become 2 many to one relationships.

    Example: you have a table filled with addresses. I would like to develop some code that will go through and make a table of which street names are in which states. Since one street name can exist in many states, and one state can have many streets, this will be the example of the many to many relationship.

    What I'd like this code to do is to transverse the table of addresses, and make the intermediate table, i.e.

    Col 1 Col 2
    Main Street PA
    Main Street FL
    Brook Street PA
    OBT FL

    where the orig tables are:

    Main Street Philadelphia
    Main Street Orlando
    etc...

    and
    Philadelphia PA
    Pittsburg PA
    Orlando FL

    I hope this is clear. Does anyone know of a script, or have an idea on how to set this up? I'd assume someone has already worked up a script to work this out, since many to many relationships are somewhat common, but I haven't been able to find one as of yet.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    I did something like this for Zip Codes, Cities and States... a search by Zip Code will return the State and a list of Cities associated with it.

    Here, look at this page: http://www.transio.com/ufa/zip_fun.asp

    Type in a zip code and click "look up". You'll see that the city and state lists get populated with the appropriate info.

    Is that what you're looking for?

  4. #3
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Well, the City/State/Zip was just an example, not really what I'm looking for. Just looking for a tool to make a many to many relationship into 2 one to many relationships.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    A tool or a database design? What are the exact tables and fields that you want to relate.

  6. #5
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Tool. Here's what I'm doing. Transio, I think you've seen it before.

    Example: Asus A7V8X Motherboard

    Asus makes multiple types of HW, i.e. motherboards and video cards. Video cards are made by many different manufacturers, who may or may not make motherboards, or whatever.

    Manufacturers and Product Types are a many to many relationship. If I make a DB of entries like the example above, I'd like a tool (or help in making one) that can take a many to many relationship like the one above, and create a intermediate table so that there are now 2 many to one relationships.

    Example: a table like so:
    ASUS | Motherboards
    ASUS | Video Cards
    ABIT | Motherboards
    etc.

    Yes, you can do this by hand, however with a huge DB, it can be a humongous pain in the ***.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  7. #6
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    No... what you want is Manufacturers and Products... then Products have Product Types, like so:

    Code:
    CREATE TABLE maunfacturers (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        description TEXT NULL,
        PRIMARY KEY (id)
    }
    
    CREATE TABLE product_types (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        description TEXT NULL,
        PRIMARY KEY (id)
    }
    
    CREATE TABLE products (
        id INT NOT NULL AUTO_INCREMENT,
        manufacturer_id INT NOT NULL REFERENCES manufacturers(id),
        product_type_id INT NOT NULL REFERENCES product_types(id),
        name VARCHAR(255) NOT NULL,
        description TEXT NULL,
        PRIMARY KEY (id)
    }
    Of course, you will store differnt information than name and description in each of the tables, but this is the structure you want.

    If you want to get a list of manufacturers and their offered product types, you would use SQL for that, like so:

    Code:
    SELECT
        m.name AS manufacturer, 
        pt.name AS product_type
    FROM 
        manufacturers AS m
        INNER JOIN products AS p ON m.id = p.manufacturer_id
        INNER JOIN product_types AS pt ON p.product_type_id = pt.id
    ORDER BY 
        m.name, 
        pt.name
    Hope all this makes sense

  8. #7
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    So TABLE maunfacturers is ASUS, TABLE product_types is motherboards, TABLE products is A7V8X?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  9. #8
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    yup

  10. #9
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Well actually, in retrospect, manufacturers and product_types have a many-to-many relationship in a sense, though a typical many-to-many would have a combination key table (products in this case) that has no data save the two primary keys (manufacturer_id and product_id). In this case, products are a data member of their own with a many-to-one relationship to manufactuers and a domain relationship with product types. Because product types are static (a domain), they cannot have a dynamic relationship with any of the other data.

  11. #10
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    combination key table

    that's what I'm talking about. What would make that?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:01 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com