Database Design – Best Multiple Tables Product with Categories

database-designMySQLrelational-database

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?

Best Answer

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.

Related Topic