Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    10

    Unanswered: db theory advice multiple catagories per item

    My co worker designed a database where retail items can be placed in multiple catagories. This seems odd to me..... In general, Isnt it more normal than not to be only one catarory for each item? For example, lets say I was selling a bowling ball with a picture of Mickey Mouse on it. I can then find this item in the "Mickey Mouse" catagory or in the "bowling ball" catagory but in the database the bowling ball has only one catagoryID. When I worked for a multi-million dollar corporate retail store , an item was listed once in only one catagroy. But i am sure items can be viewed

    I know there isnt a single rule, I am just looking for a solution. How should the database sturucture be built with this in mind starting out with what is listed below???? Mabey an attributes table?

    Items
    ItemID ItemName CatagoryID

    Catagories
    CatagoryID CatagoryName

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If items can indeed be in more then one category, your coworker was probably right. That would be something like:

    Items
    ItemID ItemName

    Catagories
    CatagoryName

    ItemCategoryMatrix
    ItemID
    CategoryName

    If it can only be in one category, your solution would be better. You might want to have retail category and search category or something. It just depends what the business needs are.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jun 2004
    Posts
    10
    I guess my question would be this: if I asked 1000 retail stores/online ecommerce site how they built their database, which one would they use? What percent would use the matrix you described and what percent would use one catagory per item?

    Let me throw in the fact that the co worker is not the best database programer so, what he did might, not be necessary.......

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    An awful lot depends on the industry/target market, and the complexity of the web site. If you have an auto-parts store, odds are good you can deal with one category per item. If you have Wal-Mart, you need to support multiple categories per item.

    While any business can use a "single inheritance" tree with only one category per item (leaf node), as the business gets more complex and wants to reach more customers, the pressure increases to support more complex relationships using something like the link table "matrix" or something like it.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your coworker is proactively thinking about functionality and scalability in the design. He sounds like a damn-fine database programmer to me. Tell 'im the blindman said so.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and even if you're sure that each item belongs to only one category, it is still advisable to implement the many-to-many relationship table that derrick suggested

    you can populate it with one-to-many data easily -- in fact, if you wish, you can even add a constraint to enforce the rule that an item can belong to only one category

    but if it ever has to change, you simple allow multiple rows, and very little other changes (e.g. to queries) will be required

    whereas with the initial design, with the category foreign key right in the item record, the change is extremely disruptive
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah! Tell 'im Rudy the Canadian Dude says so too. We have international agreement now.

    Good enough?

    Helluva lot easier than calling up 1000 retail stores! As if THEY knew how to design databases...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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