SQL Relationship Question

[Simon Franklin]

Im an designing a database using SQL Server 2000 that stores various product
details. I have a products table that contains a CategoryID field I also
have a Category table containing many different Categories. I have created
a one-to-many relationship between the Category table and the Products table
an all works well. The relationship allows me to select Products by Category
such as "SELECT * FROM Products Where CategoryID='MyCategory'". This is perfect
for products that are related to a single category but I would also like
a single product to be related to many categories. How would redesign the
database to allow this without storing duplicate records. Thanks in advance
Simon.

[MarkN]

First, I would advise against using a string field as a foriegn key especially
if you db gets to be of any size and definitely if that field (CategoryID)
has meaning.

To answer your question. You will need an associative entity to solve the
many-to-many relationship.

Table: Category CategoryProduct Product
PK Fields: ID CatID & ProdID ID
Example: 15 15 23 23
Example: 11 11 23


"Simon Franklin" <simonfranklin@iperium.com> wrote:
>
>Im an designing a database using SQL Server 2000 that stores various product
>details. I have a products table that contains a CategoryID field I also
>have a Category table containing many different Categories. I have created
>a one-to-many relationship between the Category table and the Products table
>an all works well. The relationship allows me to select Products by Category
>such as "SELECT * FROM Products Where CategoryID='MyCategory'". This is
perfect
>for products that are related to a single category but I would also like
>a single product to be related to many categories. How would redesign the
>database to allow this without storing duplicate records. Thanks in advance
>Simon.

[Simon Franklin]

"MarkN" <m@n.com> wrote:
>
>First, I would advise against using a string field as a foriegn key especially
>if you db gets to be of any size and definitely if that field (CategoryID)
>has meaning.
>
>To answer your question. You will need an associative entity to solve the
>many-to-many relationship.
>
>Table: Category CategoryProduct Product
>PK Fields: ID CatID & ProdID ID
>Example: 15 15 23 23
>Example: 11 11 23
>
>
>"Simon Franklin" <simonfranklin@iperium.com> wrote:
>>
>>Im an designing a database using SQL Server 2000 that stores various product
>>details. I have a products table that contains a CategoryID field I also
>>have a Category table containing many different Categories. I have created
>>a one-to-many relationship between the Category table and the Products
table
>>an all works well. The relationship allows me to select Products by Category
>>such as "SELECT * FROM Products Where CategoryID='MyCategory'". This is
>perfect
>>for products that are related to a single category but I would also like
>>a single product to be related to many categories. How would redesign the
>>database to allow this without storing duplicate records. Thanks in advance
>>Simon.
>
Thanks Mark all is clear now well almost.

[MarkN]

What is still cloudy?


"Simon Franklin" <simonfranklin@iperium.com> wrote:
>
>"MarkN" <m@n.com> wrote:
>>
>>First, I would advise against using a string field as a foriegn key especially
>>if you db gets to be of any size and definitely if that field (CategoryID)
>>has meaning.
>>
>>To answer your question. You will need an associative entity to solve
the
>>many-to-many relationship.
>>
>>Table: Category CategoryProduct Product
>>PK Fields: ID CatID & ProdID ID
>>Example: 15 15 23 23
>>Example: 11 11 23
>>
>>
>>"Simon Franklin" <simonfranklin@iperium.com> wrote:
>>>
>>>Im an designing a database using SQL Server 2000 that stores various product
>>>details. I have a products table that contains a CategoryID field I also
>>>have a Category table containing many different Categories. I have created
>>>a one-to-many relationship between the Category table and the Products
>table
>>>an all works well. The relationship allows me to select Products by Category
>>>such as "SELECT * FROM Products Where CategoryID='MyCategory'". This is
>>perfect
>>>for products that are related to a single category but I would also like
>>>a single product to be related to many categories. How would redesign
the
>>>database to allow this without storing duplicate records. Thanks in advance
>>>Simon.
>>
>Thanks Mark all is clear now well almost.

[Simon Franklin]

Hello again Mark after going through SQL Server books online with the pointers
you gave me I have now created the Junction table and added the respective
primary keys and also added the relationships between the now three tables.
However i'm still a bit confused about the type of relationship between the
Categories-To-CategoryProducts relationship and Products-To-CategoryProducts.
Am I right in thinking that the junction table should be on the many side
of the relationship between both Categories and Products table?.
"MarkN" <m@N.com> wrote:
>
>What is still cloudy?
>
>
>"Simon Franklin" <simonfranklin@iperium.com> wrote:
>>
>>"MarkN" <m@n.com> wrote:
>>>
>>>First, I would advise against using a string field as a foriegn key especially
>>>if you db gets to be of any size and definitely if that field (CategoryID)
>>>has meaning.
>>>
>>>To answer your question. You will need an associative entity to solve
>the
>>>many-to-many relationship.
>>>
>>>Table: Category CategoryProduct Product
>>>PK Fields: ID CatID & ProdID ID
>>>Example: 15 15 23 23
>>>Example: 11 11 23
>>>
>>>
>>>"Simon Franklin" <simonfranklin@iperium.com> wrote:
>>>>
>>>>Im an designing a database using SQL Server 2000 that stores various
product
>>>>details. I have a products table that contains a CategoryID field I also
>>>>have a Category table containing many different Categories. I have created
>>>>a one-to-many relationship between the Category table and the Products
>>table
>>>>an all works well. The relationship allows me to select Products by Category
>>>>such as "SELECT * FROM Products Where CategoryID='MyCategory'". This
is
>>>perfect
>>>>for products that are related to a single category but I would also like
>>>>a single product to be related to many categories. How would redesign
>the
>>>>database to allow this without storing duplicate records. Thanks in advance
>>>>Simon.
>>>
>>Thanks Mark all is clear now well almost.
>

[MarkN]

Yes.

Category has 0 to Many CategoryProduct
Product has 0 to Many CategoryProduct
CategoryProduct has 1 and only 1 Category
CategoryProduct has 1 and only 1 Product