SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Preventing Redundant Data in the Database

    Hi all,

    I have a problem where I may have to contain duplicates of products in a products table where some products belong to two different categories (from a categories table). But I don't want duplicates, and instead would prefer a work around where I can relate a particluar product to two categories.

    In the products table I have a field named 'parent_id' where this is a foriegn key for the primary key of the categories table. But, now my client has issued me with a new set of categoires, some of which contain products that are already in the database and that belong to another category.

    Is there any way I can link products to two categories, with out adding an additional column to the products table?

    For example:

    If I have the following category (in a categories table)...
    cat_id | name
    20 - Winsor Art Colours
    21 - Technical Brushes

    and the following Product (in a products table)...
    item_id | parent_id | name
    150 - 20 - Red sky brushes

    How can I make make the product (item_no: 150) also relate to category 21?

    Thanks

    Tryst

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Remove parent_id column from product table.
    Add a new table which contains only product id and parent ID. Make both columns the primary key. Voila.

  3. #3
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers MattR, but is there another way around this as I have embedded quite a few queries within my PHP to handle the tables as there are.

    Cheers

    Tryst

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes there is another way, but it is way more painful than what mattr suggested

    bite the bullet, admit that the "product can belong to only one category" design rule was shortsighted, add the relationship table, and update your queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •