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

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?

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)

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

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

What about something like this:
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.

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

r937: looking into it, but what's easier: Adjacency or Nested?
Seems a bit of the same, but yet so different...

no, none whatsoever

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.

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

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?

well, you would only want to add subcategories that you actually needed, and yes, that's the way

with the categories, yes

with the items, if the parent column actually refers to the categories table, then yes

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?

that is correct