Mysql database structure webshop

Question

I am building a simple webshop for my portfolio website. In this webshop there are two kind of (main) categories: Movies and Games.

Of course these categories have also need subcategories like: game_type, game_platform, movie_type, movie_quality. My question now is: What is the best way to do this?

For my products and categories i now have 3 tables:

webshop_products (wpID, wpName, wpDescription, wpPrice, wpPicture)
webshop_categories (wcID, wcName)
product_category (wpcID, wpID, wcID) -> this is my join table.

My own idea is to my two extra columns in the table webshop_categories: wcType (ENUM 'sub', 'main') and wcParent (For example the subcategory action can have the games or movies category ID as a parent)

Is this solution a good, or has someone a better idea?

Thanks and greetings Eric

It depends how you want to *use* the data. MySQL doesn't support recursive functions, so it is not well suited to the adjacency list model of storing hierarchical data (which is what you're proposing) should you want to, say, fetch all categories (including their ancestors) associated with a given product. For that, you might want to consider using either nested sets or closure tables. See [this answer](http://stackoverflow.com/a/192462/623041) for more information. — eggyal, May 19 '12 at 07:50

score 1 · Accepted Answer · answered May 19 '12 at 07:47

You can just add wcParent column which at the same time will be pointing to parent category id AND if it's set it automatically means that it's a sub category - no need for extra wcType column.

Also, remove wpcID in product_category and just set wpID and wcID to primary index. You really don't need an extra column.

Mysql database structure webshop

1 Answers1