Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2006
    Location
    Copenhagen, Denmark
    Posts
    11

    Unanswered: categories and subcategories (was "Database Design")

    Hello, I'm new here, and this is my first post.
    I've been working PHP for quite a while now, and I have recently started on a new shopping solution project.

    I have read Joe Celko's Trees And Hierarchies in SQL for Smarties, but can't quite grasp the subject...

    I need to create a database for my products, listing categories, sub-categories, sub-sub-categories and the products within each of the latter...
    searchable, by category, gender, size, item and color

    Categories/sub-categories/sub-sub-categories:

    1) Clothing/gender/size/item (there are 12 different sizes and 9 different items within each size
    ex: Clothing/girl/3-6 mths/Trousers
    ex: Clothing/boy/18-24 mths/Shirts

    2) Decorations/type/item
    ex: Decorations/Wallies/Children/Motif
    ex: Deorations/Wallies/General/Motif

    Any ideas or relevant links to useful resources?
    I'll be very grateful of any help/suggestions offered

    I use MySQL version 4.0.24
    Last edited by Krazy^; 03-16-06 at 09:28.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    don't feel bad about not understanding celko's nested set model, i tried a few times myself and did not see it through to implementation

    why not try the basic adjacency model design --

    categories
    id
    name
    parentid

    this is pretty bulletproof, have you tried it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Location
    Copenhagen, Denmark
    Posts
    11
    yes. i have indeed, but reaching subcategory 2, i'm getting stuck before adding in my products table....

    Would i be able to create 1 (one) table, listing categories, subcategories and level 2 subcategories {id, name, parentid} 1 table to list the products {id, name, description, gender, price} 1 table for sizes {id, parentid, name(size)} and finally a table for colors {id, parentid, name}?

    As this will, in time, become quite a extensive database, will this be optimal use, or is the another better way of doing things?
    I am quite new to MySQL, as this will be my second database driven site....
    (first one was a user, publishing site)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you can create one table for all levels of categories and subcategories

    how exactly are you getting stuck?

    id parent name
    100 --- shoes
    101 100 dress
    102 100 casual
    105 102 loafers
    107 101 brogues
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2006
    Location
    Copenhagen, Denmark
    Posts
    11
    My main problem is this:
    (story from the beginning)

    I am to design a webshop (complete with admin area and purchase history) for my company, dealing in childrens clothing and apparrel.
    Looking at general use databases, i quickly found that adding data to a single table, with all necessary information, would be too time consuming to be an option.
    The database i need should be searchable, by item number, item name, size, color and price
    This would be fairly easy, hadn't i had several hundred items for each product category....

    I have been sketching on a table design, but i need help actually building it, or at least tips n' tricks toward building it.

    the sketch is as follows:

    ~Categories:
    1 Clothing
    2 Decorative Items

    ~Subcategories in Clothing(1)
    1:1 Infants
    1:2 Children

    ~Subcategories in Infants(1:1)
    1:1:1 Girls
    1:1:2 Boys

    ~Subcategories in Children (1:2)
    1:2:1 Girls
    1:2:2 Boys

    ~Subcategories in 1:1:1, 1:1:2, 1:2:1, 1:2:2
    *:*:* Bodystockings
    *:*:* Trousers
    *:*:* Shirts/Tops
    *:*:* Sets (Complete with bodystockings, trousers and shirts/tops)
    *:*:* Skirts (only applicable to 1:1:1 and 1:2:1)
    *:*:* Dresses (only applicable to 1:1:1 and 1:2:1)

    Looking further into Category 1, all clothing items should be added to the database, containing information about Size, Color and Price (which should also be searchable to show all 'pink' items, or all items within a certain price range...)

    ~Subcategories in Decorative Items(2)
    2:1 Childrens Items
    2:2 General Items

    ~Subcategories in Childrens Items (2:1)
    2:1:1 Wallies
    2:1:2 Stickers
    2:1:3 Posters

    ~Subcategories in General Items (2:2)
    2:2:1 Wallies
    2:2:2 Art & Paintings


    The database will be expanding rapidly as my stock fills up with new items, such as toys, games and electronics (Game consoles & handheld game consoles) and games for the latter....

    Do I make any sense at all?

    So, onto my problems:
    I have created a table containing all categories and subcategories, but adding items to sublevel categories are proving a bit complicated.
    I may very well have overlooked something very simple, but after having stared myself blind on the sql, I'm finding it difficult to get on with it...
    Also implementing the colors and sizes are something i have not gotten around to yet...

    Reading my own sketch actually confuses me
    Please help me, I've almost gone bald over the last 48 hours trying to figure this out
    Last edited by Krazy^; 03-17-06 at 10:23.

  6. #6
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    What about something like this:
    Code:
    Table: categories
    Fields: categoryid, name, description
    
    Table: categories2categories
    Fields: parent_categoryid, child_catgoryid
    
    Table: items
    Fields: itemid, itemname, itemdescription
    
    Table: items2categories
    Fields: itemid, categoryid
    This way, you can have multiple categories per item. You can have subcategories fall under multiple categories. You will not need to create new tables if you want your categories to go deeper. You can decide any way you want to restrict your relationships.

    And then for the clothing items, you could create a "clothing properties" table with size, color, etc. information. Same thing with games, or anything else you want to add. This would prevent superfluous fields in the items table. (`items` would be like an abstract class in java - it's only used as part of something else, but provides the core functionality and relationships.)

    I feel like this solution is super flexible, but would require (slightly) more complex queries and might be a bit more difficult to maintain. Let me know what you guys think.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jfulton
    You can have subcategories fall under multiple categories.
    i would urge you most strenuously not to do this

    a subcategory should belong to only one category

    for example, if you want to have "discount/clearance" under men's shoes, and "discount/clearance" under women's shoes, then create these as two separate subcategories, each with a different primary key, but merely having the same name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2006
    Location
    Copenhagen, Denmark
    Posts
    11
    r937: looking into it, but what's easier: Adjacency or Nested?
    Seems a bit of the same, but yet so different...
    Last edited by Krazy^; 03-18-06 at 08:57.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Krazy^
    Would reserving ID's be a good idea?
    no, none whatsoever
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    i would urge you most strenuously not to do this

    a subcategory should belong to only one category

    for example, if you want to have "discount/clearance" under men's shoes, and "discount/clearance" under women's shoes, then create these as two separate subcategories, each with a different primary key, but merely having the same name
    In your example Rudy, those two subcategories are in fact two separate collections of items and should be their own entities. I'm just saying that the situation may arise when it will make sense for a subcategory to appear under multiple parents - when the same collection of items can fall under multiple categories. It all depends on how the data is being stored and organized. I'm not saying that my solution is the best idea, but it provides great flexibility.

    Suppose I have unisex clothing subcategory - like "winter hats and scarves" - and I want to display this subcategory under both women's and men's clothing. If I maintain a separate subcategory for each, (when they in fact share the same exact items and properties) not only will I be storing redundant data in the item-category relationships, but when I go back to make any changes to my collections, I will need to make changes to the relationships for EACH created subcategory. Now suppose these hats and scarves are for children too. That means each time I add or remove an item from the collection, I'll have to do it four times. Using my solution, we know that these four subcategories are actually only one collection of items.

    I've run into situations like this before, and from experience I have found them to be a nightmare when the database is structured using the adjacency model. The only problem, is if you NEED the parent - child relationship to be 1:n. (ie. building a breadcrumb navigation from my suggestion would be difficult.)

    All that said, it's just a suggestion, not the holy grail of solutions.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    subcategories under more than one parent category is a difficult concept, but you explain it well



    p.s. if you would like to write articles for a new site about sql, please contact me via PM
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2006
    Location
    Copenhagen, Denmark
    Posts
    11
    for now, i don't have any cross-category items in my stock, so i assume i need to use the adjacency model, and create separate item entries for each subcategory (ie clothing/babies/girls/hats and clothing/babies/boys/hats), as there are infact separate items, with different stock status....

    Now, from what i gather, my categories table should look something like this:

    category_id:namearent
    1:clothing:null
    2:decor:null
    3:infants:1
    4:children:1
    5:boys:3
    6:girls:3
    7:boys:4
    8:girls:4

    adding further subcategories should be fairly simple, by setting 'parent' to the corresponding category
    9:shoes:5
    10:shoes:6
    11:shoes:7
    12:shoes:8
    13:type:9
    14:type:10
    15:type:11
    16:type:12
    17:subtype:13
    18:subtype:14
    etc

    although it seems like quite some extensive work, adding in subcategories to all those categories, but i assume that's the only way

    Now to brands, items, colors and sizes table:
    item_id:brand_namearent
    1:adidas:
    2:nike:
    3:ecco:
    4:brand_name:

    actual products in stock:
    item_id:name:color:sizerice:qty
    1:absolado:red:11.5:35.00:5
    2:f30.6:blue:11.5:35.00:3
    etc

    am i on the right track?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Krazy^
    although it seems like quite some extensive work, adding in subcategories to all those categories, but i assume that's the only way
    well, you would only want to add subcategories that you actually needed, and yes, that's the way

    Quote Originally Posted by Krazy^
    am i on the right track?
    with the categories, yes

    with the items, if the parent column actually refers to the categories table, then yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Mar 2006
    Location
    Copenhagen, Denmark
    Posts
    11
    So gathering up on previously posted information, i need to create a table (category) listing all top- to sublevel categories, including brands, pointing the parent id to the correct parent category.
    that's easy peasy enough, although a crapload of work as the categories are nearing the endless, with all the brands i have in stock for all types of items....

    listing the products themselves would then mean using a new table as described in my previous post and using the model names for item_name and adding in size, color variation, price and stock_status as we go....

    this is where i will ask my next question
    I've read a little about JOIN and such, and as i understand, i need to use this function in the sql_query codes witin my php, in order to list the items correctly in my product catalog?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that is correct

    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
  •