I was looking to create an EAV structure for an inventory management system. But I read this answer and decided against it. I am looking to create multiple tables for all my products. But the trouble now have is that all the products will have categories and sub categories( only two tiers ). How can I create tables to reflect the categories for the products. Do I create tables for each category and sub categories. The categories and sub categories need to be searchable.
I will have a
Product: str:name str:description
a batter table to contain the information of the battery
Battery: str:volt str:size fr:product_id Doll: str:material str:stuffing fr:product_id
But how do I handle the case when doll and the battery have categories and sub categories of their own? The sub categories and sub categories also needs to be relatable to the particular product while adding a product.
Do I create
battery_categories? I feel like as products are added. Every product should have 3 tables and it may grow to a lot of tables.
What is the best way to handle this situation?