DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Many to Many Relationships

  1. #1
    Edwin Guest

    Many to Many Relationships


    Hello All,

    I need to implement a many to many relation ship between two tables.
    One table contains a material used to create products and the other contains
    products that we sell. A product can have a number of different materials
    used in it and a material can be used for a number of products.
    In the past, I've seen schemes where the materials would have a comma delimited
    field and the product table would get the different number of materials associated
    with it by doing a join on this field and using the IN statement instead
    of the equal sign. I was wondering if there was a better way of implemeting
    this.

    Thanks in advance.

  2. #2
    DaveSatz Guest

    Re: Many to Many Relationships

    comma-delimited data would be a totally non-relational means to stored the
    data.

    why something like this:

    product
    --------
    product_id (PK)

    material
    --------
    material_id (PK)


    product_material
    -----------------
    product_id (fk to product) (PK)
    material_id (fk to material) (PK)

    --
    HTH,
    David Satz
    Principal Software Engineer
    Hyperion Solutions
    ->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
    (Please reply to group only - emails answered rarely)
    -----------------------------------------------------------------
    "Edwin" <vbedluciano@hotmail.com> wrote in message
    news:3b0184da$1@news.devx.com...
    >
    > Hello All,
    >
    > I need to implement a many to many relation ship between two tables.
    > One table contains a material used to create products and the other

    contains
    > products that we sell. A product can have a number of different materials
    > used in it and a material can be used for a number of products.
    > In the past, I've seen schemes where the materials would have a comma

    delimited
    > field and the product table would get the different number of materials

    associated
    > with it by doing a join on this field and using the IN statement instead
    > of the equal sign. I was wondering if there was a better way of

    implemeting
    > this.
    >
    > Thanks in advance.




  3. #3
    Sammy Guest

    Re: Many to Many Relationships


    "Edwin" <vbedluciano@hotmail.com> wrote:
    >
    >Hello All,
    >
    >I need to implement a many to many relation ship between two tables.
    >One table contains a material used to create products and the other contains
    >products that we sell. A product can have a number of different materials
    >used in it and a material can be used for a number of products.
    >In the past, I've seen schemes where the materials would have a comma delimited
    >field and the product table would get the different number of materials

    associated
    >with it by doing a join on this field and using the IN statement instead
    >of the equal sign. I was wondering if there was a better way of implemeting
    >this.
    >
    >Thanks in advance.


    Yes. You will need to create a third table that will link the two tables
    together; also refered to as "Pass-thru". you will need to create one-to-many
    relationship between it and each of the two tables. The "one" side of the
    relationship will link to each of the two tables and the many side will link
    to the pass-thru table.

    Hope that helps

  4. #4
    Edwin Guest

    Re: Many to Many Relationships


    "Sammy" <jmail@softucraft.com> wrote:
    >
    >"Edwin" <vbedluciano@hotmail.com> wrote:
    >>
    >>Hello All,
    >>
    >>I need to implement a many to many relation ship between two tables.
    >>One table contains a material used to create products and the other contains
    >>products that we sell. A product can have a number of different materials
    >>used in it and a material can be used for a number of products.
    >>In the past, I've seen schemes where the materials would have a comma delimited
    >>field and the product table would get the different number of materials

    >associated
    >>with it by doing a join on this field and using the IN statement instead
    >>of the equal sign. I was wondering if there was a better way of implemeting
    >>this.
    >>
    >>Thanks in advance.

    >
    >Yes. You will need to create a third table that will link the two tables
    >together; also refered to as "Pass-thru". you will need to create one-to-many
    >relationship between it and each of the two tables. The "one" side of the
    >relationship will link to each of the two tables and the many side will

    link
    >to the pass-thru table.
    >
    >Hope that helps


    It certainly does. I really appreciate your help.

    The first table will look like this?
    product
    --------
    product_id (PK)
    ...

    The second like this?
    material
    --------
    material_id (PK)
    ...

    And the third like this?
    product_material
    -----------------
    product_id (fk to product) (PK)
    material_id (fk to material) (PK)

    I recently was told that there is another set of tables that need to have
    the same kind of relationship with material called Task. Should I use the
    same pass thru table for both?


    Thanks in advance, and thanks for the help on my first question guys!!!!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center