Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2005
    Posts
    7

    Unanswered: How to structure multiple categories

    Hi, all. I hope you'll forgive my newbie question. I'm working on a php/mysql project beyond my very limited capabilities (but what can I say, I work for free.)

    I want this app to display products that sometimes appear across multiple categories. After much (unfruitful) thought, I decided on:

    unique_id prod_name [blah blah] category1 category2 category3

    And then a category table:
    unique_category_id category_name parent_category

    ... but not only did I realize how silly that was, I actually came across some reading on denormalized tables, which this seems to be.

    Okay, so I should probably somehow be putting a product's actual categories into a third table somehow, right? Because one product might fit four categories where another would only fit one.

    But this is where my brain stops. Any suggestions?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it might seem complex at first, but it isn't, honest

    first of all, you want a product to be in multiple categories

    this implies a many-to-many relationship, which means three tables
    Code:
    create table products
    ( id smallint not null primary key auto_increment
    , name varchar(11)
    );
    insert into products ( name ) values 
     ( 'shampoo' ), ( 'beer' ), ( 'underwear' ), ( 'toothpaste' )
    ;
    create table categories
    ( id smallint not null primary key 
    , name varchar(11)
    );
    insert into categories ( id, name ) values 
     ( 101,'food' )
    ,( 102,'drink' )
    ,( 103,'clothing' )
    ,( 104,'essential' )
    ,( 105,'luxury' )
    ;
    create table productcategories
    ( product_id smallint not null 
    , category_id smallint not null 
    , foreign key ( product_id ) references products ( id )
    , foreign key ( category_id ) references categories ( id )
    , primary key ( product_id, category_id )
    );
    insert into productcategories values 
     ( 1, 105 )
    ,( 2, 102 )
    ,( 2, 104 )
    ,( 3, 103 )
    ,( 4, 105 )
    ;
    select products.name   as product
         , categories.name as category
      from products
    left outer
      join productcategories
        on products.id 
         = productcategories.product_id
    left outer
      join categories
        on productcategories.category_id 
         = categories.id
    order
        by product
         , category
    ;
    product    category
    beer       drink
    beer       essential
    shampoo    luxury
    toothpaste luxury
    underwear  clothing
    verify for yourself that this is many-to-many

    note that your categories table, with the parent_id, is the correct way to model a hierarchy of categories (and i did not sho this in my example)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    7
    Rudy, do you have a web site?

    If so, maybe you can publish this thread in its entirely, because I think you just wrote a be-yoot-iful article on this! WHAT a reply!

    It was worth cleaning my glasses so I can read it over and over again ... I think it's actually getting through to me ... Thank you SO much! This really is a quite elegant (as well as enlightening) response!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Rudy does good work... And besides that, he's lots of fun to aggrivate (I'm well versed on this subject!).

    Yes, he has a web site (see the links in his signature at the bottom of his posts), as well as being featured on a number of other web sites too.

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the kind words, guys

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2005
    Posts
    7
    Okay, I have one more question (Hah! hahhh hahhh!)

    Thanks to the fine advice here, I have my tables populated. Now I'm trying to put together a query. My only obstacle, despite my total inexpertise, is forming a query when one of the tables doesn't have a unique index.

    In English, my query would look like this:
    // Select prod_name from products, where cat_id in prod_categories = the cat_id we're scrolling through in this foreach loop.

    prod_categories doesn't have unique IDs, of course, because one product ID can apply to different categories.

    I'm futzing around with a LEFT JOIN command right now, but perhaps someone can give me a nudge in the right direction? I'll post here if I solve it myself.

    THANKS again.

    - blake

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blakekr
    ... forming a query when one of the tables doesn't have a unique index.
    that's not possible, all three tables i illustrated for you have a primary key

    Quote Originally Posted by blakekr
    = the cat_id we're scrolling through in this foreach loop.
    oh, please, no, do not tell me you have a query inside a loop

    Quote Originally Posted by blakekr
    prod_categories doesn't have unique IDs, of course, because one product ID can apply to different categories.
    oh, my beautiful tables! oh, my gorgeous relationships! my pretty queries!

    trashed! ruined! it is to weep


    Quote Originally Posted by blakekr
    I'm futzing around with a LEFT JOIN command right now
    thank you so much, that's a mental picture i just did not need at this point in my life
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2005
    Posts
    7
    Oh, well, of COURSE it's ugly, remember who's "coding" this? Not you, ME!

    As for the prod_categories, you're right, it does have a primary key now that you mention it, just a strange advanced sort of key that I've never seen before.

    Thanks for the reminder tho.

    Edited to say ... ARGHHHHHHHHHH

    I just found the query statements in your original post.

    ARGHHHHHHH
    Last edited by blakekr; 01-11-05 at 15:59.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so, i need to ask, is there really a foreach loop?

    and why did you think you needed a left outer join?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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