SQL Relationship Question


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: SQL Relationship Question

  1. #1
    Simon Franklin Guest

    SQL Relationship Question


    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.

  2. #2
    MarkN Guest

    Re: SQL Relationship Question


    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.



  3. #3
    Simon Franklin Guest

    Re: SQL Relationship Question


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

  4. #4
    MarkN Guest

    Re: SQL Relationship Question


    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.



  5. #5
    Simon Franklin Guest

    Re: SQL Relationship Question


    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.

    >



  6. #6
    MarkN Guest

    Re: SQL Relationship Question


    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




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