Many to Many Relationships

[Edwin]

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.

[DaveSatz]

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.

[Sammy]

"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

[Edwin]

"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!!!!