What is the most elegant MySQL schema for products, options, and categories?

Question

I worked with a dozen templates (Zen Cart, Cube Cart, etc.). Each of them has its own bizarre way to structure products, options, and categories. All additional functions lead to a situation in the McGuyver'd stack, which makes working with code a complete drag and drop.

So, six years ago, I built my own web store engine, which has been developing over the years and becomes its own map package. Now I am doing engine overhaul. Although no engine is suitable for all the needs of an online store, I was wondering if this model has any shortcomings or if there is a better way to create a flexible, normalized, non-unpleasant database for trading:

Notes:
option_types = colors, sizes, materials
options = red, white, blue, S, M, L, cotton, spandex, leather

Except for the basic things omitted specifically (position, activity, etc.), does anyone see a way to improve this?

Answer 1

Here are my notes / opinions on this subject. You are missing power, but I will do my best to guess them.

• Categories are fine.

• Remove id from item_categories as you are not using it. Create a composite primary key on category_id and item_id.

providing each record with a unique identifier is more reasonable in many ways: it is faster to search in one field than two, it is safer to delete, etc.

What search will you do on this id? The queries you will run are: "Get all categories for an item" and "Get all items for a category." I do not understand why it would be safer to delete. However, I would add that adding id may not be compatible for insertion, since you may have different identifiers, but the same category_id and item_id pairs. You will need to check the restrictions there and make sure that the pairs are unique (and not what the PCs are used for)?

• items are ok ... (see comments below)
• Remove id from item_options (in the same case as above, and see comments below)
• option_types ok

Now, I think that the relationship between items and parameters will require more thought. This seems to be a many-to-many relationship. As an element, such as a T-shirt, can have many sizes, it makes sense to say that each pair and parameters should have a different size. But what happens when, in addition to size, you also have other material, such as cotton and leather. You will need to get information on pairs of cotton-S, cotton-M, cotton-L and leather-S, leather-M and leather-L. This makes sense, as I am sure that they will all have different prices and weights. But now add 2 colors to our t-shirts. You will need to add price and weight for each of the 12 combinations that we have now.

Not to mention that if the user would like to see the price of the product, he will have to choose all the options until he reaches the price. I am not sure how this should be done, since I do not know about requiremets. I just give up the idea: you can apply prices and weight variations at the base price and weight, which will be part of this element.

Just some raw thoughts before going to bed:

• option_types may be some kind of hierarchy
• Carefully think about how you will handle stock with this design in mind. You will have 10 items for the Black T-Shirt ... but how many items do you have for the black leather T-shirt? How is this number related to 10 original ones?

Answer 2

Parameter table. I would add a value under the name. i.e. Black L Black M Black S Blue L Blue M Blue S etc. like a trip to the idea of ​​the Bridge.