MySQL table design question

I have a situation where I am creating pricing tables for products that will have a category, sub-category and another sub-category under that. So for example Widgets, Big Widgets and Small Widgets as level 2, and under Big Widgets, Type 1, 2 and 3. But no sub-sub-category under Small Widgets.

Then I want to create a pricing table that will be rows and columns to account for models and sizes. So the rows will be Model 1, Model 2, etc. and the columns will be 6", 8", 10" etc. So I would maintain a price for Widgets/Big Widgets/Type 1/Model 1/10"

The web page menu structure will be built from the 3 category tables with some form of nested menu structure (not being addressed here).

So my question is how do I create a table structure to best handle this and to allow for the addition and maintenance of products by the user without programmer intervention going forward? I started with 3 tables for the category and the two subs but since I don’t always have a second sub, I’m not crazy about that.

I also thought about creating a separate product table that would make reference to the 3 category tables and when I am creating the menu structure I would know if there was a third level or not because it would either call the third level menu or I could look for the record id of the second level category which in this case would be a product.

Hope that makes sense and any input would be appreciated. Thanks

1 Like

I’m going to close this. I think it is probably too ambiguous to discuss in this forum. Thanks to all who read it.

Have you looked at the models chosen by other e-commerce platfoms? Like OScommerce (old but it could serve as an example) or similar.

Basically, if you see how they did it, it may give you an idea. They solved this problem before

too ambiguous? i disagree

the solution you are looking for is really simple

here’s a diagram –

notice that each product belongs to only one category, and each category belongs to only one parent category

for more information, please see Categories and Subcategories

Another aha moment. Makes sense. Unfortunately, the link to Categories and Subcategories goes to a 404 page and I would really like to read that if you know the correct link. Thanks. The input is appreciated.

Never mind, did a search and found it. Thanks again.

yeah, sorry about that… i updated/fixed the link

so one thing you should be looking for, and which is very definitely possible, is that you can tie a given product to any level in the category-subcategory hierarchy

so some products are tied to type 3 big widgets (at level 3 in the hierarchy), while some products are tied to small widgets (at level 2)

it’s all based on the FK in the product row, which can point to any category, which can be at any level

Surprisingly, this is a complicated subject. There is a lot of normalization concerns going on.

If you have a table for categories, you could put the parent in that table as well. Like this:

ID  |  Name     | Parent
3   | Furniture | 
4   | Chair     | 3
5   | Bean Bag  | 4

Here the Chair has a parent of Furniture, and Bean Bag has a parent of Chair. You can easily chain these together. But what happens if you delete Furniture? You have to search your code to see if any category has ID 3 as a parent, but if you delete the 3, you have to search if anything has THAT as a parent. The whole chain breaks and can take a lot of overhead to test and fix.
If you deleted the Chair field, you have to search for anything that has 4 as a parent, but then, you can’t just delete it, you have to test if that category has a grandparent and move it to the grandparent instead.

This is a normalization issue, it’s called a delete anomaly.

You also can’t have unlimited categories in their own columns:

 ID | Name | Parent1 | Parent2 | Parent3 | Parent4

This isn’t practical at all.

To solve normalization, you usually use multiple tables and another table to link them with foreign key restraints. Sadly, even this is not without problems, as the FK cannot handle the inheritance issue, i.e. if you delete a “middle” category, it can’t reassign to the grandparent category. This is business logic.

No matter what you do, you have to consider all the conditions:

Scale - Don’t limit yourself to just a sub and sub-sub category. You never know if you need more.
Delete - How to handle links to parents and grandparents if any category in the chain is deleted.
Many-to-many - Make sure each product can have many categories, not just one chain.
Reassign - How to move categories around without breaking things.
Queries - However you handle these issues, make sure they can be done with as few queries as possible, use foreign key restraints so the server does some of the work for you.

This is one of those “don’t reinvent the wheel” things. You should check out how other shopping cart software handle this and see how you like it.

A store that I deal with uses multiple bad patterns, they not only put the parent in the category table, they also use a semi-colon to point to multiple categories in the same field. (HINT: always try to avoid CSV fields!)

ID   |    Name       | Categories
55   |   Shark bait  | Category1~subcat;Anothercat~subcat1~subcat2;athirdcat

This is a pain to deal with. It links the categories by name instead of by ID. It uses CSV practices, and makes normalizations difficult.
All the problems discussed, they solve in the business logic, in the code. Mores the pity.

Good luck to you!

1 Like

Thanks for all the input. I think I can run from here. My situation is relatively simple in terms of layers and also in terms of control over who deletes, etc. so the single table, parent relationship should work just fine for me. Again, thanks, I do appreciate it.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.