SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    One-To-Many Relationship with a Twist

    I have a basic 1:Many table relationship for products and the categories they are in. The tables are set up as

    Products
    id
    name

    Categories
    id name

    CategoriesXProducts
    cat_id
    prod_id

    This has been in place for years, works great, yadda yadda. Now we have a new requirement where we need to specify a single relationship (inside of CxP) as the "master category". Currently when we want to know about the product, we select the categories and only use the first returned category. This obviously doesn't always work well or even consistently since there is nothing to sort CxP on.

    Without a complete overhaul of the current structure, what is the best way to accomplish this? I would like to be able to enforce the 1 master per product at the database level, but the only structure I can think of that allows that is a column that is "is_master" where no is NULL and yes is 1.... but that's a nasty hack
    MySQL v5.1.58
    PHP v5.3.6

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by BrandonK View Post
    I have a basic 1:Many table relationship
    I think you mean a many:many relationship?
    the only structure I can think of that allows that is a column that is "is_master" where no is NULL and yes is 1.... but that's a nasty hack
    A hack? No, it's a good solution. And nasty? Why?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    use an integer instead of a boolean (especially a boolean that allows NULL!)

    that way you can have "master status" of 100, 200, and so on

    this would be a lot easier to hack for future requirements, e.g. major category plus minor category
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, yeah, it's a many to many. I always forget the first table has a bunch of records :duh:

    It's a hack because it's an odd handling of NULL. NULL should not mean false -- the only reason I'd use it is because NULL values don't affect unique indices.

    Rudy, using that method (a status_code), there's no way to enforce singularity/uniqueness/whatever the proper term is (outside of additional application logic) for each product. Any thoughts on that?
    MySQL v5.1.58
    PHP v5.3.6

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    CREATE TABLE CategoriesXProducts
    ( cat_id INTEGER NOT NULL
    , prod_id INTEGER NOT NULL
    , PRIMARY KEY ( cat_id , prod_id )
    , status INTEGER NOT NULL DEFAULT 100
    );

    you can link each product to the same category only once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right, that's how my current indexing works, but I'm curious if there was any way to enforce there only being 1 "master category" per product as well. Basically if I do a join to show a product's master category, I want to be able to know it will only return one row per product
    MySQL v5.1.58
    PHP v5.3.6

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    in my scheme, the master category would be the one with the lowest status number (actually, "priority" might be a better name than "status")

    but there are many ways you can assign primacy ("first among equals"), just choose a design that gives you some flexibility (which a boolean does not)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Can't you avoid duplicate prod_id-status couples by putting another unique key on that pair of columns?

  9. #9
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I put an additional unique index on prod_id, status, then products would be limited to having two categories (status=200 for primary and status=100 for non-primary/whatever). The only way I know of to get around that is default status = null, but then I'm right back to my first post trying to avoid that.
    MySQL v5.1.58
    PHP v5.3.6

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    can a product belong to more than one category? it looks like yes

    can a product belong to more than two categories?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, there is currently no limit to the number of categories a product can be in. Currently, we have products that are in more than 10 categories. This is why it is becoming harder for me to pick the master category given our current schema
    MySQL v5.1.58
    PHP v5.3.6

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    You can also handle the master category assignment in your application code: when someone assigns a master category, check if there already is one, and react any way you want: give a message, change the master category, whatever.

  14. #14
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, does that smiley mean my schema needs an overhaul or was there something else I am missing?

    Guido, yeah, I can handle it in the app logic, I just like to have the database enforce relationship restraints when possible (it gives me more peace of mind). So far Rudy's recommendation is winning (I like the idea of flexibility for other category markers in the future).
    MySQL v5.1.58
    PHP v5.3.6

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by BrandonK View Post
    Rudy, does that smiley mean my schema needs an overhaul or was there something else I am missing?
    it means that i see that you are on the right track and i am happy about it

    the many-to-many table is a necessity

    choosing one of the many to be a "first among equals" requires an additional column in the many-to-many table, and you ~could~ make it a boolean, but you can also get other advantages by making it an integer

    i'm not sure guido was actually suggesting this, but at no time should you consider storing the "first among equals" attribute outside the database (for one thing, it would mean at least one row for every product)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I guess status_code it is. I'll just have to enforce the restriction of the "master status" in the application. Thanks for the tip about the status_code -- should be more useful than a simple boolean field.
    MySQL v5.1.58
    PHP v5.3.6

  17. #17
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm not sure guido was actually suggesting this, but at no time should you consider storing the "first among equals" attribute outside the database (for one thing, it would mean at least one row for every product)
    I was saying that if the OP wants to have only 1 category (at the most) per product that has the value 1 (or whatever value he wants to use for his 'master category'), and he doesn't want to put a unique key on the product/status pair (because he doesn't want to use NULL as a value for all categories that don't have a status), then he'll have to enforce that restriction in his application code.


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
  •