If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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)

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.

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...)

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

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.

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

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.

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:

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?