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 I 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 battery table to contain battery information
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 categories and sub categories also need 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?
First things first, I'm not sure I agree with your choice of one table per product type. I'm not saying it's wrong, but... It could cause you a lot of grief down the road. It's a fragile schema. You know best. Just get some rest and think on it a bit more.
Regarding category -> sub category, the most straightforward plan of attack is probably two tables with a relationship table that links a product. Using your convention:
Category: int: id str: name SubCategory: int: id str: name fk: Category_id ProductSubCategory: fk: product_id fk: SubCategory_id
So, a category might be computers. Sub-categories are ultrabooks, workstations, and tablets. A product might be a Samsung tablet. Assign said product to the "tablets" sub-category via the relationship table. From that, you can determine it's also a "computer".
If you're lazy and you're 100% sure your schema won't change, you can just drop the SubCategory_id directly on a product. No relationship table required. That means you can only ever have one sub-category, though. Not sure if that's what you want.
In another universe, consider a self-referencing category table. That allows any number of sub-category levels:
Category: int: id str: name fk: Category_id (null if top level)
id: 1, name: "Computers", Category_id: null id: 2, name: "Laptops", Category_id: 1 id: 3, name: "Tablets", Category_id: 1 id: 4, name: "Ultrabooks", Category_id: 2 id: 5, name: "Chromebooks", Category_id: 2
If products can be in multiple bottom-level sub-categories, use a relationship table. Otherwise, just drop the id on the product table. Easy squeezy.