MySQL database with products, products categories and different attributes

Question

I'm having some difficult designing a database with a lot of different attributes per product.

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

E.g.:

• Product iGal 7S → Category Smartphones → Attributes attr_phone
• Product UberLaptop → Category Notebooks & Laptops → Attributes attr_laptop

From what I understood, it would be best to avoid EAV and proceed with class table inheritance.

On this observation, I would have as starting point:

---

If I had a single attributes set, I'd use an attribute_id foreign key in the products table and call it a day, but I'll potentially have 50+ attributes set (and therefore 50+ tables).

1. Is CTI still valid as approach?
2. How could I assign the correct attributes set to a product?

Sorry for the stupid question, but in particular I fail to understand how I can assign a different attributes set when a different category is selected. Thanks for reading.

Answer 1

Is CTI still valid as approach?

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

How could I assign the correct attributes set to a product?

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