Laravel database design multi-level categories with product details table

Question

I'm trying to make relashionships between multi-level categories and subcategories, with product details table.

at first i have categories table :

+----+----------+
| id | name     |
+----+----------+
| 1  | vehicles |
+----+----------+

and i have separated table for each category like category_cars table :

+----+-----------+---------+-------+
| id | parent_id | mileage | motor |
+----+-----------+---------+-------+
| 1  | 1         | 1000    | 1600  |
+----+-----------+---------+-------+

and the products table :

+----+-------------+-------+
| id | category_id | title |
+----+-------------+-------+
| 1  | 1           | 1000  |
+----+-------------+-------+

basically the category_id in the products table is the id of category_cars

and when i create a product i set it's title in products table, then add the other attributes of the proudct to the category_cars table like mileage, motor, ...

so what if i need to create a sub-category of vehicles e.g: cars, in this case i have no idea where to create the product.

what i'm trying to achieve is to have multi-level categories, like 9 static parent categories and dynamically created sub-categories

and a product attributes based on which product is this e.g: a car, or a motorcycle. to be able to have separated data (attribute) for each product type.

there's a default columns that will be globally for each product, like name,price and each product will need to have it's extra columns depending on it's type.

so the point is to get product this way :

product =>
    [
        name = myproduct
        price = 200
        category [
            id = 1
            name = vehicles
        ]
        product details [
            milage = 1000
            motor = 1600
            ...
        ]
        ....
    ]

and display all products from a category whether it's main category or sub-category.

i can't figure out how i can make a relashonship between these. the logic itself is so complicated for me.

so how can i achieve this?

Answer 1

this model is named EVA

entity value attribute

i made this befor and i will share my solution with u

• category table (id , title , parent_id)
• attributes table (id, title)
• product table (id , title , price , category_id ,.. )
• product_attribute table ( product_id, attr_id , value)

and if u want to make attributes based on category u will make this table also:

• category_attribute (category_id , attribute_id )

Answer 2

It's a mistake to make separated table for each category like category_cars table because this means each new category in the future will need new table.

It's more prudent to make self join in categories table and add new column called parent_id in categories table.