MySQL database with products, product categories and various attributes

Question

I have a complex database design with many different attributes for each product.

Here's the situation: each product must belong to a category , and each category has a different set of attributes .

For instance:

• IGal 7S Product → Category Smartphones → Attributes attr_phone
• UberLaptop Product → Category Laptops and Notebooks → Attributes attr_laptop

From what I understood , it would be better to avoid EAV and continue class table inheritance.

In this observation, I would have a starting point:

---

If I had one set of attributes, I would use the attribute_id foreign key in the products table and call it day, but I will potentially have 50 + attributes set (and therefore 50 + ).

• Is CTI valid as an approach?
• How can I assign the correct attributes set for the product?

Sorry for the stupid question, but in particular, I don’t understand how I can assign different attributes set when choosing a different category. Thank you for reading.

Answer 1

Is CTI valid as an approach?

I think that if the number of categories is on the order of the tenth, and not the hundredth, then yes.

How can I assign the correct attributes set for the product?

You can add to each category a row the table name of the corresponding attribute table, and for each attribute table the row identifier will be the identifier of the corresponding product (so you can define it as a foreign key for the product table).

Answer 2

In almost all situations, it is “wrong" to have 55 tables with the same layout. Make it 1 table better. But then it turns you into a nightmare called Entity-Attribute-Value.

Select several attributes that you usually want to look for. Put the rest in a JSON row in one column. More details