3

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.

For example I will have a product table

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 doll_categories, doll_sub_categories, battery_categories, 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?

developernaren
  • 197

1 Answers1

1

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)

The data:

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.

Scant Roger
  • 9,048
  • This is exactly the approach that I decided to take. Add category to the top level and have a subcategory table above product and have sub_category_id as fk on products. I am 99% percent sure about the level of category being just 2 but there is always that 1% I guess :) And also to know which category is for which product during adding a product, I decided to put a product slug in the category table as well, otherwise i would not know what categories are for which product. Is there another way you have in mind? Thanks for the answer. – developernaren Nov 21 '15 at 05:54
  • I don't think this is a great approach. Having one table for all categories and sub-categories is the best method IMO, as a sub-category is in fact a category. Use three fields parentID, ID, category. Main categories will have NULL as the parentID and sub-categories will have the ID of the main category in parentID. And if you ever need to scale this up, you won't need to add a sub-sub-categories table, you just carry on with the same system. – TheCarver Jun 23 '18 at 14:14